How to display cell values in formula instead of references?

[lawi227]

For example:

When I click in G5, the formula is: "='Benchmark Inputs '!F18/'Benchmark Inputs '!F10"

Instead, I would like to see: $2,398,528,746 / $4,219,000,000
The values can be displayed in H5.

My formulas have a variety of general, currency, and percentage formatting.

I'm ultimately using this as a way to confirm my calculations and show my work. I would prefer to not use VBA.

Thanks!

 

[BarryL]

if you click into the cell and highlight the whole formula and press F9 you will achieve this.

 

[BarryL]

the below should also help

https://support.office.com/en-gb/ar...s-result-56f2929c-970b-45c1-823e-a87b304d28b8

 

[lawi227]

That shows me the final value being calculated (which is .57), but i want to see the value for F18 and F10 located on the worksheet called "Benchmark Inputs"

 

[BarryL]

That shows me the final value being calculated (which is .57), but i want to see the value for F18 and F10 located on the worksheet called "Benchmark Inputs"

what? if you highlight the formula in the formula bar and press F9 it will give you the values from Benchmark Inputs in the formula bar

 

[lawi227]

It only shows the calculated result. I'm using a mac but that shouldn't make a difference.

 

[Eric W]

I'm not sure there's an easy way to do that. I'd suggest selecting your G5 cell, then go to the Formulas tab and click Evaluate Formula. It will step by step evaluate the formula, showing you the values as they are used/calculated. That's a way to check your work.

If you want to show your work, you'd probably have to rewrite your formula. In H5, put:

='Benchmark Inputs '!F18 & "/" & 'Benchmark Inputs '!F10

essentially leaving the range references alone, and putting the formula pieces in quotes. That's certainly the effect you want, I can't think of a way offhand to create the effect automatically.

 

[Scott T]

If you highlight a section for example the part in red and press F9 it will show you what that section comes out to. Be careful as if you hit enter it will hard code that value into the formula

"=[COLOR=#FF0000]'Benchmark Inputs '!F18[/COLOR]/'Benchmark Inputs '!F10"

would become

"=[COLOR=#FF0000]2398528746[/COLOR]/'Benchmark Inputs '!F10"

 

[lawi227]

This worked. I was highlighting the whole formula. Is there a way I could break down each component? My formulas all reference cells from the "Benchmark Inputs" tab, so I would think I could use some formula to parse out each piece of the equation.

 

[Scott T]

When a formula refers to a cell with a formula in it, it looks at the result of that formula not the formula itself for example
In A1

=Sheet2!A7+Sheet3!A7

In B1

=SEARCH("+",A1)

Even though the formula in A1 has a + the result does not so B1 returns a #VALUE error.

I do not know of a way to do what you want to do.