Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to perform a "where used"?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to perform a "where used"?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to perform a "where used"?
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. "Dave O" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to perform a "where used"?
Check out this post from Jim Cone...
http://snipurl.com/1joun "Jeff XL discussion" wrote in message ... 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. "Dave O" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to perform Search and Replace on "Tilde", CHAR(126), ~ | Excel Worksheet Functions | |||
%1 appears in "Application used to perform action" when trying to edit a file type | Excel Worksheet Functions | |||
Perform a "Conditional Substitute/Replace" | Excel Worksheet Functions | |||
How to perform "ifcount" in many sheets | Excel Discussion (Misc queries) | |||
How to perform "ifcount" in many sheets | Setting up and Configuration of Excel |