Want to return Data Validation list location

Topic

New Reply

Is there any way (VBA or otherwise) to return the location of the Data Validation list for a cell?

I’m documenting a spreadsheet, particularly the inputs to the calculation. I’d like to create a formula that shows from where each input cell is getting its allowed values. This should not only be quicker than copying the text from the Data Validation dialogue, but it would be robust to changes.

Any info appreciated.

(Excel 2010)

Reply | Quote

Replies

PStephens,

The following User Defined Function will return the address of the validation list of a cell.

If Cell A2 has a validation list of L4 to L8, in cell C2 for example, enter the formula:

=ValidationList(A2)

In a standard module:

Code:

Public Function ValidationList(rng As Range) As String
    ValidationList = rng.Validation.Formula1
End Function

HTH,
Maud

40470-ValidationList

Reply | Quote

Thanks, Maud!

Exactly what I was after.

Reply | Quote