Highlight cells with dependent cells?

[SacreBleu]

Without using VBA (I am not familiar with VBA), is there an easy way to highlight or mark all the cells in a worksheet that have dependant cell(s)?
I am trying to debug a very complicated worksheet, and that would help a lot.

 

[SacreBleu]

postscript-
It would actually be more useful to have a way to highlight or mark all cells that do not have dependant cell(s).

 

[Zoobie]

Try this out:

Under the Tools menu select Forumula Auditing. This will allow you to trace dependents, precedents, and errors. Activating the Formula Auditing toolbar makes the feature convenient to use for large debugging tasks.

 

[johnwm]

It's built-in!
Menu - Tools|Auditing|Trace Dependents

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[johnwm]

Sorry - slow typing!

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[SacreBleu]

Zoobie,
I was actually looking for a way to mark every cell simultaneously, without showing the dependant arrow(s). otherwise, I would have to select every cell on the worksheet to show dependents individually, and there are hundreds of cells (too much work). I do not need to see specifically what cells were precedent. In other words, if every cell had depedent tracer arrows visible, it would be hard to spot which cells do not have dependents.

 

[Zoobie]

I'm not sure what you can do then without using VBA. The only thing that may help is to go under Tools->Options->View and select the Formula button this won't highlight anything but any cell with a formula will show the formula and all the cells with numeric or text input will appear as normal. For a large sheet with complicated formulas this may be even more cumbersome than the auditing feature.

 

[SacreBleu]

Zoobie,
Thanks but that won't work either. Most cells have formulas - I just need to see the "broken" cells that don't have dependent cells, due to errors incurred while adding-on/patching this worksheet over a long time.

 

[francesca]

VBA is not that hard. I got into it by reading "Visual Basic for Dummies", recording and then editing macros and using the help function, which is excellent in VBA. It was a great investment and has saved me time and heartache countless times. Once you've learned a little of it, you'll wonder how you ever survived without it.

 

[SacreBleu]

francesca,
I am sure learning Visual Basic can be done. However, this worksheet had grown more complicated over 8 years of improvements, and I am afraid it would take a long time to convert it to VBA. (See my previous thread "How do I reduce File Size?").
For future Excel projects, I would be less leery of using VBA>

 

[pmover]

SacredBleu,

unfortunately, there is no technique to accomplish the goal you've stated without writing VBA.

while i do not remember the website, there is a fellow whom wrote vba to conduct trace precedents or trace dependents of worksheets. i believe the results merely indicate how many dependents/precedents exist for a particular cell - formula, and not where they are located. from memory, the fella's name is Aaron Blood. just did a web search and olah, my memory is correct!

try:

http://www.xl-logic.com/menu.html

download the explode.zip file.

sometimes it is best to simply start another workbook to reduce file size. i often acomplished this for "old" or extensively modified workbooks and the results are worth the exercise.

lastly, having nothing to gain or loose, i would be willing, able, and would volunteer to simplify the workbook for you upon request.

good luck!
-pmover

 

[trainguy]

You may be able to do the opposite using the edit/go to/special command.

This brings up a window that will automatically select all cells that have, for example, constants, or formulas, or text.

It's a really useful tool, not very talked about.

This may help you...

tg

 

[SacreBleu]

trainguy- I tried that, but don't see what it does when I select the dependent cell option.

pmover- thnks for the offer, but you wouldn't want to tangle with this. I am essentially re-doing it from scratch onto a new worksheet; I wanted to fully verify the old version because the precedents/dependents are so "convoluted". I am making slow progress, but the new version looks much more efficient, because I am a lot better at formula and logic-statement writing now than I was 8 years ago when this thing started.

 

[trainguy]

Sacrebleu,

If you select a single cell, then edit/go to/dependent cells (direct or all levels), Excel will select all these, and you can for example change their colour, and start debugging.

tg

 

[SacreBleu]

trainguy-
OK I see that. I also found that pressing the Auditing Toolbar "dependant cells" button repeatedly does the same thing, but with tracer arrows.

 

[pmover]

SacredBleu,

good that you are persistent in resolving the matter. fyi, i am not shy nor bashful in tangling with a "convoluted" workbook. afterall, the application does what it is instructed or programmed to do.

i came across this website and thought you would be interested in what the author has to say about reducing large excel files.

http://www.ozgrid.com/Excel/ExcelProblems.htm

fyi, this website, along with others, has significant and useful information for one to practice.

good luck and do not hesitate to ask!

-pmover

 

[Gearhead]

One further sugestion Mathlook for Excel an Add-In is available which not only displays the formulas as you see them in a textbook but also will display the dependant cells as a single formula. Tech street offers this tool.

http://www.techstreet.com/