Hiding a fomula for printing purposes

Posted by Mark on November 02, 2000 8:17 AM

I'm using the vlookup in a 12 line quote sheet I created in Excel, I don't always use all 12 lines. If I don't use the line it displays #N/A in the cell, it also goofs up the final "total" cell as it is part of sum formula and displays #N/A(in the Total) unless it's excluded from the Sum.
How do I make is display 0.00 instead of #N/A when not in use?

Thanks

Mark

Posted by Jimmy on November 02, 2000 8:32 AM

Mark I had about the same thing, What I did to make it work was on the sheet where my look-up data was I just added an extra row with the value 0.00 in the first column and left the second column blank, you could probably add 0.00 in the second column also.

Posted by Mark on November 02, 2000 9:21 AM

Didn't work in my case **Need More help please**

Posted by Jimmy on November 02, 2000 10:35 AM

Re: Didn't work in my case **Need More help please**

I tried it and it seems to work, I may be going more simple than you need, This is what I done.

I made a look-up sheet on sheet 2
a b
0 $0.00
item1 $1.00
item2 $2.00
item3 $3.00

Then on sheet1 I have two columns, The first column to enter the item (A1) , the second column with the formula (B1) VLOOKUP(A1,Sheet2!,a1:b4,2,false). Copy this formula into each cell where you want the data returned. Then at the bottom (B5)I just sum the above cells. When I entered item1 into cell A1 it would return the value $1.00 into B1, any cell in column A that I did not enter an item into I had the value of $0.00 in column B. If you like I will email this workbook to look at.

Jimmy

Posted by Celia on November 02, 2000 3:36 PM

Mark
Try this :-

=IF(ISNA(yourformula),0,yourformula)

Celia

Posted by Jimmy on November 03, 2000 7:04 AM

Celia

I like it, wish I had known that one when I did mine. You can tell I am still a rookie

Jimmy