Using cell value as range name in list validation

[Aydee]

I am trying to use the value in a cell, A7, as the data validation range name for another cell.

I have created a range called "Category", which is populated with 3 values, "Quality", "Productivity" and "Value". I have also created ranges named "Quality", "Productivity" and "Value". Each of these 3 lists have several items.

The data validation of cell A7 creates a drop down list from the range "Category".

Suppose the user selects "Productivity" in A7. I am trying to get the data validation of cell B7 to create a drop down list with the value of range "Productivity". If they select "Quality" in A7, B7 should provide a list with the values from the range names "Quality".

Any advice or suggestions would be appreciated.

Thank you.

 

[MickG]

Try this:-
Place this code in the Active sheet Code Module.
Click "Alt + f11", Vb Code window shows , Paste code in this window.
Close Vb window.
Run code by Changing value in "A7"

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
Application.EnableEvents = False
Range("B7").ClearContents
[COLOR="Navy"]With[/COLOR] Range("B7").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Application.Transpose(Range(Range("A7").Value).Value), ",")
[COLOR="Navy"]End[/COLOR] With
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

NB:- I have assumed the named ranges were on the same sheet.!!!

 

[MickG]

Try this for Named ranges in another sheet.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A7" [COLOR="Navy"]Then[/COLOR]
Range("B7").ClearContents
[COLOR="Navy"]With[/COLOR] Range("B7").Validation
    .Delete
       [COLOR="Navy"]Set[/COLOR] R = ActiveWorkbook.Names(Target.Value).RefersToRange
    .Add Type:=xlValidateList, Formula1:=Join(Application.Transpose(R.Value), ",")
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick