×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Highlight cells with dependent cells?

Highlight cells with dependent cells?

Highlight cells with dependent cells?

(OP)
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.

RE: Highlight cells with dependent cells?

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

RE: Highlight cells with dependent cells?

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.

RE: Highlight cells with dependent cells?


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: www.essexsteam.co.uk

RE: Highlight cells with dependent cells?

Sorry - slow typing!

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

Steam Engine enthusiasts: www.essexsteam.co.uk

RE: Highlight cells with dependent cells?

(OP)
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.

RE: Highlight cells with dependent cells?

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.

RE: Highlight cells with dependent cells?

(OP)
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.

RE: Highlight cells with dependent cells?

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.

RE: Highlight cells with dependent cells?

(OP)
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>

RE: Highlight cells with dependent cells?

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

RE: Highlight cells with dependent cells?

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

RE: Highlight cells with dependent cells?

(OP)
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.

RE: Highlight cells with dependent cells?

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

RE: Highlight cells with dependent cells?

(OP)
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.

RE: Highlight cells with dependent cells?

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

RE: Highlight cells with dependent cells?

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.
www.techstreet.com/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close