LINKING CELLS

  • Thread starter Thread starter Maureen
  • Start date Start date
M

Maureen

I have a workbook with a worksheet for each month and a
summary analysis for each month on separate worksheets. I
am now working on the year-to-date summary and have
encountered the following problem:

I am referencing some cells in each monthly summary
analysis that contain a formula that returns a blank cell
if the result is "0". This returns a #VALUE!. When the
source cell is not blank, it returns the correct numerical
value.

I would like to continue to obtain a blank cell in the
year-to-date summary if the source cell is blank as a
result of a formula.

What to do... what to do?

Thanks.
 
Maureen said:
I have a workbook with a worksheet for each month and a
summary analysis for each month on separate worksheets. I
am now working on the year-to-date summary and have
encountered the following problem:

I am referencing some cells in each monthly summary
analysis that contain a formula that returns a blank cell
if the result is "0". This returns a #VALUE!. When the
source cell is not blank, it returns the correct numerical
value.

I would like to continue to obtain a blank cell in the
year-to-date summary if the source cell is blank as a
result of a formula.

What to do... what to do?

Thanks.
Click to expand...

There are two possible approaches.

The one I prefer is to use
Tools > Options > View tab, uncheck "Zero values"
so that formula results that are zero are displayed as blanks. The important
point is that their values remain as zero, so subsequent formulas work
properly. I think this is better than writing formulas that return a null
string "" (note that this is not a blank, but displays as such) and then
having to cope with that result subsequently.

The alternative is to construct your year-to-date formulas to cope with the
existence of null strings (which are seen as text) in what would otherwise
be numeric data. How you do this depends on the formula you are using, which
you have not quoted. A simple SUM formula (e.g. =SUM(A1:A12)) will cope
automatically. As yours doesn't, it will probably mean adding in an IF
function in some way. Post a reply with the formula you are using if you
need more help.
 
Back
Top