Advanced cell search... ?

[cmpgeek]

Is there a way to tell if a cell is used in an equation anywhere on the spreadsheet? That is - outside of deleting the value within the cell and then looking for the #D/V/O/# messages (or whatever that is... i cant remember it right now)...

i have a spreadsheet that was emailed to me that has a cell with a value in it that is "off by itself"... the OCD in me makes it stick out like a sore thumb, but if it is there for a purpose i can deal with it. I am wondering if there is a way to click on that cell and run a code or something and have Excel check the spreadsheet and see if there is an equation or something that looks at the cell for information... That way, if nothing is looking at it, then i can delete it!

(this is a 50+ page workbook and the same cell is used in each one which makes me think that there IS something looking at it... i can deduce that if something is looking at it in one sheet - then it will be the same in all of them; but the wheels were already turning and got me wondering if such an ability exists...)

thanks!

nomi

[Ken Puls]

Hi Nomi,

Better and faster than VBA, actually. Excel can do this natively!

Click in the cell to select it. Go to the View Menu, choose Toolbars, and select Auditing. Click on the "Trace Dependents" button. If any arrows show up, it is used. If not, you can nuke it!

(To get rid of the arrows, click the eraser button)

Cheers,

[Zack Barresse]

The feature Ken refers to is only avalable in 2000 and above. In 2000, you must manually enable the Toolbar, in 2002 and up, it is in the Tools menu --> Auditing. Very handy, I use it all the time.


If you are looking for specific errors (or any), remember you can use the Goto command (F5) and choose Formulas -> Errors.

[Ken Puls]

The feature Ken refers to is only avalable in 2000 and above...

Sorry, Zack... not true! You're right about having to get it by going to Tools|Auditing though.

[Zack Barresse]

Hey! Cool!! I don't remember finding that, but I am talking about my fledgling Excel days ... umm, my name is Zack, and I'm the new guy ..

I love this place! I learn something new everyday!! Thanks Ken! Much appreciated!

[cmpgeek]

Hey! Cool!! I don't remember finding that, but I am talking about my fledgling Excel days ... umm, my name is Zack, and I'm the new guy ..

I love this place! I learn something new everyday!! Thanks Ken! Much appreciated!

Zack, you can be the new guy - just dont take away my binkie! LOL

and i am definitely with you on learning something new here everyday!

thanks to you both!

nomi

[Ken Puls]

Thanks Ken! Much appreciated!

Not a problem. I don't get to correct you often though!

In truth, I wish that I didn't know this, but am unfortunately still saddled with 97 on half of my systems at work! Just too much $$ to upgrade!

So Nomi, did this work for you? Should we mark it solved?

Cheers,

[Brandtrock]

Heeeeey you guuuuuuys!!! (a little Electric Company flashback, sorry)

And you too Nomi,

If you like the auditing tools Excel has, you should love this.

Explode Auditing Tool (free download from Aaron Blood)

I can't live without it. He got me hooked.

HTH

[Ken Puls]

Hi Brandtrock,

Thanks! Wasn't aware of that one... I think I might need more RAM... keep on adding add-ins!

That does look pretty useful, although I might miss the pretty arrows...:rofl

[cmpgeek]

why yes Ken - we CAN marked this solved LOLOL