how do you add cell values to drop down list for other cells

Hi there,

I hope I put this so its easy to understand so here goes.

I want to make a drop down list (easy, Done)

But this is the hard part I cant work out. I want to be able to have other
values attached to the drop down list that appear in other cells. eg. below.

In Cell A1 is the drop down list. And I want to add values to the items in
that list that show up in the cells C1-C8 depending on what it makes.

eg.
in the drop down list say
wooden box
wooden barral

So for wooden box I want to have in C1 2(for the number of planks needed
in C2 I want 20 for nails
in C3 1 for a handle

But when I choose the barral from the drop down list I want it to say
10 in C1 for planks
50 in C2 for nails
0 in C3 for the handle

I know it can be done as I have seen a sheet like it but its protected with
a password and I dont know whos it is.

So can anyone explane how to do it, if needed I can try and explane better
with a pick or put the sheep up so you can see it.

You could use a VLOOKUP function, to look up the values based on the choice
in the dropdown.

Use a lookup table on which the VLOOKUP operates (you can put it aside in
hidden columns or on another sheet if you want)

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"chizgiz" wrote:

Hi there,

I hope I put this so its easy to understand so here goes.

I want to make a drop down list (easy, Done)

But this is the hard part I cant work out. I want to be able to have other
values attached to the drop down list that appear in other cells. eg. below.

In Cell A1 is the drop down list. And I want to add values to the items in
that list that show up in the cells C1-C8 depending on what it makes.

eg.
in the drop down list say
wooden box
wooden barral

So for wooden box I want to have in C1 2(for the number of planks needed
in C2 I want 20 for nails
in C3 1 for a handle

But when I choose the barral from the drop down list I want it to say
10 in C1 for planks
50 in C2 for nails
0 in C3 for the handle

I know it can be done as I have seen a sheet like it but its protected with
a password and I dont know whos it is.

So can anyone explane how to do it, if needed I can try and explane better
with a pick or put the sheep up so you can see it.

Are all wooden boxes and barrels the same size?

Simple VLOOKUP table could return the results per your examples.

If boxes or barrels have different sizes you would need to identify the size
in a second dropdown before returning the results.

i.e. select wooden box from the dropdown.........a second dropdown appears
where you select the size.

The the VLOOKUP formulas will return the results.

For more on VLOOKUP and Dependent lists see Debra Dalgleish's site.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

On Thu, 24 Jul 2008 13:35:00 -0700, chizgiz
wrote:

Hi there,

I hope I put this so its easy to understand so here goes.

I want to make a drop down list (easy, Done)

But this is the hard part I cant work out. I want to be able to have other
values attached to the drop down list that appear in other cells. eg. below.

In Cell A1 is the drop down list. And I want to add values to the items in
that list that show up in the cells C1-C8 depending on what it makes.

eg.
in the drop down list say
wooden box
wooden barral

So for wooden box I want to have in C1 2(for the number of planks needed
in C2 I want 20 for nails
in C3 1 for a handle

But when I choose the barral from the drop down list I want it to say
10 in C1 for planks
50 in C2 for nails
0 in C3 for the handle

I know it can be done as I have seen a sheet like it but its protected with
a password and I dont know whos it is.

So can anyone explane how to do it, if needed I can try and explane better
with a pick or put the sheep up so you can see it.