Dynamic dropdown lists

[Nimrod]

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 ?

[Chris Davison]

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 ?

[Nimrod]

Yesss ! Got it , Tried it .... perfect !
Very compact . Thx

[jmord70]

Um, could you expand on this for me please?
Does this work in excel97?

Thanks,
Juzzy

[Nimrod]

Hi J:
What part of this would you like elaborated. How to set it up?

[Bruno]

jmord70,

Look also at this previous posting :

http://www.mrexcel.com/board/viewtop...pic=40&forum=2

[Chris Davison]

On 2002-05-05 18:50, jmord70 wrote:
Does this work in excel97?

Juzzy,

yep, I use 97

[jmord70]

Yes please Nimrod...that would be great!

[Nimrod]

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 !!!!!!!

[jmord70]

Hi Nimrod,

Forgot to post a thankyou for this one so...

Thanks!!!