I have a simple formula that picks up numbers from different cells and various worksheets.
Is there any way without flicking through all the worksheets to see what numbers make up the result of the formula?
I have a simple formula that picks up numbers from different cells and various worksheets.
Is there any way without flicking through all the worksheets to see what numbers make up the result of the formula?
Try this. display the Formula Auditing Toolbar from the View menu. Then click on Evaluate Formula (last button on left of Toolbar). click on Evaluate button at the bottom until you see the numbers used in the formula
QuoteOriginally posted by royUK
Try this. display the Formula Auditing Toolbar from the View menu.
I've always wanted to do this also... Is this toolbar not in all versions?? I don't seem to have it.. I have excel 97
I have an auditing toolbar but it only shows 'trace dependents' etc...
As an alternate, when the formula is displayed in the formula edit box at the top ofteh worksheet, click on the = sigh to the left of the box. The resulting displa will show various values as you click on different parts of your formula. Also you can select any part of your formula and hit the F9 key, which will calculate the value of the selected part. Just remember to cancel when you are done to get the original formula back.
QuoteOriginally posted by Derk
Also you can select any part of your formula and hit the F9 key, which will calculate the value of the selected part. Justt remember to cancel when you are done to get the original formula back.
Pressing F9 doesn't do anything for me. It still only shows the grand total of the cell??! I have excel 97.
QuoteOriginally posted by Aaron Blood
Have you guys seen the Explode add-in?
Explode looks cool. I am going to try this out. Thanks for the link!
I previously posted in the Hey! That's Cool! Forum that Explode is, in fact, cool. Aaron's work is top notch. This particular add-in will make anyone who ever used the Auditing Toolbar regret that they didn't find Explode sooner.
Once again, kudos to Aaron Blood's Explode!
Quote
Pressing F9 doesn't do anything for me. It still only shows the grand total of the cell??! I have excel 97.
Select just the part of the formula you want calculated when you F9. This works in Excel 97, as does clickin on the = sign to the left of the formula Edit box.
QuoteOriginally posted by Derk
Select just the part of the formula you want calculated when you F9. This works in Excel 97, as does clickin on the = sign to the left of the formula Edit box.
Ok,, I know what I did.. I was just putting the cursur inside the part of the formula i wanted to know about.. i didn't actually select the entire part...
one bad part.. if the formula is from another workbook and the workbook is not open I get a "#REF!"... but still good thing to use in other situations... Thanks for clarifying!
QuoteOriginally posted by Derk
Select just the part of the formula you want calculated when you F9. This works in Excel 97, as does clickin on the = sign to the left of the formula Edit box.
By hightlighting each cell ref gives me the answer i require (many thanks problem solved). But i have to agree that in XL97 selecting the = sign and pressing F9 still only shows the grand total.
They key, as Derk suggested is in the selection of the specific part of the formula you wish to evaluate.
Say you have something like this
='Note 28-SWAPS'!C25+'Note 28-SWAPS'!D25+'Note 28-SWAPS'!E25+'Note 28-SWAPS'!G25+'Note 28-SWAPS'!H25+'Note 28-SWAPS'!I25
select the first part (i.e. 'Note 28-SWAPS'!C25) in the formula part of the toolbar or the cell itself and press F9 - the formula will be replaced by the calculated value...
You can carry on doing this for all separate parts of the formula in turn (or in "chunks") to see the values involved.
Just remember to press ESC to cancel out of this mode (as opposed to pressing enter, which would have the effect of turning the above formula into, say
=10000+453.10318+90.62064+10000+459+92 :biggrin: (not good!) )
Hope this helps,
Don’t have an account yet? Register yourself now and be a part of our community!