Understanding Nothing

I have over 40 modules on CPAN, and probably average about 1 bug report a week (not including all the discussion that goes on the Class::DBI mailing list). Most of them are really simple to fix without much effort at all (such as typos in the docs), but like most of these things, if I don’t fix it straightaway, then it’ll be forever before I actually get around to it.

Today I went on a big cleanup and fixed a whole bundle of bugs, some of them reported over a year ago!

I’ve also taken a load of the bug reports that have been emailed to me, which had been filed into about 5 different local mail folders, and forwarded them all off to CPAN’s RT interface instead. I’ve gradually been changing the ‘how to reports bugs’ sections of all the modules to direct people to there instead of private email, as it should make it easier for me to keep track of everything. Of course, having forwarded lots of mail to there I now have more open bugs on the system than I did this morning, despite all my fixes, but at least it’s a more realistic snapshot…

My next Excel Top Tip is how to calculate the last day of a month. I thought there was a function to do this as part of a whole suite of date manipulation functions, but I seem to have imagined that, as I can’t find it now. I was dreading having to do lots of nasty date arithmetic, but then I discovered that the zeroth day of a month is treated as the last day of the previous month! So the last day of the month for the date that’s in cell B3 is simply:

=DATE(YEAR(B3), MONTH(B3)+1, 0)

It even works across year boundaries, so DATE(2004, 13, 0) really does give December 31st 2004!

I’m trying to build a simple bookkeeping system in Excel, and I’m learning all manner of interesting things. But I ran into a significant problem that I’ve found a really ugly solution to. If this is really the best way to do this, hopefully this post will be useful to someone else someday. If it’s not, then hopefully someone will let me know what is so I can rid this bad taste from my mouth!

The (oversimplifed) issue is this: consider 3 worksheets: one for purchases, one for sales, and one for the bank statement.

The purchase invoices go into the Purchase sheet as they arrive, and similarly the sales invoices to the Sales sheet as they’re raised. Then, when they are paid, or the money is received, an entry is made in the Bank sheet. To tie them together, the bank sheet has an “Xref” column in which you enter a pointer to the reference number of the relevant Sale or Purchase cell. So it becomes, for example, “=Sale!A6″. This was all fine, until I then wanted the next column to auto-fill the amount of that invoice so we can check the amount is correct on both sides.

Sounds simple, but I can’t find any way to do it with normal Excel functions. Every function I can find works on the value of a cell, not the underlying reference. So when I access “Bank!B10″ it will give me “SI0103″ rather than the “Sale!A6″ I need to do my dynamic lookup. I asked a few users and they all said “Oh that sounds like it should be doable”, but no-one could actually come up with a way to do it.

Eventually I had to write (for values of write that involve cut’n'paste from a useful website somewhere) a VB function into the spreadsheet to get the formula for a Cell (with all the security warning implications that brings):

Function udfFormula(cell As Range) As String
Application.Volatile
If cell.HasFormula = True Then
udfFormula = cell.Formula
End If
End Function

With that I could slowly build up the formula I needed: C10 becomes: “=udfFormula($B10)”, and D10 becomes “=REPLACE(REPLACE($C10,FIND(“!”,$C10)+1,300,”1:65536″),1,1,”")” to translate the “=Sale!A6″ into “Sale!1:65536″. Then I can plug that into E10: “=VLOOKUP($B10, INDIRECT($D10), 4, 0)” to lookup the referenced ID on the sheet and get back the value in the 4th column over, which is the amount. Phew!

I tried putting this all in one formula, but Excel started running really slowly, failing to calculate the formulas, and even getting them wrong, so I kept it in several columns which I can hide once I find a way to have the formulas auto-fill into the cells.

I still don’t believe this is the correct way to solve this, though…