Dynamic dropdown lists

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
I would like to create to columns of dropdowns in and excel spreadsheet .
I want to have it so designed that ColA dropdowns determine what selections are available in ColB dropdowns.
For example A:A dropdown list include CPU,Memory,Make. If you select CPU then DropDown in the corresponding B Cell will show different CPU types. If I select Memory in a different cell in ColA then the corresponding cell in ColB will have a dropdown list of Memory Selections.
Any Ideas ?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
On 2002-05-05 15:13, Nimrod wrote:
I would like to create to columns of dropdowns in and excel spreadsheet .
I want to have it so designed that ColA dropdowns determine what selections are available in ColB dropdowns.
For example A:A dropdown list include CPU,Memory,Make. If you select CPU then DropDown in the corresponding B Cell will show different CPU types. If I select Memory in a different cell in ColA then the corresponding cell in ColB will have a dropdown list of Memory Selections.
Any Ideas ?

I'll spare you details as I'm sure you're capable !

list out your lower-level lists and give them all defined named ranges each, named per your upper-level selection possibilities

so your list of memory selections is called "memoery" as it's an option in yoru first list... etc etc etc

then in the cell you want your second dropdown, data validate it as "from list" referring to =indirect(a1).... where a1 was the result of your first dropdown

makes sense ?
 
Upvote 0
Okay J ... here are the details to set it up .. enjoy

For an example let’s create two drop-downs with B1 being dependent on A1. That is to say what is displayed in A1 will determine what is available in the B1 drop-down list.

Step 1: Create 4 lists on Sheet2
A1=CPU
A2=Memory
A3=HDSZ

B1=P2
B2=P3
B3=P4

C1=128
C2=256
C3=512

D1=Gigbyte
D2=Terabyte

Step 2: Highlight each list and name it.
( naming a list is done with the “Insert” …”name” … toolbar command
2.a. The A:A list can be called anything ..this time lets call it “overview”
2.b. The B:B list Must be called “CPU”
2.c. The C:C list Must be called “Memory”
2.d. The D:D list must be called “HDSZ”

Step3: Go to Sheet1 to make the two drop-downs

Step4: Select sheet1!A1 and select “Data”… “validation” from toolbar commands

Step5: in the validation window the “Allow:” drop down = “lists” and in Source textbx type: “=overview” …. Press OK

Step6: To make the B1 drop down repeat step 4 & 5 ACCEPT in Source textbx type: “=INDIRECT(A1)”

Now Your Done !!!!!!!
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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