Excel error: "This type of reference cannot be used in a Data Validation formula"

I have been successfully using a formula to populate an in-cell dropdown using Data Validation, List. The formula looks like this:

=IF(sel_ActionIndex=2, lstDraftRefs, IF(sel_ActionIndex=3, lstFinalRefs, 0))

Basically, a dynamic drop-down list appears in a cell based on a previous selection (Final or Draft).

I need to replicate this on another tab, so I made the list names local:

=IF(sel_ActionIndex=2, dbFTW!lstDraftRefs, IF(sel_ActionIndex=3, dbFTW!lstFinalRefs, 0))

Excel then gives me the error message, and I'm wondering how I can get this to work. I've not seen any documentation that local names cannot be used in a Data Validation formula. Any suggestions appreciated.

1 Answer

Try this:

Option Explicit
Option Base 1
' You can access the Lists from all Sheets of your Workbook!
Public lstDraftRefs As Validation
Public lstFinalRefs As Validation
Public Sub CreateValidation(ByVal SH As String, _ ByRef R As Range, ByRef C As Range, _ ByRef X As Validation, ByVal InputMessage As String)
Dim N As Long
Dim V As Variant
Dim I As Integer
Dim J As Integer N = 0 For Each V In C I = C.Row J = C.Column N = N + 1 Next If (N <> 1) Then Exit Sub ActiveSheet.Cells(I, J).Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & SH & "!" & R.Address .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = InputMessage .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set X = Selection.Validation
End Sub
Public Sub X()
Dim R As Range
Dim C As Range ThisWorkbook.Worksheets(1).Activate Set R = ThisWorkbook.ActiveSheet.Range("$A$2:$A$10") ThisWorkbook.Worksheets(2).Activate Set C = ThisWorkbook.ActiveSheet.Range("$A$1") Call CreateValidation("Sheet1", R, C, lstDraftRefs, "Input lstDraftRefs") ThisWorkbook.Worksheets(1).Activate Set R = ThisWorkbook.ActiveSheet.Range("$B$2:$B$10") ThisWorkbook.Worksheets(3).Activate Set C = ThisWorkbook.ActiveSheet.Range("$A$1") Call CreateValidation("Sheet1", R, C, lstFinalRefs, "Input lstFinalRefs")
End Sub

Have fun.

Do not change the "Set"s to "Let"s or "ByRef"s to "ByVal"s.