PDA

View Full Version : Creating Dynamic Named Ranges for Dependent Data Validation Lists



zljordan
05-20-2014, 11:23 AM
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
05-20-2014, 11:51 AM
You only need 1 little line of VBA

zljordan
05-20-2014, 01:46 PM
Thank you for the reply. I keep getting an error though.

Aussiebear
05-20-2014, 01:48 PM
What is the error message? Unless you tell us we could well spend all next week guessing.

zljordan
05-20-2014, 02:13 PM
I apologize...it's working correctly now. Thank you!

mikerickson
05-20-2014, 05:01 PM
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",columnOfInterest),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