finding named range in data validation

heritagedoorchris

New Member
Joined
Jul 6, 2005
Messages
16
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

New Member
Joined
Jul 6, 2005
Messages
16
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. :rolleyes:

[/quote]
 

heritagedoorchris

New Member
Joined
Jul 6, 2005
Messages
16
heritagedoorchris said:
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. :rolleyes:
Reality is but a shared illusion.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,452
Messages
5,572,209
Members
412,448
Latest member
ManuW
Top