Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank fields | Excel Discussion (Misc queries) | |||
Excel 0 and Blank fields. | Excel Discussion (Misc queries) | |||
Excel 0 and Blank fields. | Excel Discussion (Misc queries) | |||
Excel 0 and Blank fields. | Excel Discussion (Misc queries) | |||
If Statement based on blank fields | Excel Worksheet Functions |