ExcelBanter - how do I create dependent lists?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - how do I create dependent lists? (https://www.excelbanter.com/excel-programming/294144-how-do-i-create-dependent-lists.html)

how do I create dependent lists?

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

--
Message posted from http://www.ExcelForum.com

how do I create dependent lists?

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)

"Kashgarinn " wrote in message
...
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 of
galaxies), values which are in column A on worksheet 2. In column B on
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 in
column A the galaxy serial number of the constellation, in column B
there is the constellation serial number, and in column C there is the
name of the constellation.

Then in worksheet 4, there are the individual names of each
solarsystem, their respective serial number (for instance: "EH2I-P"
serial: 30005108), the constellation serial which they're in, and the
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_ the
constellations within that galaxy.

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

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

Thanks for any replies.

K.

---
Message posted from http://www.ExcelForum.com/

how do I create dependent lists?

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

-----Original Message-----
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 of
galaxies), values which are in column A on worksheet 2.
In column B on
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 in
column A the galaxy serial number of the constellation,
in column B
there is the constellation serial number, and in column C
there is the
name of the constellation.

Then in worksheet 4, there are the individual names of
each
solarsystem, their respective serial number (for
instance: "EH2I-P"
serial: 30005108), the constellation serial which they're
in, and the
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_
the
constellations within that galaxy.

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

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

Thanks for any replies.

K.

---
Message posted from http://www.ExcelForum.com/

.

how do I create dependent lists?

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)

"Kashgarinn " wrote in message
...
Bob:

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

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

Please e-mail your solution to

Thanks alot for your help.

Trev:

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

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

It should be rightfully called a planetary star system.

K.

---
Message posted from http://www.ExcelForum.com/

how do I create dependent lists?

Bob:

I'd like to run a macro dependant on what I choose, so the control too
combobox would be great, thanks.

my e-mail:

K

--
Message posted from http://www.ExcelForum.com

how do I create dependent lists?

You may want to take a gander at Debra Dalgleish's Create Dependent Lists:

http://www.contextures.com/xlDataVal02.html

--

Kashgarinn wrote in message ...
Bob:

I'd like to run a macro dependant on what I choose, so the control tool
combobox would be great, thanks.

my e-mail:

K.

---
Message posted from http://www.ExcelForum.com/