0's in fields that should be blank
 

Recently upgraded to 2003, a problem has started within a spreadsheet one
department has always used. They run a query in Access and then copy and
paste the results into Excel. The first worksheet is the pasted data, the
second worksheet contains a bunch of links to that data, formats it and
performs additional calculations. Since the upgrade, any field that should
be blank will place a zero in that field. This is a problem for their
calculation. They want the calculated fields to show the error, now they are
performing calculations when they shouldn't. Is there an option that says if
it's blank enter a 0 that could be turned off or how can they avoid this in
the future?

Thanks!

0's in fields that should be blank
 

ToolsOptionsView tab, uncheck Zero Values

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"TJAC" wrote in message ...
| Recently upgraded to 2003, a problem has started within a spreadsheet one
| department has always used. They run a query in Access and then copy and
| paste the results into Excel. The first worksheet is the pasted data, the
| second worksheet contains a bunch of links to that data, formats it and
| performs additional calculations. Since the upgrade, any field that should
| be blank will place a zero in that field. This is a problem for their
| calculation. They want the calculated fields to show the error, now they are
| performing calculations when they shouldn't. Is there an option that says if
| it's blank enter a 0 that could be turned off or how can they avoid this in
| the future?
|
| Thanks!

0's in fields that should be blank
 

Ok, that did take out the 0's; however, I it is still calculating as if there
are 0's there. I also changed the options area for error checking to check
for errors in empty cells. That still won't allow errors to be displayed.
It still calculates as if it were a 0 there.

"Niek Otten" wrote:

ToolsOptionsView tab, uncheck Zero Values

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"TJAC" wrote in message ...
| Recently upgraded to 2003, a problem has started within a spreadsheet one
| department has always used. They run a query in Access and then copy and
| paste the results into Excel. The first worksheet is the pasted data, the
| second worksheet contains a bunch of links to that data, formats it and
| performs additional calculations. Since the upgrade, any field that should
| be blank will place a zero in that field. This is a problem for their
| calculation. They want the calculated fields to show the error, now they are
| performing calculations when they shouldn't. Is there an option that says if
| it's blank enter a 0 that could be turned off or how can they avoid this in
| the future?
|
| Thanks!

0's in fields that should be blank
 

All linked cells will show zero, if you want something that looks like empty
use

=IF(Sheet1!A1="","",Sheet1!A1)

--
Regards,

Peo Sjoblom


"TJAC" wrote in message
...
Ok, that did take out the 0's; however, I it is still calculating as if
there
are 0's there. I also changed the options area for error checking to
check
for errors in empty cells. That still won't allow errors to be displayed.
It still calculates as if it were a 0 there.

"Niek Otten" wrote:

ToolsOptionsView tab, uncheck Zero Values

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"TJAC" wrote in message
...
| Recently upgraded to 2003, a problem has started within a spreadsheet
one
| department has always used. They run a query in Access and then copy
and
| paste the results into Excel. The first worksheet is the pasted data,
the
| second worksheet contains a bunch of links to that data, formats it and
| performs additional calculations. Since the upgrade, any field that
should
| be blank will place a zero in that field. This is a problem for their
| calculation. They want the calculated fields to show the error, now
they are
| performing calculations when they shouldn't. Is there an option that
says if
| it's blank enter a 0 that could be turned off or how can they avoid
this in
| the future?
|
| Thanks!