how do I create dependent lists?

K

Kashgarinn

Greetings.

Thank you for reading this, I hope you can help me.

on worksheet 1 I would like to create 3 drop-down lists.

The first drop-down list displays 66 values (in this case names o
galaxies), values which are in column A on worksheet 2. In column B o
worksheet 2, there is a serial number for each of the 66 names.

Example: on worksheet 2, A1 = "derelik" and B1 = 10000001

Here comes my problem:

On worksheet 3 there is a list of constellations within each galaxy
each constellation has its own serial number (for instance "San matar
which has the serial number "20000001"). So on worksheet 3 there is i
column A the galaxy serial number of the constellation, in column
there is the constellation serial number, and in column C there is th
name of the constellation.

Then in worksheet 4, there are the individual names of eac
solarsystem, their respective serial number (for instance: "EH2I-P
serial: 30005108), the constellation serial which they're in, and th
galaxy serial that solarsystem is in.

So what I want to do is this:

From the first drop-down list I choose the galaxy.

From the second drop-down list I get a choice of _only_ th
constellations within that galaxy.

From the third drop-down list I get a choice of _only_ solarsystem
within that constellation.

Is this possible? If so, can you help me?

Thanks for any replies.

K
 
B

Bob Phillips

Hi Kashgarinn,

I have a sample workbook that creates these sort of lists, as many as you
wish. However, the way I have built is to have all of list 3 in column C,
list 2 is in column B, duplicated to align with C, and similarly with A. So
the data might look like

galaxy A const A-A solar A-A-A
galaxy A const A-A solar A-A-B
galaxy A const A-B solar A-B-A
galaxy B const B-A solar B-A-A
galaxy B const B-B solar B-B-A
galaxy B const B-B solar B-B-B

If you are willing to structure your data this way, you are welcome to a
copy of my workbook to implement on.

I have a control toolbox combobox version, and a data validation dropdown
version (no forms or userform combo versions yet). If you want a copy, email
me directly and say which you want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Trev

Hi,
I don't think I am going to be very helpful, but i believe
that you can change the lookup range of the second combo
box from the on_update event of the first. I would opt to
use Access for my database, in which case what you seem to
want to do is fairly easy, having different tables for
galaxies, constellations and solar systems.

Incidentally, as a point of interest for me, are 'solar
systems'in different galaxies 'solar systems'
or 'planetory systems'? I thought 'solar system' uniquely
identified ours

Trev
 
K

Kashgarinn

Bob:

Thanks alot for your reply. Your solution sounds interesting, and I'
love to take a gander at it.

I'm using excelforum.com to check this out, and it didn't display a
e-mail I can reply to.

Please e-mail your solution to [email protected]

Thanks alot for your help.

Trev:

Thanks alot for your reply. I'll check the on_update function, than
you.

I don't really want to use access as I wouldn't first know how t
implement it into excel, and I would like to be able to share my fil
to others without them needing anything more than an excel viewer, o
excel.

It should be rightfully called a planetary star system.

K
 
B

Bob Phillips

Kasggarinn,

Control toolbox combobox or data validation lists? I think the choice would
be dependent upon what you want to do with the final selection, if you just
want to see the value, DV is good enough. If you want to run a macro
dependent upon the planetary system chosen, the control toolbox version
would probably be better.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top