Showing all parts of a formula as values

**bear1986** · 11-13-2012, 04:08 PM

Hi everyone, I need some help and hope someone here can do so!

I have an excel sheet where every cell is a sum of 2 values on a different sheet. The problem is that I need the cell to show the sum of the 2 values, but I also need the formula to show the values of each part of the formula, not their cell references.

I know this can be done by highlighting part of the formula and pressing F9 but the spreadsheet is very large and would take ages to go through one by one.
I have also tried a macro but the values will be changed weekly so it would need to be easy to update.

An example:

a1=1
a2=2
b1=a1+a2+a3

I need to see b1 as '6' and when I select the cell, the formula needs to show as =1+2+3

Hopefully this all makes sense!!

I would really appreciate it if anyone could help or tell me if it is even possible to do this?

**e.isayev** · 11-13-2012, 04:19 PM

Most probably it is possible only with VBA. Lets see what excel gurus will propose

**MrShorty** · 11-13-2012, 04:46 PM

The closest built in utility that I know of is the "formula evaluation" tool (I don't know where to find it in 2010 -- in my older version it is in the formula auditing submenu of the tools menu). With your desired cell selected, you call the formula evaluation tool and you step through each substitution (reference for value) to see how the formula is being calculated. Useful for debugging, but may not meet your requirements.

When I want to be able to see both the formula and the result, I will use a 2nd cell. One cell with the regular formula, and a 2nd cell with a formula to build the text string representing the formula. For the example given =concatenate(text(a1,"0"),"+",text(a2,"0"),"+",text(a3,"0")). For a large spreadsheet, this will require some significant effort upfront to add all those formulas to the spreadsheet, but should be effortless after that.

**bear1986** · 11-15-2012, 02:12 PM

Mr Shorty, thank you for replying!

The formula evaluation tool does excactly what I need it to do, with only one minor extra thing...

Is there some way of making a shortcut with a macro? I mean pressing a key combination and getting the box open with the values in it? It would be just fine if it did this on just the cell that is selected at that time?

If you could help further it would be great!!

**MrShorty** · 11-15-2012, 02:20 PM

I'm not aware of any way to manipulate the formula evaluation tool with VBA. Have you tried recording a macro to see if it gives you anything?

**bear1986** · 11-15-2012, 03:04 PM

Yes I have but it doesn't seem to recognise what I am doing... thank you for trying though!

if anyone has any idea how to program this, your help would be greatly appreciated!