Caught out with cheating by using conditional formatting <g>. How to deal with via formula instead?

[StargateFanFromWork]

Cell C5 is a value from another sheet in the workbook. However, I set
conditional formatting to blank out B5 if B1, which is on the same sheet C5
is on, is blank. Now that I have added a formula that will use the value
from cell C5, this workaround isn't working because C5 is being calculated
whether or not B1 is filled or not, which isn't good. If B1 is empty, C5
should count as a zero value so that the new formula shows the balance
without taking C5 into account.

So instead, how can we deal with putting a value in C5 from the other page
_unless_ B1 if empty. If B1 is empty, C5 should truly read as 0 rather than
my blanking it out via conditional formatting.

The formula in C5 currently looks like this:
=IF(('TO PAY'!$E$14)="",('TO PAY'!$E$13),('TO PAY'!$E$14))

Thanks so much in advance. D

 

[Trevor Shuttleworth]

Maybe:

=IF(B1="",0,IF('TO PAY'!$E$14="",'TO PAY'!$E$13,'TO PAY'!$E$14))

Regards

Trevor

 

[StargateFan]

Maybe:

=IF(B1="",0,IF('TO PAY'!$E$14="",'TO PAY'!$E$13,'TO PAY'!$E$14))

Regards

Trevor

Trevor, thank you. This seems to be working well. It took me about
half an hour to edit and test but all the cells seem to work now.

I ran into trouble with only one, a cell that is a straight copy, as
it were, from another sheet:
='TO PAY'!$B$2

It, too, needs to go to "0" if B1 in the current sheet is blank. I'm
very sorry, I've researched the ng archives again and have done
numerous trials but nothing I've done works. I came close but got a
TRUE and FALSE cell entry rather than the currency one that the above
formula gives <g>. Pls help. Hopefully this will be the last
question for this sheet <g>.

TIA D

 

[StargateFan]

Trevor, thank you. This seems to be working well. It took me about
half an hour to edit and test but all the cells seem to work now.

I ran into trouble with only one, a cell that is a straight copy, as
it were, from another sheet:
='TO PAY'!$B$2

It, too, needs to go to "0" if B1 in the current sheet is blank. I'm
very sorry, I've researched the ng archives again and have done
numerous trials but nothing I've done works. I came close but got a
TRUE and FALSE cell entry rather than the currency one that the above
formula gives <g>. Pls help. Hopefully this will be the last
question for this sheet <g>.

[SNIP]

Hey, I kept going and kept checking the archives and playing around
and, for once, arrived at something that I think works. Is this
syntax okay?
=IF('TO PAY'!$A$2="","",'TO PAY'!$A$2)

It seems to do the job. tx. D

 

[Trevor Shuttleworth]

If it works for you, then it's OK.

You don't necessarily need the absolute references. If you remove the
dollar signs you can copy the formulae and they will adjust automatically.

=IF('TO PAY'!A2="","",'TO PAY'!A2)

The only place it might need to be absolute is the reference to cell B1 in
the original question.

Regards, have a good Christmas and New Year

Trevor

Trevor, thank you. This seems to be working well. It took me about
half an hour to edit and test but all the cells seem to work now.

I ran into trouble with only one, a cell that is a straight copy, as
it were, from another sheet:
='TO PAY'!$B$2

It, too, needs to go to "0" if B1 in the current sheet is blank. I'm
very sorry, I've researched the ng archives again and have done
numerous trials but nothing I've done works. I came close but got a
TRUE and FALSE cell entry rather than the currency one that the above
formula gives <g>. Pls help. Hopefully this will be the last
question for this sheet <g>.

[SNIP]

Hey, I kept going and kept checking the archives and playing around
and, for once, arrived at something that I think works. Is this
syntax okay?
=IF('TO PAY'!$A$2="","",'TO PAY'!$A$2)

It seems to do the job. tx. D

 

[StargateFan]

If it works for you, then it's OK.

Oh, good. It's just that sometimes I'll do something and the syntax
is not quite right even though it works for that particular situation.
But then down the road, the flaws show up when it proves inadequate in
covering unforeseen circumstances. But if the syntax is not totally
cumbersome, then that's okay. Thanks.

You don't necessarily need the absolute references. If you remove the
dollar signs you can copy the formulae and they will adjust automatically.

Thanks. I had so much trouble with this one that I put them in.

=IF('TO PAY'!A2="","",'TO PAY'!A2)

The only place it might need to be absolute is the reference to cell B1 in
the original question.

Regards, have a good Christmas and New Year

Thank you, likewise! D

Trevor

On Wed, 20 Dec 2006 22:50:17 -0000, "Trevor Shuttleworth"

Maybe:

=IF(B1="",0,IF('TO PAY'!$E$14="",'TO PAY'!$E$13,'TO PAY'!$E$14))

Regards

Trevor

Trevor, thank you. This seems to be working well. It took me about
half an hour to edit and test but all the cells seem to work now.

I ran into trouble with only one, a cell that is a straight copy, as
it were, from another sheet:
='TO PAY'!$B$2

It, too, needs to go to "0" if B1 in the current sheet is blank. I'm
very sorry, I've researched the ng archives again and have done
numerous trials but nothing I've done works. I came close but got a
TRUE and FALSE cell entry rather than the currency one that the above
formula gives <g>. Pls help. Hopefully this will be the last
question for this sheet <g>.

[SNIP]

Hey, I kept going and kept checking the archives and playing around
and, for once, arrived at something that I think works. Is this
syntax okay?
=IF('TO PAY'!$A$2="","",'TO PAY'!$A$2)

It seems to do the job. tx. D