Data Validation and Dynamic Named Ranges

Hi Everyone,

This has been killing me all day. I can't seem to figure out how to make this work...

I have numerous lists on Sheet1 of a workbook. They are all named appropriately (ex. F5:F25 = JanuaryContacts, H5:H25 = FebruaryContacts, etc)

On sheet2, column B I have list of each month in a drop down.

I'm trying to set it up so when the user chooses January in Sheet2!B2, the JanuaryContacts list will populate in C2. If they choose February in Sheet2!B2, the FebruaryContacts list will populate.

I am unable to use simple IF statements because there are 12 months.

I've tried a INDIRECT(SUBSTITUTE)) in the data validation but it doesnt work because the lists are on a separate sheet than the drop downs.

I've also tried a INDIRECT(VLOOKUP) using a names chart (see directly below) and that didnt work either.

Column A Column B
January JanuaryContacts
February FebruaryContacts



Could someone please help me figure this out?

Thanks!

Sorry - the original posting was just using examples. I should've said that some of the choices are multiple words.

So instead of "January" in the first drop down, lets say the choice is "January One"

I'd probably have to use a SUBSTITUTE in there, right? I tried doing that and I couldn't get it to work either.

Also, I have a separate list in the workbook titled "Contacts" - Will that affect this formula?

Thanks again!

I apologize NBVC. I am unable to attach files from here (work).

Would someone be able to answer how to do a INDIRECT(SUBSTITUTE) if my original drop down is "January One" and I would like my second drop down to come out as "JanuaryOneContacts" ? Hopefully that will work and I'll be able to get the rest from there.

Thanks!

Ah, yeah, that formula pulls up every contact in the "contacts" list instead of just the ones that go with B2.

Well I know its difficult to help when I am unable to provide a sample workbook.

Thanks for trying!

Hello again,

I have attached a sample document. In column B of Sheet1 I would like each cell to populate drop downs based on what is chosen in column A. (Ex. If "January One" is chosen, then the "JanuaryOneContacts" list should appear in a drop down in column B.

Note: The separate lists in "Background Info" are populated through a simple autofilter/paste macro. I dont think this should affect my problem though.

Thanks!

I keep getting a "The Source currently evaluates to an error. Do you wish to continue?" error. I click Yes and then try the drop down and nothing appears.

Also, I have attached an updated version of the file. I'm very sorry - I forgot to add another little hitch in the problem. I have another tab for classes. It is pretty much the same exact situation as the Contacts. When the user chooses "January One" in column A of sheet1, only the January One Classes should populate in the column D drop down.

Will I have to change the naming convention since "JanuaryOne" is already the name of the JanuaryOne contacts list?

Thanks again!

I've named the columns to match the sample, I've copied the data validation exactly, etc. But for some reason I'm only getting 3 blank options on the column B drop down. Is there a setting or something I might be missing?

Thanks!