“Hey, could you help me on this financial spreadsheet?”, my friend asked.

“Sure”, I respond. I may have forgotten my finance classes, but I’m okay with Excel.

The assignment: some blocks of formulas needed to be duplicated into multiple blocks, but if copy-pasted the formulas would no longer be right. Excel by default isn’t good with this; sure, you have $A$1 vs. A1, but multiple blocks of essentially the same logic means a formula should be able to generalize in one way across the Y-axis within a block, yet in another way across the same axis to generalize between the top-left cell between blocks 1 and 2 (if also arranged vertically). Removing that $ won’t suffice anymore.

At this point I’m very much inclined to attempt redoing the whole thing in PowerPivot, but the model seems a bit big, so might as well stick with what we had. I somehow managed to generalize things, ending up with formulas like this:

`=INDIRECT("F"&$B23) * $F17 * INDIRECT($C23&":"&$C23)`

vs. the original:

`=$F$11 * $F13 * J$12`

.. among other gems such as this one:

`=INDEX(Revenues!$A:$ZY,$D70+$B70,COLUMNS($A$1:J$1))`

This somehow ended up dynamic enough, so content, I send it back.

“Great!”, my friend said. “Except could you make the formulas back the way they were again? I know it’s probably a lot of manual editing and copy pasting, but financial modeling standards demand transparency.”

“Then why not also show the old static version for their check?”, I ask, defensively. “Static formulas won’t scale, and trying to paste/fix using VBA for each sheet structure will be horrible. Either tell them to accept PowerPivot, or let these econometrists waste away their own time manually editing all day if that’s what they’d prefer.”

“Sorry, finance people don’t ‘get’ Excel, they’d rather just have simple formulas without complicated lookups”, my friend explains.

“Perhaps they shouldn’t be in finance then”, I rebuke in a last desperate struggle. “I thought ‘econometrist’ sounded smarter than someone copy-pasting all day!”

But the damage was done; my dynamic Excel formulas rejected. I could still rebuild everything in PowerPivot, but felt courageous enough to go find a way to convert my dynamic formulas back into simple ones.

I figured it out after discovering an existing formula parser; the result of my efforts can be downloaded here (just download as it’s about the VBA). The meat is in the CompileReferences sub — if ran, it converts formulas with the likes of INDEX and INDIRECT back into simple, static, direct cell references. If run, you end up with the simplified static version of the file, for my sample case here.

A paste of the main VBA can be found here, though it also requires regXLib and cregXLib from here (plus the corresponding reference to Microsoft VBScript Regular Expressions 5.5).

My testing has been limited to this one spreadsheet so far, and PowerPivot conversion is still my next step, but perhaps this may be of interest to others stuck with the dilemma of dynamic Excel formulas vs. ‘financial modeling standards’ grade transparency.