finding named range in data validation

[heritagedoorchris]

I have a very large spreadsheet that I am trying to clean up. There are 240 named ranges in the spreadsheet. Some are used in formulas and some in data validation. I trace dependents to locate those used in formulas. Is there a way find the cells that have data validated using named ranges? It does not show up in a dependent trace. I am using Excel2003. Thank you in advance.

 

[nbrcrunch]

menu: Insert > Name > Paste > Paste List

The above will put a list of all named ranges in individual rows within a column starting at whichever cell is currently selected.

 

[heritagedoorchris]

Thank you, but I have my list of named ranges and I created it just as you suggested. This list shows me where they exist in the workbook. To find out where they are used in formulas I can go to the named range, select it, and trace dependents. The problem I have is if a named range is used in data validation only it will show no dependents in a trace. I need to find out which cells in the workbook use the named ranges as data validation. If I delete a named range that shows no dependents and is used in data validation...well, you can see the problem there.

[/quote]

 

[heritagedoorchris]

Thank you, but I have my list of named ranges and I created it just as you suggested. This list shows me where they exist in the workbook. To find out where they are used in formulas I can go to the named range, select it, and trace dependents. The problem I have is if a named range is used in data validation only it will show no dependents in a trace. I need to find out which cells in the workbook use the named ranges as data validation. If I delete a named range that shows no dependents and is used in data validation...well, you can see the problem there.

Reality is but a shared illusion.