Formatting Cell that displays sum from other worksheets

[Ty]

I have cells that display the sums from other worksheets. As it stand
right now if there is no value in the other worksheet the a 0
appearers in the totals cell. What I would like to do if possible is
instead of there being a 0 in the totals cell I would like it just to
be blank.

I have set up conditional formatting on the total cells to change the
font color if the value is greater than 0 but cannot seem to get rid
of the zero if there is no value to display.

Thanks,

Ty

 

[Chip Pearson]

Without seeing your formula, an applicable solution is just a guess.
The following formula will sum A1:A10 if there are any numeric values
in the range. If the range is empty or has only text data, it returns
an empty string.

=IF(COUNT(A1:A10)>0,SUM(A1:A10),"")

You can do something very similar with individual cells rather than
ranges. The following formula looks at A1, A3, and A5 and sums them if
there is a numeric entry. Otherwise, it returns an empty string.

=IF(COUNT(A1,A3,A5)>0,SUM(A1,A3,A5),"")

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ty]

The current format is as follows.

Assume a worksheet named "Ted" and a worksheet named 'Totals".

On the Ted sheet there is a totals column AH. So lets say that Cell
AH5 = 100

On the Totals sheet there is a Ted column with a corresponding cell 5
the correct formula in this cell is =Ted!AH5

It would display 100 in this case. If there was nothing entered in the
cell AH5 on the Ted it would display 0 which I would like to get rid
of and just have a blank cell.

This really is only a cosmetic thing to de-clutter the page as there
are allot of cells and columns in the form.

Thanks,
Ty

 

[Gord Dibben]

=IF(Ted!AH5="","",Ted!AH5)


Gord Dibben MS Excel MVP