Archive of Mr Excel Message Board


Back to Printing in Excel archive index
Back to archive home

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


Re: Hiding a fomula for printing purposes

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.


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

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


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

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


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


Re: Hiding a fomula for printing purposes

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

Mark
Try this :-

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

Celia


Re: Hiding a fomula for printing purposes

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.