|re: yes that is what exactly I need to do.|
And that is exactly what I suggested in my first response.
Since I don't know your level of Excel expertise, I'll explain it in more detail. Nothing here is meant to be "degrading" but since I don't know what you know, I can't assume anything.
The "trick" behind Dependent Validation Lists is to use Named Ranges. A Named Range is a cell of range cells that been given a Name so that you can refer to it by that name instead of its cell references.
Example Using Named Ranges:
1 - Enter the number 1 through 10 in A1 through A10
2 - Select A1:A5.
3 - In the box above Column A, where it says A1, type in the word Type1 and hit Enter. Note: In order for the Name to be applied to that Range, you must hit Enter and not just click away from the box.
4 - Select A6:A10 and Name it Type2 using the instructions in Step 3 above.
5 - In B1 enter = SUM(A1:A5) and you should get 15.
6 - In B2 enter = SUM(Type1) and you should get 15 again because Excel will recognize Type1 as the Name of the Range A1:A5.
7 - In B3 enter =SUM(Type2) and you should get 40.
8 - In B4 enter =SUM(Type3) and you should get #NAME? because there is no Range named Type3.
The INDIRECT function
Excel provides an INDIRECT function so that you can refer to text in a cell and have Excel interpret it as a reference to a Range.
Using the Named Range example from above, try this:
9 - In C1 enter Type1
10 - In C2 enter Type2
10 - In C3 enter =SUM(C1) and you should get 0 since there is nothing in C1 for Excel to SUM.
11 - In C4 enter =SUM(INDIRECT(C1)) and you should get 15 because the INDIRECT function tells Excel that Type1 is a Range Name, which it can then SUM.
Dependent Validation Lists
OK, so back to the main issue.
You want to have a Validation List (VL) that is dependent on another VL. In order to do that, the Dependent VL (DVL) has to change the range of cells that it gets it data from depending on what has been selected in the Main VL.
Since we can't enter a list of Ranges for it to choose from, we need a method that automatically changes the range that it refers to.
To do that, we combine the use of Named Ranges and the INDIRECT function.
11 - In D1, create a VL using C1:C2 as the source so that you can select either Type1 or Type2.
12 - In E1, create a Vl using =INDIRECT(D1) as the source.
Note: If D1 is empty when you create the VL in E1, you'll get an error message. That's OK, just click Yes.
Now, when you select Type1 or Type2 from the drop down in D1, Excel will use that value in the INDIRECT function and recognize it as a Named range and populate the VL in E1 with the values from that range.
I hope this helps and if you have any more questions, come on back.
P.S. Yhe only reason I used SUBSTITUTE in my first response is because you had spaces in your example (Type 1). Since we can't read minds, we can only assume that you meant what you posted. I had to deal with the space since Range Names can't contain spaces. What I was suggesting was that you name your ranges Type1 and Type2 and then deal with the space by replacing Type 1 with Type1 via the SUBSTITUTE function so that INDIRECT would recognize the Name.