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.

"Misty" wrote:

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,

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

"Misty" wrote in message
...
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?

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

Thanks again!
Misty

"L. Howard Kittle" wrote:

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

"Misty" wrote in message
...
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?

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

Thanks again!
Misty

"BigPig" wrote:

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.

"Misty" wrote:

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 BigPig,

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

"BigPig" wrote:

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.

"Misty" wrote:

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 BigPig,

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

Thanks.
Misty

"BigPig" wrote:

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.

"Misty" wrote:

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,

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'. http://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

"BigPig" wrote:

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'. http://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 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 "1-11", 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.

Hi BigPig,

I think I led you in the wrong direction. If I choose A Mah, then I would
need to choose between 1-11, 12-24, 25-99, 100-249, 250+. Then I would want
the prices of 4.20, 3.60, 3.20, 3.10 and 3.00 to autofill when choosing from
1-11, 12-24 and so on. I need the forumla to be able to do that for each type
of wood. I only have 3 columns for drop downs. Wood Type, Panel Qty and
Price.

Can that work? If that is the formula you gave me, I am having difficulty
getting it to work.

I also create the drop down boxes on a different spreadsheet than where the
actualy drop down boxes appear. (I do the validation thing and that works, to
get them to appear on the other page - I just can't get the formula to work.)

I apologize for being a pain. I appreciate all the help you have given me
so far.

Thanks.
Misty

"BigPig" wrote:

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 "1-11", 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.

Hi Misty,

The example I provided earlier was all on the same spreadsheet. So when you
are using data validation, index and match, in a different spreadsheet as you
have found out it doens't work.

Here's what you can do:
You can use the formula 'offset' along with named cell ranges and data
validation.
It sounds a lot more complicated than it is.

1. Put you data in sheet 2. Ie column a, a1=WoodTypes, a2=A Mah, a3=Cherry,
a4=Oak

2. Highlight the cells b1 to f1, right click, format cells, text.

3. Cells b1 to f1, 100-249, 1-11, 12-24, 250+, 25-99
The reason for the mix, is they are actually 'sorted' in ascending order

4. Populate the rest of the cells with the corresponding prices accordingly.
In the example I used, I only used A Mah through Cherry, and 100-249 to
25-99, and used your price range and some other prices that seemed right.

5. In Sheet1 select cell A1, go to Insert, name, define, type in WoodTypes,
in the refers to field select the little table on the right, select the
sheet2 tab, and select the cell range a2 through a4 in my example, in yours I
am sure it's longer. Click on the little box to the right of the define
name/refers to field, click add, and close.

6. With a1 still selected go to data validation, allow, list, and in the
field 'source' type in =WoodTypes. Now cell a1 should have a little triangle
next to it.

7. Repeat the process for the €˜number of sheets starting with cell b1 in
sheet1, don't put any spaces in your defined name. Meaning, use No_Of_Sheets,
in lieu of No. Of Sheets. With Naming cell ranges, you cant have any spaces.
Likewise, when using Data validation, you cant refer to cells outside of the
active sheet, unless they are €˜named.

8. Now in cell c1 of sheet1 type in:
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A4),MATCH(B1, Sheet2!B1:F1))

What this formula essentially does is selects a cell x amount of rows, and x
amount of columns from the reference, in this case cell A1 in sheet2.

I picked this method because of the reference to sheets that you were using.
Since excel is math based, it searches through data thats sorted. When you
combine symbols like €“ and +, it will mess up your equation, thats why I
suggested formatting your €˜no of sheets range as text.

To find out more about data validation and named cell ranges, refer to Debra
Dalgleishs site: http://www.contextures.com/excelfiles.html#DataVal

As I mentioned in an earlier post, her website is more than helpful.

Let me know how everything turns out.

Hi - Its me...again

I got the formula to work, but its not pulling the right amounts for each
type of wood. For example: if I click on A. Mah and choose 1-11, its not
giving me the correct price. How would you suggest I trouble shoot that?

Thanks.
Misty

"BigPig" wrote:

Hi Misty,

The example I provided earlier was all on the same spreadsheet. So when you
are using data validation, index and match, in a different spreadsheet as you
have found out it doens't work.

Here's what you can do:
You can use the formula 'offset' along with named cell ranges and data
validation.
It sounds a lot more complicated than it is.

1. Put you data in sheet 2. Ie column a, a1=WoodTypes, a2=A Mah, a3=Cherry,
a4=Oak

2. Highlight the cells b1 to f1, right click, format cells, text.

3. Cells b1 to f1, 100-249, 1-11, 12-24, 250+, 25-99
The reason for the mix, is they are actually 'sorted' in ascending order

