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

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.))



Marc wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote in message
...
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.))



Marc wrote:

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

--

Dave Peterson

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("Sh eet2!A1"))




Marc wrote:

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

"Dave Peterson" wrote in message
...
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.))



Marc wrote:

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

--

Dave Peterson

--

Dave Peterson