I want to use the IF function to auto-populate a 4th cell (A4 , worksheet 1)
by using several Define Names (PARTNUM,PRICE, and SPECIAL) AND based on a YES
or NO selection from B2 on worksheet 2.

1) If cell A1 on worksheet 1 is not blank (I currently have A4, worksheet 1
program as follows =IF (A1<"", LOOKUP(A1,PARTNUM,PRICE),"")
2) If cel B2 on worksheet 2 is <"No" (I currently have A4, worksheet 1
program as follows =IF (A1<"", LOOKUP(A1,PARTNUM,SPECIAL).

These 2 functions are working together, however, I have the problem that if
A1, worksheet 1 is blank then A4, worksheet 1 returns a "N/A". I need to use
the 2 logics above in addition to gettting rid of "N/A" when A1 is blank. I
have tried nesting the 2 logics above in addition to adding the "" at the end
of the logic but no luck. HELP please.

Your help is greatly appreciated it in advance
--
tech1NJ

Switch it around to get rid of the N/A:
=IF(A1="","",LOOKUP(A1,PARTNUM,PRICE))
Or maybe to cover both zero and blank in A1:
=IF(OR(A1="",A1=0),"",LOOKUP(A1,PARTNUM,PRICE))

I can't tell what you want for the 2 logical questions, though. In
your description I think you left out some AND or OR statements that
would explain better. I think you mean something like this. Which
LOOKUP goes to which?

- IF A1 is not blank, and Sheet2!B2 is not "No", do xxx.
- IF A1 is not blank, and Sheet2!B2 is "No", do yyy.

Thanks for the quick reply Spiky.

Let me try and be a bit more detailed. 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.

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 have the cell empty if
nothing is selected from my Part cell.

I have the following formula located in (sheet1!E1)....=IF(SHEET3!A3<"No",
LOOKUP(SHEET1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3 ,PARTNUM,PRICE)).

I hope that this helps and as before, thank you for your asssitance in
this.....
--
tech1NJ


"Spiky" wrote:

Switch it around to get rid of the N/A:
=IF(A1="","",LOOKUP(A1,PARTNUM,PRICE))
Or maybe to cover both zero and blank in A1:
=IF(OR(A1="",A1=0),"",LOOKUP(A1,PARTNUM,PRICE))

I can't tell what you want for the 2 logical questions, though. In
your description I think you left out some AND or OR statements that
would explain better. I think you mean something like this. Which
LOOKUP goes to which?

- IF A1 is not blank, and Sheet2!B2 is not "No", do xxx.
- IF A1 is not blank, and Sheet2!B2 is "No", do yyy.

Busy week, this time I'm not as fast!

So, your real issue (or first issue) is the empty cell messing up
other formulas. I'm not sure which formula is in the Price cell in
Sheet1!D1, but this should get rid of that error. You would just put
in your entire formula, minus the = sign:
=IF(B1="","",yourformula)

If the error still pops up, it is because of your formula, not because
of the empty B1. And that would need different testing.

Problem solved. Thanks
--
tech1NJ


"tech1NJ" wrote:

Thanks for the quick reply Spiky.

Let me try and be a bit more detailed. 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.

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 have the cell empty if
nothing is selected from my Part cell.

I have the following formula located in (sheet1!E1)....=IF(SHEET3!A3<"No",
LOOKUP(SHEET1!B1,PARTNAME,PRICE2),LOOKUP(SHEET3!A3 ,PARTNUM,PRICE)).

I hope that this helps and as before, thank you for your asssitance in
this.....
--
tech1NJ


"Spiky" wrote:

Switch it around to get rid of the N/A:
=IF(A1="","",LOOKUP(A1,PARTNUM,PRICE))
Or maybe to cover both zero and blank in A1:
=IF(OR(A1="",A1=0),"",LOOKUP(A1,PARTNUM,PRICE))

I can't tell what you want for the 2 logical questions, though. In
your description I think you left out some AND or OR statements that
would explain better. I think you mean something like this. Which
LOOKUP goes to which?

- IF A1 is not blank, and Sheet2!B2 is not "No", do xxx.
- IF A1 is not blank, and Sheet2!B2 is "No", do yyy.