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.