T
tech1NJ
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.
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.