Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I get Excel to automatically chose one of two answers?
I am trying to design an automobile purchase order form that uses option
codes and its corresponding prices. When I enter one option code on one cell/row (i.e. LCB4 for Leather Upholstery), the cell next to it will automatically kick out the correct price of $1450. However, another option code (i.e. ZPP for an automobile Premium Package) at the price of $2900 includes Leather Upholstery. If ordered, I need to enter ZPP on another cell/row that will generate the priceof $2900 next to ZPP; however, I do not know how to get Excel to automatically recognize the ZPP code and change my Leather Price of $1450 to $0 since it is included in the Premium Package. Can anyone assist? Thanks again. -- Erwin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I get Excel to automatically chose one of two answers?
Hi Erwin,
Maybe something like this in F1 which returns a price from range J1:K5 for LCB4. =IF(E2="zpp",0,VLOOKUP(E1,J1:K5,2,0)) Where column E is where you are listing the options E1 = LCB4 E2 = ZPP (or not) HTH Regards, Howard "Erwin" wrote in message ... I am trying to design an automobile purchase order form that uses option codes and its corresponding prices. When I enter one option code on one cell/row (i.e. LCB4 for Leather Upholstery), the cell next to it will automatically kick out the correct price of $1450. However, another option code (i.e. ZPP for an automobile Premium Package) at the price of $2900 includes Leather Upholstery. If ordered, I need to enter ZPP on another cell/row that will generate the priceof $2900 next to ZPP; however, I do not know how to get Excel to automatically recognize the ZPP code and change my Leather Price of $1450 to $0 since it is included in the Premium Package. Can anyone assist? Thanks again. -- Erwin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I get Excel to automatically chose one of two answers?
I mocked up a scenario and used VLOOKUPs to price various option codes.
For the LCB4 code, leather seats, I used this formula: =IF(SUMPRODUCT(--(B2:B6="ZPP"))0,0,VLOOKUP(B6,I6:J10,2,0)) The formula looks for the existence of ZPP in the range where option codes are entered. If it finds ZPP, it returns $0 for leather seats. If ZPP is not found, it performs the VLOOKUP for LCB4 as usual. In this mockup, B2:B6 is the range that holds option codes; I6:J10 is the array holding the option code and the dollars associated with them. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I get Excel to automatically chose one of two answers?
Hi Howard,
Thank you so very much! Very very helpful. Erwin -- Erwin "L. Howard Kittle" wrote: Hi Erwin, Maybe something like this in F1 which returns a price from range J1:K5 for LCB4. =IF(E2="zpp",0,VLOOKUP(E1,J1:K5,2,0)) Where column E is where you are listing the options E1 = LCB4 E2 = ZPP (or not) HTH Regards, Howard "Erwin" wrote in message ... I am trying to design an automobile purchase order form that uses option codes and its corresponding prices. When I enter one option code on one cell/row (i.e. LCB4 for Leather Upholstery), the cell next to it will automatically kick out the correct price of $1450. However, another option code (i.e. ZPP for an automobile Premium Package) at the price of $2900 includes Leather Upholstery. If ordered, I need to enter ZPP on another cell/row that will generate the priceof $2900 next to ZPP; however, I do not know how to get Excel to automatically recognize the ZPP code and change my Leather Price of $1450 to $0 since it is included in the Premium Package. Can anyone assist? Thanks again. -- Erwin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I get Excel to automatically chose one of two answers?
Hi Dave,
Thank you so much too! Very very helpful. Not knowing all the functions of Excel, I was stumped for the longest time. Best regards, Erwin -- Erwin "Dave O" wrote: I mocked up a scenario and used VLOOKUPs to price various option codes. For the LCB4 code, leather seats, I used this formula: =IF(SUMPRODUCT(--(B2:B6="ZPP"))0,0,VLOOKUP(B6,I6:J10,2,0)) The formula looks for the existence of ZPP in the range where option codes are entered. If it finds ZPP, it returns $0 for leather seats. If ZPP is not found, it performs the VLOOKUP for LCB4 as usual. In this mockup, B2:B6 is the range that holds option codes; I6:J10 is the array holding the option code and the dollars associated with them. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel Changing Numbers Automatically | Excel Discussion (Misc queries) | |||
How do i update hyperlink in excel spread sheet automatically. | Excel Discussion (Misc queries) | |||
Need Answers to Urgent Questions on Excel! Hurry, oh, please hurry! | Excel Discussion (Misc queries) | |||
Can you automatically insert next sequential number in an excel fo | Excel Discussion (Misc queries) |