4. Populate the rest of the cells with the corresponding prices accordingly.
In the example I used, I only used A Mah through Cherry, and 100-249 to
25-99, and used your price range and some other prices that seemed right.

5. In Sheet1 select cell A1, go to Insert, name, define, type in WoodTypes,
in the refers to field select the little table on the right, select the
sheet2 tab, and select the cell range a2 through a4 in my example, in yours I
am sure it's longer. Click on the little box to the right of the define
name/refers to field, click add, and close.

6. With a1 still selected go to data validation, allow, list, and in the
field 'source' type in =WoodTypes. Now cell a1 should have a little triangle
next to it.

7. Repeat the process for the €˜number of sheets starting with cell b1 in
sheet1, don't put any spaces in your defined name. Meaning, use No_Of_Sheets,
in lieu of No. Of Sheets. With Naming cell ranges, you cant have any spaces.
Likewise, when using Data validation, you cant refer to cells outside of the
active sheet, unless they are €˜named.

8. Now in cell c1 of sheet1 type in:
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A4),MATCH(B1, Sheet2!B1:F1))

What this formula essentially does is selects a cell x amount of rows, and x
amount of columns from the reference, in this case cell A1 in sheet2.

I picked this method because of the reference to sheets that you were using.
Since excel is math based, it searches through data thats sorted. When you
combine symbols like €“ and +, it will mess up your equation, thats why I
suggested formatting your €˜no of sheets range as text.

To find out more about data validation and named cell ranges, refer to Debra
Dalgleishs site: http://www.contextures.com/excelfiles.html#DataVal

As I mentioned in an earlier post, her website is more than helpful.

Let me know how everything turns out.

Hi Misty,

Did you sort the '1-11', '12-24' and so on? A way to do it is:

1. Make sure that the cells that hold the 'No of Sheets', are formatted as
text.

2. Highlight the range minus the 'WoodTypes' Column.

3. Go to 'Data', 'Sort', 'Options', 'Sort Left to Right'.

Let me know if this works.

One more thing,

Another thing I thought might be:

What cell did you select as your reference for 'offset'? Assuming that
everything else is sorted, then it may be that your 'reference' cell needs to
be adjusted to a different cell. The reference cell as in: 'A1', see below.
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A4),MATCH(B1, Sheet2!B1:F1))

Where cell 'A1' is a reference point from where 'offset' searches x amount
of columns 'match(b1,sheet2!b1:f1)', and rows 'match(a1, sheet2!a2:a4)', from
the reference cell 'sheet2!A1'.

"Misty" wrote:

Hi - Its me...again

I got the formula to work, but its not pulling the right amounts for each
type of wood. For example: if I click on A. Mah and choose 1-11, its not
giving me the correct price. How would you suggest I trouble shoot that?

Thanks.
Misty

"BigPig" wrote:

Hi Misty,

The example I provided earlier was all on the same spreadsheet. So when you
are using data validation, index and match, in a different spreadsheet as you
have found out it doens't work.

Here's what you can do:
You can use the formula 'offset' along with named cell ranges and data
validation.
It sounds a lot more complicated than it is.

1. Put you data in sheet 2. Ie column a, a1=WoodTypes, a2=A Mah, a3=Cherry,
a4=Oak

2. Highlight the cells b1 to f1, right click, format cells, text.

3. Cells b1 to f1, 100-249, 1-11, 12-24, 250+, 25-99
The reason for the mix, is they are actually 'sorted' in ascending order

4. Populate the rest of the cells with the corresponding prices accordingly.
In the example I used, I only used A Mah through Cherry, and 100-249 to
25-99, and used your price range and some other prices that seemed right.

5. In Sheet1 select cell A1, go to Insert, name, define, type in WoodTypes,
in the refers to field select the little table on the right, select the
sheet2 tab, and select the cell range a2 through a4 in my example, in yours I
am sure it's longer. Click on the little box to the right of the define
name/refers to field, click add, and close.

6. With a1 still selected go to data validation, allow, list, and in the
field 'source' type in =WoodTypes. Now cell a1 should have a little triangle
next to it.

7. Repeat the process for the €˜number of sheets starting with cell b1 in
sheet1, don't put any spaces in your defined name. Meaning, use No_Of_Sheets,
in lieu of No. Of Sheets. With Naming cell ranges, you cant have any spaces.
Likewise, when using Data validation, you cant refer to cells outside of the
active sheet, unless they are €˜named.

8. Now in cell c1 of sheet1 type in:
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A4),MATCH(B1, Sheet2!B1:F1))

What this formula essentially does is selects a cell x amount of rows, and x
amount of columns from the reference, in this case cell A1 in sheet2.

I picked this method because of the reference to sheets that you were using.
Since excel is math based, it searches through data thats sorted. When you
combine symbols like €“ and +, it will mess up your equation, thats why I
suggested formatting your €˜no of sheets range as text.

