software

I have 2 drop down lists made, but I want to have one linked to the other
one. For example the first list has types of wood in it.
Bamboo
Maple
Oak

The 2nd one has pricing in it.
1.25
2.50
3.50

If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can that
be done?

Hi Misty,

If the end result for your question is for a cell to display 3.50 after
selecting 'Bamboo', then why not place these lists in your worksheet/book
somewhere. For example:
Column A - Bamboo, Maple, Oak
Column B - 3.50, 2.50, 1.25

if your drop down is in cell a5, then in cell b5 you could type:
=INDEX(A1:B3,MATCH(A5,A1:A3,0),2)

Hope that answers your question.

quot;Mistyquot; wrote:

gt; I have 2 drop down lists made, but I want to have one linked to the other
gt; one. For example the first list has types of wood in it.
gt; Bamboo
gt; Maple
gt; Oak
gt;
gt; The 2nd one has pricing in it.
gt; 1.25
gt; 2.50
gt; 3.50
gt;
gt; If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can that
gt; be done?

Hi Misty,

Looks like you could use VLOOKUP to do that.

=VLOOKUP(F1,A1:B10,2,0)

Where F1 is the drop down with the wood types.
A1:A10 is a list of all the wood types in the drop down list
B1:B10 is the price of each wood type

HTH
Regards,
Howard

quot;Mistyquot; gt; wrote in message
...
gt;I have 2 drop down lists made, but I want to have one linked to the other
gt; one. For example the first list has types of wood in it.
gt; Bamboo
gt; Maple
gt; Oak
gt;
gt; The 2nd one has pricing in it.
gt; 1.25
gt; 2.50
gt; 3.50
gt;
gt; If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can
gt; that
gt; be done?
Thank you! I will work with this and if I can't get it work, I will let you
know.

Thanks again!
Misty

quot;L. Howard Kittlequot; wrote:

gt; Hi Misty,
gt;
gt; Looks like you could use VLOOKUP to do that.
gt;
gt; =VLOOKUP(F1,A1:B10,2,0)
gt;
gt; Where F1 is the drop down with the wood types.
gt; A1:A10 is a list of all the wood types in the drop down list
gt; B1:B10 is the price of each wood type
gt;
gt; HTH
gt; Regards,
gt; Howard
gt;
gt; quot;Mistyquot; gt; wrote in message
gt; ...
gt; gt;I have 2 drop down lists made, but I want to have one linked to the other
gt; gt; one. For example the first list has types of wood in it.
gt; gt; Bamboo
gt; gt; Maple
gt; gt; Oak
gt; gt;
gt; gt; The 2nd one has pricing in it.
gt; gt; 1.25
gt; gt; 2.50
gt; gt; 3.50
gt; gt;
gt; gt; If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can
gt; gt; that
gt; gt; be done?
gt;
gt;
gt;

Thank you! I will work with this and if I can't get it to work, I will let
you know.

Thanks again!
Misty

quot;BigPigquot; wrote:

gt; Hi Misty,
gt;
gt; If the end result for your question is for a cell to display 3.50 after
gt; selecting 'Bamboo', then why not place these lists in your worksheet/book
gt; somewhere. For example:
gt; Column A - Bamboo, Maple, Oak
gt; Column B - 3.50, 2.50, 1.25
gt;
gt; if your drop down is in cell a5, then in cell b5 you could type:
gt; =INDEX(A1:B3,MATCH(A5,A1:A3,0),2)
gt;
gt; Hope that answers your question.
gt;
gt; quot;Mistyquot; wrote:
gt;
gt; gt; I have 2 drop down lists made, but I want to have one linked to the other
gt; gt; one. For example the first list has types of wood in it.
gt; gt; Bamboo
gt; gt; Maple
gt; gt; Oak
gt; gt;
gt; gt; The 2nd one has pricing in it.
gt; gt; 1.25
gt; gt; 2.50
gt; gt; 3.50
gt; gt;
gt; gt; If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can that
gt; gt; be done?

