Creating Dynamic Named Ranges for Dependent Data Validation Lists

[zljordan]

Hello All,

I am trying to create macro that can dynamically name and update named ranges that are being used in dependent data validation lists on a different page. Thus far, I have only been able to dynamically create the level of the data validation, and keep getting an error message when I attempt to select the dependent variable.

I have attached the following workbook to give you a better idea of what I am trying to do.

Thank you,

Zac

[snb]

You only need 1 little line of VBA

[zljordan]

Thank you for the reply. I keep getting an error though.

[Aussiebear]

What is the error message? Unless you tell us we could well spend all next week guessing.

[zljordan]

I apologize...it's working correctly now. Thank you!

[mikerickson]

You don't need VBA. Neither do you need all those named ranges.
Select Data Validation! D2 and then define these names.

Name:colNum RefersTo: =MATCH('Data Validation'!$B2,Data!$1:$1,0)
Name:columnOfInterest RefersTo: =OFFSET(Data!$A:$A,0,colNum-1)
Name:Style RefersTo: =Data!$A$2:INDEX(Data!$A:$A,MATCH("zzzz",Data!$A:$A),1)
Name:ValidationList RefersTo: =INDEX(columnOfInterest,2,1):INDEX(columnOfInterest,MATCH("zzzz",columnOfIn terest),1)

Then, leave the validation on Column B as is (i.e. list validation source =Style)
and change the validation on each cell of column C to List Validation with the source =ValidationList