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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,001
Messages
5,834,820
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top