Dynamic dependent drop menus

Hi Guys,

I'm creating a set of linked dependent drop menus, but I'm running in an annoying problem.
I will explain step by step what I'm doing and where I get the error from excel.


1) the first drop menu list contains AT15; AU10; AT55.
2) So I created a first name called : PLANT and so far so good.

Now I need to create three names for the three options of the first drop menu.

3) SO I created the name AT15 that should contain let's says (apple; pears; bananas)

But I get an obstinate message error from excel telling that I cannot use the NAME AT15.
It works if I change both the fist menu entry with into AT and I created the NAME AT but with AT15 doesn't work!! and I actually need AT15 in the menu...

is it because the combination AT (TEXT)+ 15 (number)???
I cannot find any other explanation.

Could you please help me out?

because AT15 is a valid cell reference so Excel cannot differentiate from your named range:

what is specific about the name AT15? try AT_15

thanks guys and thanks TMS I think you are 100%, but the problem is that I will use the spreadsheet with the menus to upload data directly into our system so AT_15 want work

AT15 must be there or the upload will fail.

any suggestions please?

If AT15 is the drop down (in A1) then in your dependent (B1) use:

=INDIRECT(LEFT(A1,2)&"_" & RIGHT(A1,2))

with named range of AT_15

thanks TMS, I didn't have time to test it, I will shortly

thanks TMS and John, I didn't have time to test it, I will shortly ...and sorry for the late reply!