|Unfortunately, I can't access Google docs from work. Let's try a brief example and see if that works for you:|
I have a Drop Down validation list in A1 that offers the choice of 100 or 200.
I have a Drop Down validation list in B1 that is dependent on the Drop Down in A1. In other words, the list of choices in B1 will change based on the choice made in A1.
When 100 is chosen in A1, I want the Drop Down in B1 to offer Red, Blue and Green.
When 200 is chosen in A1, I want the Drop Down in B1 to offer Black, Brown, and Orange.
I have this table:
1 Red Black
2 Blue Brown
3 Green Orange
- I select C1:C3.
- I click in the Name Box above Column A, enter my100 and hit Enter.
I have now given C1:C3 a Range Name of my100.
- I select D1:D3.
- I click in the Name Box above Column A, enter my200 and hit Enter.
I have now given D1:D3 a Range Name of my200.
I do this because, as we know, I can't use a number as a Range Name.
Now, for the Drop Down in B1, I use:
Source: =INDIRECT("my" & A1)
The INDIRECT function will concatenate the string my with the number in A1 and use that as the Source for the Drop Down in B1.
When A1 contains 100, B1's Drop Down will be populated with the values in the range named my100, which refers to C1:C3 or Red, Blue, Green.
When A1 contains 200, B1's Drop Down will be populated with the values in the range named my200, which refers to D1:D3 or Black, Brown, Orange.
Does that help?