| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the IF Function for 3 conditions
I have a book with 3 sheets.
Sheet 1 has the following: This sheet contains a QTy cell (sheet1!A1), Part cell (sheet1!B1 - this cell uses a Validation Data that allows a LIST and SOURCE =PARTNAME), a Description cell (sheet1!C1), a Price cell (sheet1!D1), and an Extended cell (sheet1!E1). Sheet 2 has the following: This sheet has the Part names - defined as PARTNAME, Part's Descriptions - defined as DESCRIPTION, and Prices for the parts. I need to have several price columns due to location of user. This is where my problem begins...... Defined Names are "Price=Sheet2!E2:E10", "Price2=Sheet2!F2:F10" and "Price3=Sheet2!G2:G10. Example. "Price" is used if the user is from NY, "Price2" is used if the user is from CA and a "Price3" is used if the user is from NJ. The Part Name and Part Description work as required and I am not having any issues issue with them. Sheet 3 has the following: Sheet3!A3 = (this cell uses a Validation Data that allows a LIST and SOURCE =ANSWER), This name "ANSWER" consist of a "Yes" and "No" located further down on this sheet. I have the following formula located in (sheet1!E1)....=IF(SHEET3!A3<"No", LOOKUP(SHEET1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3 ,PARTNUM,PRICE)) This works as long as I have the Part cell populated. Now, to the meat of the problem,,, If my Part cell in Sheet1 is empty, then my Price cell in Sheet1 shows "#N/A". I would like to know if there is a way that I can add a third condition to my formula above in order to get rid of the "#N/A" if my Part cell is empty. Any help is greatly appreciated. -- tech1NJ |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use the IF Function for 3 conditions
You could do a nested IF statement:
=IF(SHEET3!A3="","",IF(SHEET3!A3<"No",LOOKUP(SHEE T1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3,PARTNUM,PR ICE))) -- John C "tech1NJ" wrote: I have a book with 3 sheets. Sheet 1 has the following: This sheet contains a QTy cell (sheet1!A1), Part cell (sheet1!B1 - this cell uses a Validation Data that allows a LIST and SOURCE =PARTNAME), a Description cell (sheet1!C1), a Price cell (sheet1!D1), and an Extended cell (sheet1!E1). Sheet 2 has the following: This sheet has the Part names - defined as PARTNAME, Part's Descriptions - defined as DESCRIPTION, and Prices for the parts. I need to have several price columns due to location of user. This is where my problem begins...... Defined Names are "Price=Sheet2!E2:E10", "Price2=Sheet2!F2:F10" and "Price3=Sheet2!G2:G10. Example. "Price" is used if the user is from NY, "Price2" is used if the user is from CA and a "Price3" is used if the user is from NJ. The Part Name and Part Description work as required and I am not having any issues issue with them. Sheet 3 has the following: Sheet3!A3 = (this cell uses a Validation Data that allows a LIST and SOURCE =ANSWER), This name "ANSWER" consist of a "Yes" and "No" located further down on this sheet. I have the following formula located in (sheet1!E1)....=IF(SHEET3!A3<"No", LOOKUP(SHEET1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3 ,PARTNUM,PRICE)) This works as long as I have the Part cell populated. Now, to the meat of the problem,,, If my Part cell in Sheet1 is empty, then my Price cell in Sheet1 shows "#N/A". I would like to know if there is a way that I can add a third condition to my formula above in order to get rid of the "#N/A" if my Part cell is empty. Any help is greatly appreciated. -- tech1NJ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use the IF Function for 3 conditions
On Jul 30, 11:20 pm, John C <johnc@stateofdenial wrote:
You could do a nested IF statement: =IF(SHEET3!A3="","",IF(SHEET3!A3<"No",LOOKUP(SHEE T1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3,PARTNUM,PR ICE))) -- John C "tech1NJ" wrote: I have a book with 3 sheets. Sheet 1 has the following: This sheet contains a QTy cell (sheet1!A1), Part cell (sheet1!B1 - this cell uses a Validation Data that allows a LIST and SOURCE =PARTNAME), a Description cell (sheet1!C1), a Price cell (sheet1!D1), and an Extended cell (sheet1!E1). Sheet 2 has the following: This sheet has the Part names - defined as PARTNAME, Part's Descriptions - defined as DESCRIPTION, and Prices for the parts. I need to have several price columns due to location of user. This is where my problem begins...... Defined Names are "Price=Sheet2!E2:E10", "Price2=Sheet2!F2:F10" and "Price3=Sheet2!G2:G10. Example. "Price" is used if the user is from NY, "Price2" is used if the user is from CA and a "Price3" is used if the user is from NJ. The Part Name and Part Description work as required and I am not having any issues issue with them. Sheet 3 has the following: Sheet3!A3 = (this cell uses a Validation Data that allows a LIST and SOURCE =ANSWER), This name "ANSWER" consist of a "Yes" and "No" located further down on this sheet. I have the following formula located in (sheet1!E1)....=IF(SHEET3!A3<"No", LOOKUP(SHEET1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3 ,PARTNUM,PRICE)) This works as long as I have the Part cell populated. Now, to the meat of the problem,,, If my Part cell in Sheet1 is empty, then my Price cell in Sheet1 shows "#N/A". I would like to know if there is a way that I can add a third condition to my formula above in order to get rid of the "#N/A" if my Part cell is empty. Any help is greatly appreciated. -- tech1NJ =IF(B1="","What? No Part!",IF(SHEET3!A3<"No",LOOKUP(SHEET1! B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3,PARTNUM,PRICE ))) Ken Johnson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use the IF Function for 3 conditions
Hi John,
Thanks for the quick reply. I have tried your suggestion and got an additional error and it will not allow me to enter the formula as suggested. The error recvd with the suggested formula is : "You have entered too many arguments for this function" By the way I'm using Excel 2000 -- tech1NJ "John C" wrote: You could do a nested IF statement: =IF(SHEET3!A3="","",IF(SHEET3!A3<"No",LOOKUP(SHEE T1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3,PARTNUM,PR ICE))) -- John C "tech1NJ" wrote: I have a book with 3 sheets. Sheet 1 has the following: This sheet contains a QTy cell (sheet1!A1), Part cell (sheet1!B1 - this cell uses a Validation Data that allows a LIST and SOURCE =PARTNAME), a Description cell (sheet1!C1), a Price cell (sheet1!D1), and an Extended cell (sheet1!E1). Sheet 2 has the following: This sheet has the Part names - defined as PARTNAME, Part's Descriptions - defined as DESCRIPTION, and Prices for the parts. I need to have several price columns due to location of user. This is where my problem begins...... Defined Names are "Price=Sheet2!E2:E10", "Price2=Sheet2!F2:F10" and "Price3=Sheet2!G2:G10. Example. "Price" is used if the user is from NY, "Price2" is used if the user is from CA and a "Price3" is used if the user is from NJ. The Part Name and Part Description work as required and I am not having any issues issue with them. Sheet 3 has the following: Sheet3!A3 = (this cell uses a Validation Data that allows a LIST and SOURCE =ANSWER), This name "ANSWER" consist of a "Yes" and "No" located further down on this sheet. I have the following formula located in (sheet1!E1)....=IF(SHEET3!A3<"No", LOOKUP(SHEET1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3 ,PARTNUM,PRICE)) This works as long as I have the Part cell populated. Now, to the meat of the problem,,, If my Part cell in Sheet1 is empty, then my Price cell in Sheet1 shows "#N/A". I would like to know if there is a way that I can add a third condition to my formula above in order to get rid of the "#N/A" if my Part cell is empty. Any help is greatly appreciated. -- tech1NJ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use the IF Function for 3 conditions
works fine for me, essentially, i did the following to your formula....
in front of your formula, i added an IF statement to check and see if your Sheet3!A3 cell was blank, if it was, I want a blank to display, then I added a paren at the end... Did you copy and paste my formula into your cell? -- John C "tech1NJ" wrote: Hi John, Thanks for the quick reply. I have tried your suggestion and got an additional error and it will not allow me to enter the formula as suggested. The error recvd with the suggested formula is : "You have entered too many arguments for this function" By the way I'm using Excel 2000 -- tech1NJ "John C" wrote: You could do a nested IF statement: =IF(SHEET3!A3="","",IF(SHEET3!A3<"No",LOOKUP(SHEE T1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3,PARTNUM,PR ICE))) -- John C "tech1NJ" wrote: I have a book with 3 sheets. Sheet 1 has the following: This sheet contains a QTy cell (sheet1!A1), Part cell (sheet1!B1 - this cell uses a Validation Data that allows a LIST and SOURCE =PARTNAME), a Description cell (sheet1!C1), a Price cell (sheet1!D1), and an Extended cell (sheet1!E1). Sheet 2 has the following: This sheet has the Part names - defined as PARTNAME, Part's Descriptions - defined as DESCRIPTION, and Prices for the parts. I need to have several price columns due to location of user. This is where my problem begins...... Defined Names are "Price=Sheet2!E2:E10", "Price2=Sheet2!F2:F10" and "Price3=Sheet2!G2:G10. Example. "Price" is used if the user is from NY, "Price2" is used if the user is from CA and a "Price3" is used if the user is from NJ. The Part Name and Part Description work as required and I am not having any issues issue with them. Sheet 3 has the following: Sheet3!A3 = (this cell uses a Validation Data that allows a LIST and SOURCE =ANSWER), This name "ANSWER" consist of a "Yes" and "No" located further down on this sheet. I have the following formula located in (sheet1!E1)....=IF(SHEET3!A3<"No", LOOKUP(SHEET1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3 ,PARTNUM,PRICE)) This works as long as I have the Part cell populated. Now, to the meat of the problem,,, If my Part cell in Sheet1 is empty, then my Price cell in Sheet1 shows "#N/A". I would like to know if there is a way that I can add a third condition to my formula above in order to get rid of the "#N/A" if my Part cell is empty. Any help is greatly appreciated. -- tech1NJ |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to use the IF Function for 3 conditions
John, I did just that and it works...I omitted one of my commas...Thanks for
the help... -- tech1NJ "John C" wrote: works fine for me, essentially, i did the following to your formula.... in front of your formula, i added an IF statement to check and see if your Sheet3!A3 cell was blank, if it was, I want a blank to display, then I added a paren at the end... Did you copy and paste my formula into your cell? -- John C "tech1NJ" wrote: Hi John, Thanks for the quick reply. I have tried your suggestion and got an additional error and it will not allow me to enter the formula as suggested. The error recvd with the suggested formula is : "You have entered too many arguments for this function" By the way I'm using Excel 2000 -- tech1NJ "John C" wrote: You could do a nested IF statement: =IF(SHEET3!A3="","",IF(SHEET3!A3<"No",LOOKUP(SHEE T1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3,PARTNUM,PR ICE))) -- John C "tech1NJ" wrote: I have a book with 3 sheets. Sheet 1 has the following: This sheet contains a QTy cell (sheet1!A1), Part cell (sheet1!B1 - this cell uses a Validation Data that allows a LIST and SOURCE =PARTNAME), a Description cell (sheet1!C1), a Price cell (sheet1!D1), and an Extended cell (sheet1!E1). Sheet 2 has the following: This sheet has the Part names - defined as PARTNAME, Part's Descriptions - defined as DESCRIPTION, and Prices for the parts. I need to have several price columns due to location of user. This is where my problem begins...... Defined Names are "Price=Sheet2!E2:E10", "Price2=Sheet2!F2:F10" and "Price3=Sheet2!G2:G10. Example. "Price" is used if the user is from NY, "Price2" is used if the user is from CA and a "Price3" is used if the user is from NJ. The Part Name and Part Description work as required and I am not having any issues issue with them. Sheet 3 has the following: Sheet3!A3 = (this cell uses a Validation Data that allows a LIST and SOURCE =ANSWER), This name "ANSWER" consist of a "Yes" and "No" located further down on this sheet. I have the following formula located in (sheet1!E1)....=IF(SHEET3!A3<"No", LOOKUP(SHEET1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3 ,PARTNUM,PRICE)) This works as long as I have the Part cell populated. Now, to the meat of the problem,,, If my Part cell in Sheet1 is empty, then my Price cell in Sheet1 shows "#N/A". I would like to know if there is a way that I can add a third condition to my formula above in order to get rid of the "#N/A" if my Part cell is empty. Any help is greatly appreciated. -- tech1NJ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use IF function for two conditions | Excel Worksheet Functions | |||
| Max function with conditions | Excel Worksheet Functions | |||
| If Function - two conditions? | Excel Discussion (Misc queries) | |||
| If function with more than 7 conditions | Excel Worksheet Functions | |||
| Two Conditions Function | Excel Worksheet Functions | |||
Linear Mode
