Locations of validation lists

[Pierre]

Have well over 100 dropdowns in cells in a worksheet. Each refers to
a separate validation and selection list on another tab. Is it
possible to go right to or identify each lists' location from within
the cell. Many names are similar and doing maintenance on the lists
involves a great deal of hunt and peck., and I vaguely remember in a
prior version seeing something like "Refers To": sheet name, and
referenced location. Now using Excel 2007. Right now, all I get from
F5 is a list of all the named ranges containing the lists. They are
exceedinglar similar, and can be prone to errors is the wrong field is
selected. Again, looking for list range references from the cell.
TIA.
Pierre

 

[Pete_UK]

Click on the Formulas tab and then Name Manager in the Defined Names
section to see a list of names and the ranges they refer to.

Hope this helps.

Pete

 

[Gord Dibben]

On Ribbon go to Formulas>Name Manager to see a list of named ranges with values
and references.


Gord Dibben MS Excel MVP

 

[Pierre]

Thanks Gord and Pete_UK. Those were helpful posts. Unfortunately,
when clicking on the cell, and viewing the dropdowns available, I am
looking for the range that populates the dropdown, not just it's
contents. So, what range does this cell draw from?

Pierre

 

[Pierre]

Found what I was looking for. From the cell with the dropdown,
Data>Data Validation>Data Validation> on the Settings tab, it will
identify the source.

Many thanks for the use of any grey matter expended.

Pierre

 

[Gord Dibben]

I have revised some JE McGimpsey code.

Simply select a cell then run macro to get message box with activecell address
and source list name.

Sub DataValDocumenter()
'adapted from code posted by J.E. McGimpsey 2005-02-03
'http://www.mcgimpsey.com/excel/index.html
Dim sVal(0 To 2) As Variant
Dim rValidation As Range
Dim sC As String
Dim strDV As String
sC = vbTab
On Error Resume Next
Set rValidation = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rValidation Is Nothing Then
With ActiveCell.Validation
sVal(1) = .Formula1
sVal(2) = .Formula2
strDV = sVal(1)
End With
strDV = sC & sVal(0) & sC & strDV
MsgBox ActiveCell.Address(False, False) & strDV
End If
End Sub

The full code from JE will write all DV to a text file.

Code can be found at Debra Dalgleish's site should you want it.

Very handy for full documentation of DV parameters.

http://www.contextures.on.ca/xlDataVal09.html


Gord

 

[Pierre]

Gord: That code you dug up worked like a charm! Many, many thanks.

Pierre

 

[Gord Dibben]

Good to hear.

Thanks for the feedback and thanks to JE for the code.


Gord