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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StargateFanFromWork - 20 Dec 2006 21:19 GMT

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. :oD

Reply to this Message

Trevor Shuttleworth - 20 Dec 2006 22:50 GMT

Maybe:

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

Regards

Trevor

> 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
[quoted text clipped - 14 lines]
>
> Thanks so much in advance. :oD

Reply to this Message

StargateFan - 21 Dec 2006 23:07 GMT

>Maybe:
>
[quoted text clipped - 3 lines]
>
>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 :oD

>> 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
[quoted text clipped - 14 lines]
>>
>> Thanks so much in advance. :oD

Reply to this Message

StargateFan - 22 Dec 2006 00:44 GMT

>>Maybe:
>>
[quoted text clipped - 17 lines]
>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. :oD

Reply to this Message

Trevor Shuttleworth - 23 Dec 2006 23:24 GMT

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

>>>Maybe:
>>>
[quoted text clipped - 26 lines]
>
> It seems to do the job. tx. :oD

Reply to this Message

StargateFan - 27 Dec 2006 10:49 GMT

>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.
Ordinarily I don't but it has proved safer for this worksheet <g>.

>=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! :oD

>Trevor
>
[quoted text clipped - 28 lines]
>>
>> It seems to do the job. tx. :oD

Reply to this Message