Going to/Analyzing formula with many cell references

[apiekar]

Hello,

I have been dealing with many spreadsheets lately that I didn't build and I am trying to understand and analyze many formulas within these sheets. The way I've been doing it is clicking on the cell, viewing the formula, finding the cell in a different sheet, then returning to the original formula to find the next part of the formula.

When some of these formulas reference 10-15 different cells on different sheets these tasks become quite time-consuming. Is there a way to toggle between all the cells in a formula more quickly or some other strategy that gets the same task accomplished?

Thanks!

[modytrane]

A couple of things you can do to help.

Use Tools\Formula Auditig\Evaluate Formula.

Use Tools\Formula Auditing\Trace Precedents

Click on the used cell, right click and then add watch.

See the atachment for a screen shot of a simple formula that uses cells from two other sheets.

modytrane

[darkyam]

Another potential option if the same cells come up again and again is to make the cells named ranges, then do a find/replace to replace the cell reference with the named range. It will be a bit of work at first, but it will make it easier for anyone after you to know what's going on and it may save you some time switching back and forth all the time.

[broro183]

hi Apiekar,

I've found these two free add ins useful:
Aaron Blood's Explode.xla - http://www.xl-logic.com/modules.php?...ddetails&lid=1
John Mote's Formula Mapper.xla - attached because I can't find the site I sourced it from. I find this very useful for identifying consistency of formulae within a spreadsheet.

hth
Rob