I have one spreadsheet with all the stock my supplier has available with
retail and trade prices. Columns a part number, description, retail,
trade.

I have just completed another sheet which I want to keep separate with
what stock I have in my workshop.

I wish to add another column to the supplier sheet which looks at my
stock sheet and puts the stock level in.

As I only have a fraction of what the supplier has on offer, there are a
lot of parts I don't have a stock level for. If I use VLOOKUP I can get
the stock level for the parts I have, but for all the parts I don't have
I get #NA. VLOOKUP is looking for an exact match which isn't going to
happen unless I have all the stock my supplier has.

I have tried the IF statement to try to make any #NA cell read zero, but
to no avail.

Anyone got an idea on how to make the #NA cells read zero if I haven't
that particular stock item on my sheet?
--
Gary Ridgway

You could of course use an IF statement like
=IF(ISERROR(vlookupformula),0,vlookupformula)
but I wonder if the #N/A is not more to the point: It tells you that you
don't have the good in your list of stock items, probably because you never
ordered it, while 0 would mean, that the item is in your list, but the
current stock level is zero. For me these are 2 different statuses and you
would loose this important distinction if you set the values for both cases
to zero.

Joerg




"Gary Ridgway" wrote in message
...
I have one spreadsheet with all the stock my supplier has available with
retail and trade prices. Columns a part number, description, retail,
trade.

I have just completed another sheet which I want to keep separate with
what stock I have in my workshop.

I wish to add another column to the supplier sheet which looks at my stock
sheet and puts the stock level in.

As I only have a fraction of what the supplier has on offer, there are a
lot of parts I don't have a stock level for. If I use VLOOKUP I can get
the stock level for the parts I have, but for all the parts I don't have I
get #NA. VLOOKUP is looking for an exact match which isn't going to happen
unless I have all the stock my supplier has.

I have tried the IF statement to try to make any #NA cell read zero, but
to no avail.

Anyone got an idea on how to make the #NA cells read zero if I haven't
that particular stock item on my sheet?
--
Gary Ridgway

You do have a point there Joerg.

In article , Joerg Mochikun
writes
You could of course use an IF statement like
=IF(ISERROR(vlookupformula),0,vlookupformula)
but I wonder if the #N/A is not more to the point: It tells you that you
don't have the good in your list of stock items, probably because you never
ordered it, while 0 would mean, that the item is in your list, but the
current stock level is zero. For me these are 2 different statuses and you
would loose this important distinction if you set the values for both cases
to zero.

Joerg




"Gary Ridgway" wrote in message
...
I have one spreadsheet with all the stock my supplier has available with
retail and trade prices. Columns a part number, description, retail,
trade.

I have just completed another sheet which I want to keep separate with
what stock I have in my workshop.

I wish to add another column to the supplier sheet which looks at my stock
sheet and puts the stock level in.

As I only have a fraction of what the supplier has on offer, there are a
lot of parts I don't have a stock level for. If I use VLOOKUP I can get
the stock level for the parts I have, but for all the parts I don't have I
get #NA. VLOOKUP is looking for an exact match which isn't going to happen
unless I have all the stock my supplier has.

I have tried the IF statement to try to make any #NA cell read zero, but
to no avail.

Anyone got an idea on how to make the #NA cells read zero if I haven't
that particular stock item on my sheet?
--
Gary Ridgway



--
Gary Ridgway

ISERROR will hide all errors, not just the #N/A

Use ISNA function.

=IF(ISNA(vlookup formula)),"no data",vlookup formula))

=IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"no
data",VLOOKUP(G1,$A$1:$F$31,2,FALSE))


Gord Dibben MS Excel MVP

On Wed, 1 Apr 2009 09:56:29 +0100, Gary Ridgway wrote:

You do have a point there Joerg.

In article , Joerg Mochikun
writes
You could of course use an IF statement like
=IF(ISERROR(vlookupformula),0,vlookupformula)
but I wonder if the #N/A is not more to the point: It tells you that you
don't have the good in your list of stock items, probably because you never
ordered it, while 0 would mean, that the item is in your list, but the
current stock level is zero. For me these are 2 different statuses and you
would loose this important distinction if you set the values for both cases
to zero.

Joerg




"Gary Ridgway" wrote in message
...
I have one spreadsheet with all the stock my supplier has available with
retail and trade prices. Columns a part number, description, retail,
trade.

I have just completed another sheet which I want to keep separate with
what stock I have in my workshop.

I wish to add another column to the supplier sheet which looks at my stock
sheet and puts the stock level in.

As I only have a fraction of what the supplier has on offer, there are a
lot of parts I don't have a stock level for. If I use VLOOKUP I can get
the stock level for the parts I have, but for all the parts I don't have I
get #NA. VLOOKUP is looking for an exact match which isn't going to happen
unless I have all the stock my supplier has.

I have tried the IF statement to try to make any #NA cell read zero, but
to no avail.

Anyone got an idea on how to make the #NA cells read zero if I haven't
that particular stock item on my sheet?
--
Gary Ridgway