How to perform a "where used"?

[Guest]

Hello. Thank you all in advance for your help.

The task I am trying to do is to perform a "where used" check. By "where
used", I mean that I want my workbook to do a check of every worksheet in the
workbook and report (put the sheet name(s) in a cell) any worksheet where a
specific cell is used.
In other words:
In a worksheet called "data", I have a set of values in column A. The
values from worksheet "data" are used to varying degree, by hyperlink, in
other sheets(worksheets X1,X2,X3, etc). The value in sheet data, cell A1
might only be used in X1, but cell A2 might be used in sheet X2, X3, X10)
As I mentioned, what i want is for a way to automatically output in one cell
which sheets cell A1 is used, and in another cell output where cell A2 is
used, etc.

 

[Dave O]

You may already know Excel provides this type of functionality, so
forgive me if this is not new information for you. On the menu if you
click >View >Toolbars and select Formula Auditing, a toolbar pops up.
On that toolbar are buttons to "Trace Precedents" (a list of cells
that generate the value in a particular cell) "Trace Dependents" (a
list of cells that reference a particular cell). You're describing the
functionality of the Trace Dependents button. The output is an arrow,
and not a text list in a cell, but this might save you from re-
inventing this particular wheel.

Thinking beyond the boundaries of that particular box, you could write
VBA code to generate such a list. The code would have to create a list
of all the cells that contain formulas, and then within the formulas
strip out math operators, Excel functions, and whatever else. It would
take a bit of doing to make that happen.

 

[Guest]

Dave O -
Thank you. I am aware of the trace dependents (as well as using the find
fucntion to search all worksheets for a formula "=data!a1". However, I'm
trying to find an automated way of generating the list of worksheets. In
this instance, it would be highly beneficial to have the list basically smack
you the face, if you know what I mean.
I'm not familiar with VBA code. i'll try investigating that. If you have
some tips or suggestions, please let me know, and thanks again.

 

[M. Authement]

Check out this post from Jim Cone...

http://snipurl.com/1joun