Better way to make dependent lists from current list that stores numbers

[bbrazeau]

I need to limit the choices in a drop down list (numbers) based on the users selection from another drop down list (numbers). The method I have gotten to work is not optimal in my opinion and I'm hoping someone has a better way to do it. What I'm currently using is the INDIRECT function combined with a =RIGHT(I9,LEN(I9)-2) function to strip off the letters I had to add to make it work. ( the issue with numbers was excel wouldn't let me name a range with a number.) So here's what I have. The user selects from a list that has entries like "A_1" or "A_4.875", then there is another cell that has the formula =RIGHT(I9,LEN(I9)-2)and is formatted as a number. Then there is a series of named ranges that have the numeric values I'll allow for each selection in the 1st list each named range has a name that matches one of the choices from the 1st list "A_1","A_4.875",etc...(This is where excel wouldn't allow me to name the ranges "1" or "4.875") Then there is a 2nd list that uses the INDIRECT function from the 1st list to populate itself. Is there a way to do this without the intermediate list? In a nut shell I want the user to select a length and then have the possible widths limited to the ones available for that length.

 

[Fluff]

If DV1 has numbers then in dv2 use =INDIRECT("A_" &I16)where I16 is the location of DV1

 

[bbrazeau]

Thank You Fluff, But I'd like to eliminate the need for "A_" all together if possible. Let me rephrase my question as I think there's some confusion. What is the best way to have list 1 have 7.875,9.875,11.875,13.375,etc... and list 2 have varying content like (10,15,20.75 for 7.875),(12.5,15,22.875 for 9.875), etc..based on what the user chose in list 1?

 

[Fluff]

If you remove the A_ then you will not be able to use named ranges.
Whilst It's almost certainly possible with formulae, I've no idea how.
For somebody else to help, I suspect that they would need to see how your data is laid out.
There are tools available here that enable you to post data to the board.

 

[bbrazeau]

I would like the green to be able to accept numbers. I would like to eliminate the need for the yellow.
These are not the numbers I'll use , but if the concept can be made to work I'll enter the numbers I need.
Excel 2013 32 bit

	A	B	C	D	E	F	G
1	A_1	1	7	13	19	25	31
2	B_2	2	8	14	20	26	32
3	G_3	3	9	15	21	27	33
4	D_4	4	10	16	22	28	34
5	E_5	5	11	17	23	29	35
6	F_6	6	12	18	24	30	36
7
8
9
10
11				5
12		E_5		22

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[Fluff]

If I understand correctly you don't need col A
in DV1 you set the list range to B1:B6, then create a named range called A_1 which is cells C1:G1, another named range A_2 range C2:G2 etc
and then use what I supplied in post#2

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Main List​	Dep1​	Dep2​	Dep3​	Dep4​	Dep5​		Main Drop​	Dep Drop​
2​	1​	7​	13​	19​	25​	31​		3​	21​
3​	2​	8​	14​	20​	26​	32​
4​	3​	9​	15​	21​	27​	33​
5​	4​	10​	16​	22​	28​	34​
6​	5​	11​	17​	23​	29​	35​
7​	6​	12​	18​	24​	30​	36​

<tbody>
</tbody>

Named ranges
MainList A2:A7 (yellow area)
DepValues B2:F7 (gray area)

Validation
H2
List
Source: MainList

I2
List
Source: =INDEX(DepValues,MATCH(H2,MainList,0),0)

Hope this helps

M.

 

[bbrazeau]

Thank You Fluff! That is exactly what I was looking for.How do I mark this as answered correctly?

 

[Fluff]

Glad we could help & thanks for the feedback.
We don't mark threads as resoled on this site.