How can I give text a changeable numeric value?
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! |
How can I give text a changeable numeric value?
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! |
How can I give text a changeable numeric value?
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! |
How can I give text a changeable numeric value?
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! |
All times are GMT +1. The time now is 01:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com