I need help with way to return 0 or N/A to a formula

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

that refers to a sheet that has been deleted. I have a set of spread sheets
that I use to do quotes. Each sheet refers back to one page to give you an
over view of the totals from each sheet. When I don't use a sheet on a
particular job I just delete that sheet/s but I get all those REF# that make
the sheet hard to read. I can't add sheets because the quotes have links to
a master price list in the same workbook!

Marc
 
Since you've broken the formulas, maybe you can just clear those cells.

Select all the cells (ctrl-a (twice in xl2003))
edit|goto|special
Check Formulas
Uncheck Numbers, Text, logicals (keep Errors checked).

click ok.
Notice that only the formulas that evaluate to errors are selected.

Hit the delete key to clear the contents of those cells.

(If you have other formulas that evaluate to errors (#n/a, #div/0!, etc), then
you'll want to be more selective in your range. (Don't do the whole sheet.))
 
I should have mentioned that I want this page to be locked so it can't get
messed up. The only cells that aren't are cells that someone can enter a
number or description.

Marc
 
You could try format|conditional formatting

formula is
=iserror(a1)
(for A1)

use same font color as fill color (white on white???)


Or you could use a formula like:

=IF(ISERROR(INDIRECT("sheet2!a1")),"",INDIRECT("Sheet2!A1"))
 
Back
Top