September 22, 2008 9:46 AM Subscribe

I have an excel file full of formulas that span multiple sheets and rows. I would like to be able to go into a formula and see the actual data used in the formula rather than just the cell references.
So, for simplicity's sake, imagine A1=1,000, A2=2,000, A3=A1/A2
When I go to cell A3 and press "F2" or "cntrl`" the formula with the references shows up "=A1/A2".
What I would like to see is **"=1,000 / 2,000"** (what you would get if you wrote =A1 &" / "& A2)
I know that I can do this sort of with the watch window or the evaluate formula but I am looking for a quick way to roll down a sheet and see the actual math that is being done without having to scroll all over the place or flip sheets.
Hopefully this makes sense. If not, I can further clarify.
No one at my office thinks that it is possible so I might just be out of luck but I live in a world of hope...

posted by GrumpyMonkey to Computers & Internet (5 answers total) 2 users marked this as a favorite

posted by GrumpyMonkey to Computers & Internet (5 answers total) 2 users marked this as a favorite

Insert a column and concatenate thusly:

=a1 & "/" & b1

posted by A Terrible Llama at 10:48 AM on September 22, 2008

=a1 & "/" & b1

posted by A Terrible Llama at 10:48 AM on September 22, 2008

that is pretty much what I am doing today but the formulas are pretty messy and all over the place so the clutter of writing out each cell value gets overwhelming.

An actual example is:

=Sheet1!C285-IF(Sheet2!E281>0,MIN(Sheet1!E278,Sheet1!E281),Sheet1!E278)+Sheet3!E283-Sheet1!D286

which becomes when it is written out:

=12000-IF(0>0,MIN(2884.25925925926,0),2884.25925925926)+15000-24115.7407407407

I am beginning to think that the tool does not exist either.

posted by GrumpyMonkey at 10:48 AM on September 22, 2008

An actual example is:

=Sheet1!C285-IF(Sheet2!E281>0,MIN(Sheet1!E278,Sheet1!E281),Sheet1!E278)+Sheet3!E283-Sheet1!D286

which becomes when it is written out:

=12000-IF(0>0,MIN(2884.25925925926,0),2884.25925925926)+15000-24115.7407407407

I am beginning to think that the tool does not exist either.

posted by GrumpyMonkey at 10:48 AM on September 22, 2008

If that doesn't work, try this:

= " ' " & a1 & "/" & b1

posted by A Terrible Llama at 10:49 AM on September 22, 2008

= " ' " & a1 & "/" & b1

posted by A Terrible Llama at 10:49 AM on September 22, 2008

This may not be at all what you are looking for, but if you are looking for this info to debug formulas, the following may help. I have found myself wanting a similar tool when the number of variables in the formula got to be such that I couldn't read the formula anymore. I was able to fix it by renaming the sheets and the cells themselves with variable names that expressed what they contained so that a formula might come out looking like:

= Printing!Total - IF( Printing!InHouse > Printing!Outsourced, MIN( Printing!Toner, Printing!lInk ), Printing!Maintenance ) + OfficeSupplies!TotalPaper - Printing!Postage

Using names Sheets and cells has gone a long way to making Excel formulas way easier to read and debug for me.

posted by chocolate_butch at 11:19 AM on September 22, 2008

= Printing!Total - IF( Printing!InHouse > Printing!Outsourced, MIN( Printing!Toner, Printing!lInk ), Printing!Maintenance ) + OfficeSupplies!TotalPaper - Printing!Postage

Using names Sheets and cells has gone a long way to making Excel formulas way easier to read and debug for me.

posted by chocolate_butch at 11:19 AM on September 22, 2008

This thread is closed to new comments.

View the calculation:

A1: = Sheet1!A1 + Sheet2!A1

View input 1:

B1 = Sheet1!A1

View input 2:

C1 = Sheet2!A1

I dont think/know if specifically what you are asking for exists (a tool to display the variable substitution only).

posted by SirStan at 10:19 AM on September 22, 2008