I'm in the AV business and I am creating a room/speaker worksheet where on
Sheet 1, there is a drop down cell for each room that references a list from
Sheet 2 of (1) available speaker types and (2) the added price so that I can
chage it from time to time. Column A would be Classic, Architectual, Extreme,
Yard and column B would be $0, $400, $600, $800 respectively.

The resulting drop down name on Sheet 1 would be a combination of the 2
columns from Sheet 2. For example "Classic ($0)" or "Yard ($800)" with the
parenthesis.

Once 1 of the 4 is chosen, I would like that cell to have the dollar value
that it represents so that after selecting the type of speaker for each room
there will be a total of added expense at the bottom of the form.

Thank you in advance!

Steve

I think you would need a VLOOKUP formula in the cell adjacent to you DV dropdown
cell that would return the price.

=VLOOKUP(A1,Sheet2!$A$1:$C$4,3,FALSE)

Also on sheet 2 you would need a third column that concatenates the speaker
types and prices.

On Sheet2 insert a column left of column A and enter in A1

=B1&" ($"&C1&")" drag/copy down then copy and paste specialvalues

Now define A1:A4 with a range name like myname.

On sheet1 in your A1 DV list source enter =myname

Pick from list and you get price in B1


Gord Dibben MS Excel MVP

On Tue, 2 Oct 2007 14:23:02 -0700, Steve_Dallas
wrote:

I'm in the AV business and I am creating a room/speaker worksheet where on
Sheet 1, there is a drop down cell for each room that references a list from
Sheet 2 of (1) available speaker types and (2) the added price so that I can
chage it from time to time. Column A would be Classic, Architectual, Extreme,
Yard and column B would be $0, $400, $600, $800 respectively.

The resulting drop down name on Sheet 1 would be a combination of the 2
columns from Sheet 2. For example "Classic ($0)" or "Yard ($800)" with the
parenthesis.

Once 1 of the 4 is chosen, I would like that cell to have the dollar value
that it represents so that after selecting the type of speaker for each room
there will be a total of added expense at the bottom of the form.

Thank you in advance!

Are you saying that you would like to extract the number part of the
cell? If so, then assuming that your pull-down is in G1, then this
formula will give you the numerical part of it:

=-("("&MID(G1,SEARCH("$",G1)+1,255))

so if you have "Yard ($800)" in G1, this will return 800 in the cell
with the formula.

Hope this helps.

Pete

On Oct 2, 10:23 pm, Steve_Dallas
wrote:
I'm in the AV business and I am creating a room/speaker worksheet where on
Sheet 1, there is a drop down cell for each room that references a list from
Sheet 2 of (1) available speaker types and (2) the added price so that I can
chage it from time to time. Column A would be Classic, Architectual, Extreme,
Yard and column B would be $0, $400, $600, $800 respectively.

The resulting drop down name on Sheet 1 would be a combination of the 2
columns from Sheet 2. For example "Classic ($0)" or "Yard ($800)" with the
parenthesis.

Once 1 of the 4 is chosen, I would like that cell to have the dollar value
that it represents so that after selecting the type of speaker for each room
there will be a total of added expense at the bottom of the form.

Thank you in advance!

First, take a look at Debra Dalgleish's Order Form example:
http://www.contextures.com/xlOrderForm01.html

It explains how to select items from a dropdown list and display
corresponding prices.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Steve_Dallas" wrote in message
...
I'm in the AV business and I am creating a room/speaker worksheet where on
Sheet 1, there is a drop down cell for each room that references a list
from
Sheet 2 of (1) available speaker types and (2) the added price so that I
can
chage it from time to time. Column A would be Classic, Architectual,
Extreme,
Yard and column B would be $0, $400, $600, $800 respectively.

The resulting drop down name on Sheet 1 would be a combination of the 2
columns from Sheet 2. For example "Classic ($0)" or "Yard ($800)" with the
parenthesis.

Once 1 of the 4 is chosen, I would like that cell to have the dollar value
that it represents so that after selecting the type of speaker for each
room
there will be a total of added expense at the bottom of the form.

Thank you in advance!