Excel 2003 Formula Error

[Steve Muir]

Bit of a strange one this.....

I have been sent a workbook which tracks staff absences, holidays etc.

There is a fomula which calculates the total number of days off, holidays
etc. by summing up a specific cell value from each month tab of the workbook.
This works fine but if a user changes one of these cell values (from blank
to 1 for example) but then decides to delete the number entered (leaving the
cell blank again) the calculated "Sum" cell on the summary sheet then
displays a "#Value" error. Try as I might I cannot replicate this error in
Excel 2007 and don't understand why I am getting this error in Excel 2003??
Why not just use 2007 then? Because my boss's PC is running 2003 and it's
for him!

The strange thing is that my collegue who created the sheet, used Excel
2007. I then opened the sheet (also in Excel 2007) and did not get the
#Value error when doing the above. But when my collegue opens the sheet in
Excel 2003 he gets the #Value error???

My question is why and how do I stop the #Value error?

 

[Chip Pearson]

Without seeing the formula, it is hard to diagnose the problem. Are
you sure that when the user clears the cell, he is really clearing the
contents and not typing in a space character? A cell having a space in
it will appear to be empty, but isn't empty and will cause #VALUE
errors in calculations.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Steve Muir]

Hi Chip,

The formula is very simple. It mearly references each cell where a value is
held and sums them.

This is the formula :
='April 2010'!C17+'April 2010'!H17+'April 2010'!M17+'April 2010'!R17+'April
2010'!W17+'May 2010'!F17+'May 2010'!K17+'May 2010'!P17+'May 2010'!U17+'June
2010'!

As I said, the issue is that the error only occurs in Excel 2003 not 2007.
I have checked to see if a space was being used instead of actually deleting
the cell contents and this was not the issue. The cell contents are being
deleted and after they are the formula returns the #Value error.

Without seeing the formula, it is hard to diagnose the problem. Are
you sure that when the user clears the cell, he is really clearing the
contents and not typing in a space character? A cell having a space in
it will appear to be empty, but isn't empty and will cause #VALUE
errors in calculations.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Bit of a strange one this.....

I have been sent a workbook which tracks staff absences, holidays etc.

There is a fomula which calculates the total number of days off, holidays
etc. by summing up a specific cell value from each month tab of the workbook.
This works fine but if a user changes one of these cell values (from blank
to 1 for example) but then decides to delete the number entered (leaving the
cell blank again) the calculated "Sum" cell on the summary sheet then
displays a "#Value" error. Try as I might I cannot replicate this error in
Excel 2007 and don't understand why I am getting this error in Excel 2003??
Why not just use 2007 then? Because my boss's PC is running 2003 and it's
for him!

The strange thing is that my collegue who created the sheet, used Excel
2007. I then opened the sheet (also in Excel 2007) and did not get the
#Value error when doing the above. But when my collegue opens the sheet in
Excel 2003 he gets the #Value error???

My question is why and how do I stop the #Value error?

.