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…