MrExcel Publishing
Your One Stop for Excel Tips & Solutions

N/A# values for (non) calculation - long (sorry everyone)


Posted by Brendan O'Leary on October 17, 2000 4:32 AM

My situation is that I have a workbook that keeps a running balance on the overseas orders that I have placed. On an individual worksheet I allow for only one order per page, that I begin by displaying my whole range of stock in numerical order.....where all I do is add my desired quantity next to the numerical part number. I then run a macro to delete all part numbers from my sheet that have no quantity next to it to neaten up the page for the purpose of faxing and attatching to email etc. All that is left displayed is the part number and qty I wish to order.

I already have many orders that I've arranged in this fashion, because I like to keep a running balance on on my last worksheet in the workbook as a reference for what has been ordered. Because my supplier (the only one) sends me goods as he has them, not always as ordered, I have quite a time in checking off deliveries that have came today for stock I may have ordered 3 months ago along with the goods I requested last week. This workbook is to help me keep on top of all records.

The way that I chose reference back was just a regular VLOOKUP command displayed in an order by order column arrangement next to the whole list of part numbers that I stock. My problem is that where I have run my macro to take out blank cells with no qty next to the part number, the value N/A# is displayed next to the part number that I did not order for that time in the balance worksheet column and I can't seen to do a simple range calculation along the row with this result being displayed. Is there a way to either change the N/A# value to a 0 or blank (remembering I already have a VLOOKUP command in that cell looking in case I have put a value on the corresponding order)or disregard this all together during a simple sum command?

Thanks in advance

Brendan


Posted by Neil on October 17, 2000 12:14 PM

replace your VLOOKUP with something like this:

=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)),"",VLOOKUP(A1,Sheet2!A1:B3,2,FALSE))

Replace the "" with a 0 if need be