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.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.