Hi BigPig,

For some reason, I am not having much success. Is there anyway you can send
me an example spreadsheet?

quot;BigPigquot; wrote:

gt; Hi Misty,
gt;
gt; If the end result for your question is for a cell to display 3.50 after
gt; selecting 'Bamboo', then why not place these lists in your worksheet/book
gt; somewhere. For example:
gt; Column A - Bamboo, Maple, Oak
gt; Column B - 3.50, 2.50, 1.25
gt;
gt; if your drop down is in cell a5, then in cell b5 you could type:
gt; =INDEX(A1:B3,MATCH(A5,A1:A3,0),2)
gt;
gt; Hope that answers your question.
gt;
gt; quot;Mistyquot; wrote:
gt;
gt; gt; I have 2 drop down lists made, but I want to have one linked to the other
gt; gt; one. For example the first list has types of wood in it.
gt; gt; Bamboo
gt; gt; Maple
gt; gt; Oak
gt; gt;
gt; gt; The 2nd one has pricing in it.
gt; gt; 1.25
gt; gt; 2.50
gt; gt; 3.50
gt; gt;
gt; gt; If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can that
gt; gt; be done?

Hi BigPig,

For some reason I am having little success. Is there anyway you can send me
a example spreadsheet?

Thanks.
Misty

quot;BigPigquot; wrote:

gt; Hi Misty,
gt;
gt; If the end result for your question is for a cell to display 3.50 after
gt; selecting 'Bamboo', then why not place these lists in your worksheet/book
gt; somewhere. For example:
gt; Column A - Bamboo, Maple, Oak
gt; Column B - 3.50, 2.50, 1.25
gt;
gt; if your drop down is in cell a5, then in cell b5 you could type:
gt; =INDEX(A1:B3,MATCH(A5,A1:A3,0),2)
gt;
gt; Hope that answers your question.
gt;
gt; quot;Mistyquot; wrote:
gt;
gt; gt; I have 2 drop down lists made, but I want to have one linked to the other
gt; gt; one. For example the first list has types of wood in it.
gt; gt; Bamboo
gt; gt; Maple
gt; gt; Oak
gt; gt;
gt; gt; The 2nd one has pricing in it.
gt; gt; 1.25
gt; gt; 2.50
gt; gt; 3.50
gt; gt;
gt; gt; If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can that
gt; gt; be done?

Hi Misty,

I am sorry for not seeing this post until now.

You probably have already figured it out, but I will answer your question as
best as I can.

Unfortunately I can't give you a sample, since I can't attach an example via
this forum. However:

Look at 'data validation'. Debra Dalgleish's site is an awesome reference
for 'data validation'. www.contextures.com/excelfiles.html#DataVal

With her help I have saved myself many headbanging moments.

In a workbook, make a column in 'a' with the heading of 'wood', and then put
in a2 Bamboo, and a3 Maple, and in a4 Oak. Then in column b put a heading of
price, then in b2 put in 3.50, b3 2.50 and b4 1.25. You can format the
numbers as prices (right click, format cells, number, currency).

Part of the key here is to make the column that you are indexing-sorted. It
has to be sorted in ascending order for this example.

In the same worksheet, for example, in cell a10, go to data-validation-allow
list. Then select the range $A$2:$A$4. You don't necessarily have to use data
validation, but it helps.

In cell b10, put in this formula =INDEX(A2:B4,MATCH(A10,A2:A4,0),2)

What this formula does is finds the row that the cell a10 has in it, ie Oak.
Then it selects the data 2 columns over, 1.25.

Match finds the number of the row, index finds the data at a particular
intersection. (row, column)

This is just one example, you could also use vlookup as mentioned by L.
Howard Kittle.

Either way works.

Hi BigPig,

