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

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

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

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

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

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