Menu
I have defined a large number of named cells on one worksheet of a workbook. Each name may be used multiple times (or never) on that worksheet or others in the workbook. Is there a way to get a list of the where else in the workbook the names are used?
For example, lets say I define the name Fred for a cell on Sheet1. There is an equation on sheet2, cell B6 that is =Fred*6. How do I get a function to return Sheet2!B6 and any other places that Fred is used?
I hope this is clear enough. :)
239
Create a Name Range using VBA Define the workbook where you want to create the named range. Use the names property and then further add method. Specify the name in the “Name” argument. Refer to the range using the “ReferTo” argument.
The VBA reference list (figure 1) can be accessed by the VBE menu Tools > References sequence. The Available References list displays each reference item by its description property, in priority order.
You can find a named range by using the Go To feature—which navigates to any named range throughout the entire workbook. You can find a named range by going to the Home tab, clicking Find & Select, and then Go To. Or, press Ctrl+G on your keyboard. In the Go to box, double-click the named range you want to find.
The free version of JKP and my Name Manager Addin can show you where a particular name is used.
You can download this addin from http://www.decisionmodels.com/downloads.htm
When I wrote the FastExcel extended version of Name Manager I added a Name Map facility that shows you a count of where the names are used (or not) by Worksheet and Other Names.
This is done by parsing out the Names from all the formulas on all the worksheets and the formulas in the Names Refers-tos (recursively), using a special-purpose parser and evaluating cases like the use of INDIRECT.
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