To find out more about data validation and named cell ranges, refer to Debra
Dalgleishs site: http://www.contextures.com/excelfiles.html#DataVal

As I mentioned in an earlier post, her website is more than helpful.

Let me know how everything turns out.

Yeah!!! Its working.

I got to looking at my drop down list for the 1-11, 12-24, 25-99 and so on
and realized that 100-249 wasn't showing up. It was because I hadn't
captured that cell while making my list.

Thank you sooo very much!

I love this site!

"BigPig" wrote:

One more thing,

Another thing I thought might be:

What cell did you select as your reference for 'offset'? Assuming that
everything else is sorted, then it may be that your 'reference' cell needs to
be adjusted to a different cell. The reference cell as in: 'A1', see below.
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A4),MATCH(B1, Sheet2!B1:F1))

Where cell 'A1' is a reference point from where 'offset' searches x amount
of columns 'match(b1,sheet2!b1:f1)', and rows 'match(a1, sheet2!a2:a4)', from
the reference cell 'sheet2!A1'.

"Misty" wrote:

Hi - Its me...again

I got the formula to work, but its not pulling the right amounts for each
type of wood. For example: if I click on A. Mah and choose 1-11, its not
giving me the correct price. How would you suggest I trouble shoot that?

Thanks.
Misty

"BigPig" wrote:

Hi Misty,

The example I provided earlier was all on the same spreadsheet. So when you
are using data validation, index and match, in a different spreadsheet as you
have found out it doens't work.

Here's what you can do:
You can use the formula 'offset' along with named cell ranges and data
validation.
It sounds a lot more complicated than it is.

1. Put you data in sheet 2. Ie column a, a1=WoodTypes, a2=A Mah, a3=Cherry,
a4=Oak

2. Highlight the cells b1 to f1, right click, format cells, text.

3. Cells b1 to f1, 100-249, 1-11, 12-24, 250+, 25-99
The reason for the mix, is they are actually 'sorted' in ascending order

4. Populate the rest of the cells with the corresponding prices accordingly.
In the example I used, I only used A Mah through Cherry, and 100-249 to
25-99, and used your price range and some other prices that seemed right.

5. In Sheet1 select cell A1, go to Insert, name, define, type in WoodTypes,
in the refers to field select the little table on the right, select the
sheet2 tab, and select the cell range a2 through a4 in my example, in yours I
am sure it's longer. Click on the little box to the right of the define
name/refers to field, click add, and close.

6. With a1 still selected go to data validation, allow, list, and in the
field 'source' type in =WoodTypes. Now cell a1 should have a little triangle
next to it.

7. Repeat the process for the €˜number of sheets starting with cell b1 in
sheet1, don't put any spaces in your defined name. Meaning, use No_Of_Sheets,
in lieu of No. Of Sheets. With Naming cell ranges, you cant have any spaces.
Likewise, when using Data validation, you cant refer to cells outside of the
active sheet, unless they are €˜named.

8. Now in cell c1 of sheet1 type in:
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A4),MATCH(B1, Sheet2!B1:F1))

What this formula essentially does is selects a cell x amount of rows, and x
amount of columns from the reference, in this case cell A1 in sheet2.

I picked this method because of the reference to sheets that you were using.
Since excel is math based, it searches through data thats sorted. When you
combine symbols like €“ and +, it will mess up your equation, thats why I
suggested formatting your €˜no of sheets range as text.

To find out more about data validation and named cell ranges, refer to Debra
Dalgleishs site: http://www.contextures.com/excelfiles.html#DataVal

As I mentioned in an earlier post, her website is more than helpful.

Let me know how everything turns out.

Hi Misty,

I am glad that everything worked out for you!

---BigPig---

You just thought you were done with me!!!

Ok - I was asked to see if there is anyway to link the drop down boxes of
wood types together. I have the same drop down box in several cells in
different parts of the worksheet, and I was wondering if there is anyway to
connect them to where if we choose "maple" in the first drop down list, can
it auto filter to the other areas of the worksheet?

Thanks!
Misty

"BigPig" wrote:

Hi Misty,

I am glad that everything worked out for you!

---BigPig---

Hi Misty,

Do you mean that you have drop downs in several different sheets that refer
to the same types of data?

If that's the case, couldn't you put all the wood types, no of sheets, and
prices in one sheet?

Please explain in more detail.

Hi Misty,

Another thing I thought of...

If your other drop downs will be identical to eachother, then you could just
refer to the cells that hold the drop downs, and the cell that holds the
formula.

Meaning, if your drop downs and offset formula are in a1:c1, then in cell
a10, type in =a1, in cell b10 type in =b1, in cell c10 type in =c1

Hope that answers your question.