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

B

bbrazeau

New Member
Joined
Jun 19, 2018
Messages
4
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
If DV1 has numbers then in dv2 use =INDIRECT("A_" &I16)where I16 is the location of DV1
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFG

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #92D050"]A_1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]31[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #92D050"]B_2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]32[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #92D050"]G_3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]33[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #92D050"]D_4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]34[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #92D050"]E_5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]35[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #92D050"]F_6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]36[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #92D050"]E_5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
 
Upvote 0
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
 
Upvote 0
Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Main List​
[/TD]
[TD]
Dep1​
[/TD]
[TD]
Dep2​
[/TD]
[TD]
Dep3​
[/TD]
[TD]
Dep4​
[/TD]
[TD]
Dep5​
[/TD]
[TD][/TD]
[TD]
Main Drop​
[/TD]
[TD]
Dep Drop​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #FFFF00"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
7​
[/TD]
[TD="bgcolor: #D9D9D9"]
13​
[/TD]
[TD="bgcolor: #D9D9D9"]
19​
[/TD]
[TD="bgcolor: #D9D9D9"]
25​
[/TD]
[TD="bgcolor: #D9D9D9"]
31​
[/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
21​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD="bgcolor: #FFFF00"]
2​
[/TD]
[TD="bgcolor: #D9D9D9"]
8​
[/TD]
[TD="bgcolor: #D9D9D9"]
14​
[/TD]
[TD="bgcolor: #D9D9D9"]
20​
[/TD]
[TD="bgcolor: #D9D9D9"]
26​
[/TD]
[TD="bgcolor: #D9D9D9"]
32​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD="bgcolor: #FFFF00"]
3​
[/TD]
[TD="bgcolor: #D9D9D9"]
9​
[/TD]
[TD="bgcolor: #D9D9D9"]
15​
[/TD]
[TD="bgcolor: #D9D9D9"]
21​
[/TD]
[TD="bgcolor: #D9D9D9"]
27​
[/TD]
[TD="bgcolor: #D9D9D9"]
33​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD="bgcolor: #FFFF00"]
4​
[/TD]
[TD="bgcolor: #D9D9D9"]
10​
[/TD]
[TD="bgcolor: #D9D9D9"]
16​
[/TD]
[TD="bgcolor: #D9D9D9"]
22​
[/TD]
[TD="bgcolor: #D9D9D9"]
28​
[/TD]
[TD="bgcolor: #D9D9D9"]
34​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD="bgcolor: #FFFF00"]
5​
[/TD]
[TD="bgcolor: #D9D9D9"]
11​
[/TD]
[TD="bgcolor: #D9D9D9"]
17​
[/TD]
[TD="bgcolor: #D9D9D9"]
23​
[/TD]
[TD="bgcolor: #D9D9D9"]
29​
[/TD]
[TD="bgcolor: #D9D9D9"]
35​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD="bgcolor: #FFFF00"]
6​
[/TD]
[TD="bgcolor: #D9D9D9"]
12​
[/TD]
[TD="bgcolor: #D9D9D9"]
18​
[/TD]
[TD="bgcolor: #D9D9D9"]
24​
[/TD]
[TD="bgcolor: #D9D9D9"]
30​
[/TD]
[TD="bgcolor: #D9D9D9"]
36​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback.
We don't mark threads as resoled on this site.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Dependent Validation List to Reference an external Workbook
Replies
0
Views
321
julhs
J
M
  • Question Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
2K
MissDB101
M
N
  • Question Question
Excel VBA copy data to a worksheet in list form
Replies
0
Views
93
NewbieExcel123
N
wizau
  • Solved
Nested IF statements, or a better way?
Replies
4
Views
334
wizau
wizau
M
  • Question Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
437
bobaol
B

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top