Thank you for responding. I did end up figuring it out. Yeah!!! I have a
more complex formula thread that I need help on. (At least I think its more
complex - heehee).

I need drop down lists that caputre the following:
1-11 12-24 25-99 100-249 250
A Mah 4.20 3.60 3.20 3.10 3.00
Cherry 4.10 3.50 3.10 3.00 2.90
C. Alder and so on.
K. Alder
Maple
R. Oak

Same as last time except there are more links that I need help on.
For example - if I choose A. Mah and then move to the next column and enter
1-11, I need it to link to 4.20 or if I choose 12-24 I need it to link to
3.60. But each wood type has different amounts for the 1-11, 12-24, 25-99,
100-249 and 250 . Make sense?

Any help you could give would be great!

Thanks.
Misty

quot;BigPigquot; wrote:

gt; Hi Misty,
gt;
gt; I am sorry for not seeing this post until now.
gt;
gt; You probably have already figured it out, but I will answer your question as
gt; best as I can.
gt;
gt; Unfortunately I can't give you a sample, since I can't attach an example via
gt; this forum. However:
gt;
gt; Look at 'data validation'. Debra Dalgleish's site is an awesome reference
gt; for 'data validation'. www.contextures.com/excelfiles.html#DataVal
gt;
gt; With her help I have saved myself many headbanging moments.
gt;
gt; In a workbook, make a column in 'a' with the heading of 'wood', and then put
gt; in a2 Bamboo, and a3 Maple, and in a4 Oak. Then in column b put a heading of
gt; price, then in b2 put in 3.50, b3 2.50 and b4 1.25. You can format the
gt; numbers as prices (right click, format cells, number, currency).
gt;
gt; Part of the key here is to make the column that you are indexing-sorted. It
gt; has to be sorted in ascending order for this example.
gt;
gt; In the same worksheet, for example, in cell a10, go to data-validation-allow
gt; list. Then select the range $A$2:$A$4. You don't necessarily have to use data
gt; validation, but it helps.
gt;
gt; In cell b10, put in this formula =INDEX(A2:B4,MATCH(A10,A2:A4,0),2)
gt;
gt; What this formula does is finds the row that the cell a10 has in it, ie Oak.
gt; Then it selects the data 2 columns over, 1.25.
gt;
gt; Match finds the number of the row, index finds the data at a particular
gt; intersection. (row, column)
gt;
gt; This is just one example, you could also use vlookup as mentioned by L.
gt; Howard Kittle.
gt;
gt; Either way works.

Hi Misty,

If I understand your question:
Row 1 has headings for: Wood Types, 1-11 and so on
Where column A is for Wood Types, Column B is for prices on quot;1-11quot;, and so on.

And you want to be able to match a Wood type, against '1-11' etc... in order
to get the appropriate price? Right?

There are several ways to do this, this is just one:

Same as before, your rows and columns of data need to be sorted in ascending
order. Meaning, Wood Types have to be in ascending order, as well as '1-11,
100-249' etc...

In spreadsheet1 column A put in 'Wood Types', and in ascending order the
'WoodTypes'. In column b put it '1-11' and all of the prices down that
column, and then in column c, put in '100-249' etc...

For this example using the information you provided, select cell a16, go
into data validation, allow list, highlight the range of woodtypes. Now
select b16, and do the same for the column headings of '1-11' '100-249' ,
data validation, etc..

In cell c16 put in:
=INDEX(A1:F7,MATCH(A16,A1:A7),MATCH(B16,A1:H1))

Where A1:F7 is the range that you want to index, A16 contains the value of
the Woodtype, and B16 contains the '1-11' etc. The first match in the INDEX
formula is looking for the row number, and the second the column number. Note
I only used the info that you gave me, so I am sure that there is a lot more.
So the cells that I selected as an example, you will probably have to put
somewhere else. Nonetheless, the process is the same.

I hope that this answers your question. Again, there are many different ways
to handle this, this is just one.