find where named range is used/referenced in entire workbook

[Chris Rodgers]

Hi!  I inherited a complex workbook used to run general ledger and reports for a small company.  There are many named ranges used for lookup tables as well as pivot tables.  In the Name Manager, I can see the named ranges and their values.

How can I determine where a named range is being used?  I am looking to delete old named ranges refencing worksheets that have since been deleted.  For example, a named range from ledger was used to identify data for 2021.  That named range was used in a pivot table for 2021.  Now that I have deleted the pivot table for 2021, it is a named range that is not being used.  Before I delete the named range, how can I ensure that no other worksheet is using that named range (no just assuming it was only used in the typical place - might be referenced in another report worksheet)?

Additionally, when I look at named ranges in the Name Manager, I can see that several have #REF! for Value (instead of a column header listing).  They also have "#REF!" in the Refers To column (ex: 'MY WORKSHEET'!#REF!).  That indicates that the range is invalid, correct (like the source data worksheet was deleted or the data no longer exists in the original range)?  If so, it is useless, but before I delete the named range, I want to ensure it is not being referenced in any other worksheet (see question above).  If it is being referenced in a worksheet, then I need to refactor that worksheet or it may be something I can delete as well.

[#2]

@Chris Rodgers 

To find out where a named range is used/referenced in your entire workbook, you can use the "Find" feature in Excel. Here is how you can do it:

1. Open the Name Manager: Go to the "Formulas" tab in Excel, and click on "Name Manager" in the "Defined Names" group. This will open the Name Manager dialog box.
2. Select the Named Range: In the Name Manager dialog box, select the named range you want to check.
3. Copy the Named Range Reference: Click on the "Refers To" box at the bottom of the dialog box. Press Ctrl+C to copy the reference of the named range to the clipboard.
4. Close the Name Manager: Close the Name Manager dialog box.
5. Open the "Find" Dialog: Press Ctrl+F to open the "Find" dialog box.
6. Paste the Named Range Reference: In the "Find what" box of the "Find" dialog box, paste the reference of the named range you copied earlier using Ctrl+V.
7. Search in Formulas: Make sure "Formulas" is selected in the "Within" dropdown menu in the "Find" dialog box. This tells Excel to search within formulas for the reference.
8. Start the Search: Click on "Find All" to start the search.
9. Review Results: Excel will display a list of cells where the named range is referenced in formulas. You can review each reference to determine where the named range is being used.
10. Verify Validity: Check each reference to ensure it is valid and still needed. If the named range is no longer needed, you can safely delete it.

Regarding your second question: Yes, a named range with "#REF!" in the "Refers To" column indicates that the range is invalid, possibly because the source data worksheet was deleted or the data in the original range no longer exists. Before deleting this named range, you should ensure it is not referenced by other worksheets by following the steps outlined above.

If the named range is no longer needed and not referenced by other worksheets, you can safely delete it. Otherwise, you may need to review and potentially modify other worksheets before deleting the named range. The text was revised with the AI.

Formularbeginn

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.