Good Morning! I've run into trouble again with an IF situation <g>. I have
two columns, one shows amount owing to an account and the other shows amount
paid to that account, in other words, a debit and a credit column. In
making the balance column, I don't know how to show the balance whether or
not both cells are filled in either column. I have conditional formatting
to blank out, as it were, the balance column until there is a value in the
first column because that's all I know how to do. I'd like to have the
balance column "appear" if there is an amount in either. It might be easier
to see an example:

Debit Credit Balance
$11.14 $11.14 $0.00
$1,837.19 $62.19 $1,775.00

I currently only know how have the balance cells appear with colours other
than the default font and background, etc., of the spreadsheet when either
cell has a value. i.e., the formula for the $1775.00 above in the
conditional formatting is this:
=NOT(ISBLANK($B3))
with custom formatting so that the cell that shows the $1775.00 stands out
with different attributes to the rest of the spreadsheet.

How can I have the $1775.00 show up when either cell is filled, i.e., when
either the cells has a value in it where the $1837.19 is now, or when the
one that has $62.19 is filled. The balance, obviously will show the result
as either positive or negative unless the balance is zero, so that issue is
taken care of in the formula itself. It's just to have the display come up
when either cell has a value in it, and that's where I'm stuck.

I hope I've explained this well enough. TIA! D

"StargateFanFromWork" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Good Morning! I've run into trouble again with an IF situation <g>. I
have
> two columns, one shows amount owing to an account and the other shows
amount
> paid to that account, in other words, a debit and a credit column. In
> making the balance column, I don't know how to show the balance whether or
> not both cells are filled in either column. I have conditional formatting
> to blank out, as it were, the balance column until there is a value in the
> first column because that's all I know how to do. I'd like to have the
> balance column "appear" if there is an amount in either. It might be
easier
> to see an example:
>
> Debit Credit Balance
> $11.14 $11.14 $0.00
> $1,837.19 $62.19 $1,775.00
>
> I currently only know how have the balance cells appear with colours other
> than the default font and background, etc., of the spreadsheet when either
> cell has a value. i.e., the formula for the $1775.00 above in the
> conditional formatting is this:
> =NOT(ISBLANK($B3))
> with custom formatting so that the cell that shows the $1775.00 stands out
> with different attributes to the rest of the spreadsheet.
>
> How can I have the $1775.00 show up when either cell is filled, i.e., when
> either the cells has a value in it where the $1837.19 is now, or when the
> one that has $62.19 is filled. The balance, obviously will show the
result
> as either positive or negative unless the balance is zero, so that issue
is
> taken care of in the formula itself. It's just to have the display come
up
> when either cell has a value in it, and that's where I'm stuck.
>
> I hope I've explained this well enough. TIA! D

The conditional formatting is what needs changing. I'm kept coming back to
the above post and I'm not certain I've been clear enough on that.

Currently, I have these 2 conditions in the "balance" column:
Condition 1: Formula is =(ISBLANK($B3)) -- cell formatting blanks out cell
to conform to empty background of spreadsheet
Condition 2: Formula is =NOT(ISBLANK($B3)) -- cell formatting pops up and
shows cell contents with light yellow background and blue text, etc.

But currently, this only works when B3 has a value entered (debit column).
If there is nothing in B3 but something in C3, the cell is blanked out even
though it shouldn't be.

So I need to change the conditions above with $B3 to reflect the condition
that it can be either _or_ both $B3 and/or $C3 that has a value whereas only
B3 is currently taken under consideration.

hth.

In your conditional format, select "formula is" and use an OR statement:
=OR($B3<>"",$C3<>"")
and this should accomplish what you desire.

However, I think conditional formatting is not the correct approach here.
You really should do one of the following instead:
1) Turn off zero values from being visible on your worksheet, or
2) Your balance column should be formatted in its entirety to change the
defaults to the bolding and/or colors you want, and the formula to calculate
the balance should just say:
=IF(OR($B3<>"",$C3<>""),$B3-$C3,"")


"StargateFanFromWork" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Good Morning! I've run into trouble again with an IF situation <g>. I
> have
>> two columns, one shows amount owing to an account and the other shows
> amount
>> paid to that account, in other words, a debit and a credit column. In
>> making the balance column, I don't know how to show the balance whether
>> or
>> not both cells are filled in either column. I have conditional
>> formatting
>> to blank out, as it were, the balance column until there is a value in
>> the
>> first column because that's all I know how to do. I'd like to have the
>> balance column "appear" if there is an amount in either. It might be
> easier
>> to see an example:
>>
>> Debit Credit Balance
>> $11.14 $11.14 $0.00
>> $1,837.19 $62.19 $1,775.00
>>
>> I currently only know how have the balance cells appear with colours
>> other
>> than the default font and background, etc., of the spreadsheet when
>> either
>> cell has a value. i.e., the formula for the $1775.00 above in the
>> conditional formatting is this:
>> =NOT(ISBLANK($B3))
>> with custom formatting so that the cell that shows the $1775.00 stands
>> out
>> with different attributes to the rest of the spreadsheet.
>>
>> How can I have the $1775.00 show up when either cell is filled, i.e.,
>> when
>> either the cells has a value in it where the $1837.19 is now, or when the
>> one that has $62.19 is filled. The balance, obviously will show the
> result
>> as either positive or negative unless the balance is zero, so that issue
> is
>> taken care of in the formula itself. It's just to have the display come
> up
>> when either cell has a value in it, and that's where I'm stuck.
>>
>> I hope I've explained this well enough. TIA! D
>
> The conditional formatting is what needs changing. I'm kept coming back
> to
> the above post and I'm not certain I've been clear enough on that.
>
> Currently, I have these 2 conditions in the "balance" column:
> Condition 1: Formula is =(ISBLANK($B3)) -- cell formatting blanks out
> cell
> to conform to empty background of spreadsheet
> Condition 2: Formula is =NOT(ISBLANK($B3)) -- cell formatting pops up and
> shows cell contents with light yellow background and blue text, etc.
>
> But currently, this only works when B3 has a value entered (debit column).
> If there is nothing in B3 but something in C3, the cell is blanked out
> even
> though it shouldn't be.
>
> So I need to change the conditions above with $B3 to reflect the condition
> that it can be either _or_ both $B3 and/or $C3 that has a value whereas
> only
> B3 is currently taken under consideration.
>
> hth.
>
>

"KC Rippstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In your conditional format, select "formula is" and use an OR statement:
> =OR($B3<>"",$C3<>"")
> and this should accomplish what you desire.

It did, thank you.

> However, I think conditional formatting is not the correct approach here.
> You really should do one of the following instead:
> 1) Turn off zero values from being visible on your worksheet, or
> 2) Your balance column should be formatted in its entirety to change the
> defaults to the bolding and/or colors you want, and the formula to
calculate
> the balance should just say:
> =IF(OR($B3<>"",$C3<>""),$B3-$C3,"")

Hmmm, sounds good but too complicated for me to figure out with my limited
knowledge. I'll keep this in mind, however, as I always appreciate advise
from people who know much more than I do. <g> I'll work with this as it is
for now and see if I can implement at some future date what you've pointed
out here. Thanks! D

> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Good Morning! I've run into trouble again with an IF situation <g>. I
> > have
> >> two columns, one shows amount owing to an account and the other shows
> > amount
> >> paid to that account, in other words, a debit and a credit column. In
> >> making the balance column, I don't know how to show the balance whether
> >> or
> >> not both cells are filled in either column. I have conditional
> >> formatting
> >> to blank out, as it were, the balance column until there is a value in
> >> the
> >> first column because that's all I know how to do. I'd like to have the
> >> balance column "appear" if there is an amount in either. It might be
> > easier
> >> to see an example:
> >>
> >> Debit Credit Balance
> >> $11.14 $11.14 $0.00
> >> $1,837.19 $62.19 $1,775.00
> >>
> >> I currently only know how have the balance cells appear with colours
> >> other
> >> than the default font and background, etc., of the spreadsheet when
> >> either
> >> cell has a value. i.e., the formula for the $1775.00 above in the
> >> conditional formatting is this:
> >> =NOT(ISBLANK($B3))
> >> with custom formatting so that the cell that shows the $1775.00 stands
> >> out
> >> with different attributes to the rest of the spreadsheet.
> >>
> >> How can I have the $1775.00 show up when either cell is filled, i.e.,
> >> when
> >> either the cells has a value in it where the $1837.19 is now, or when
the
> >> one that has $62.19 is filled. The balance, obviously will show the
> > result
> >> as either positive or negative unless the balance is zero, so that
issue
> > is
> >> taken care of in the formula itself. It's just to have the display
come
> > up
> >> when either cell has a value in it, and that's where I'm stuck.
> >>
> >> I hope I've explained this well enough. TIA! D
> >
> > The conditional formatting is what needs changing. I'm kept coming back
> > to
> > the above post and I'm not certain I've been clear enough on that.
> >
> > Currently, I have these 2 conditions in the "balance" column:
> > Condition 1: Formula is =(ISBLANK($B3)) -- cell formatting blanks out
> > cell
> > to conform to empty background of spreadsheet
> > Condition 2: Formula is =NOT(ISBLANK($B3)) -- cell formatting pops up
and
> > shows cell contents with light yellow background and blue text, etc.
> >
> > But currently, this only works when B3 has a value entered (debit
column).
> > If there is nothing in B3 but something in C3, the cell is blanked out
> > even
> > though it shouldn't be.
> >
> > So I need to change the conditions above with $B3 to reflect the
condition
> > that it can be either _or_ both $B3 and/or $C3 that has a value whereas
> > only
> > B3 is currently taken under consideration.
> >
> > hth.

Glad I could help. Don't forget to indicate that this post was helpful to you.

There is not anything really wrong with using conditional formatting, I just
wanted you to see that it isn't necessary for this situation. I will try to
explain a little better.

Let's say your Balance colum is column E.
Select E2 and change the font to bold, color to blue, whatever formatting
you were looking for.
In E2, type =IF(OR($B3<>"",$C3<>""),$B3-$C3,"") and hit the green check mark
next to the formula bar (this tells Excel to accept your formula but stay on
that cell).
With E2 still selected, put your mouse cursor over the little black box on
the lower right side of E2. This is called a fill handle, and when you put
your cursor over it, your cursor changes from the big white cross to a thin
black cross. Now click that fill handle and drag down as far as you need
(say, E99). Now your formula and all your formatting carry down.



"StargateFanFromWork" wrote:

> "KC Rippstein" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > In your conditional format, select "formula is" and use an OR statement:
> > =OR($B3<>"",$C3<>"")
> > and this should accomplish what you desire.
>
> It did, thank you.
>
> > However, I think conditional formatting is not the correct approach here.
> > You really should do one of the following instead:
> > 1) Turn off zero values from being visible on your worksheet, or
> > 2) Your balance column should be formatted in its entirety to change the
> > defaults to the bolding and/or colors you want, and the formula to
> calculate
> > the balance should just say:
> > =IF(OR($B3<>"",$C3<>""),$B3-$C3,"")
>
> Hmmm, sounds good but too complicated for me to figure out with my limited
> knowledge. I'll keep this in mind, however, as I always appreciate advise
> from people who know much more than I do. <g> I'll work with this as it is
> for now and see if I can implement at some future date what you've pointed
> out here. Thanks! D
>
> > "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > >> Good Morning! I've run into trouble again with an IF situation <g>. I
> > > have
> > >> two columns, one shows amount owing to an account and the other shows
> > > amount
> > >> paid to that account, in other words, a debit and a credit column. In
> > >> making the balance column, I don't know how to show the balance whether
> > >> or
> > >> not both cells are filled in either column. I have conditional
> > >> formatting
> > >> to blank out, as it were, the balance column until there is a value in
> > >> the
> > >> first column because that's all I know how to do. I'd like to have the
> > >> balance column "appear" if there is an amount in either. It might be
> > > easier
> > >> to see an example:
> > >>
> > >> Debit Credit Balance
> > >> $11.14 $11.14 $0.00
> > >> $1,837.19 $62.19 $1,775.00
> > >>
> > >> I currently only know how have the balance cells appear with colours
> > >> other
> > >> than the default font and background, etc., of the spreadsheet when
> > >> either
> > >> cell has a value. i.e., the formula for the $1775.00 above in the
> > >> conditional formatting is this:
> > >> =NOT(ISBLANK($B3))
> > >> with custom formatting so that the cell that shows the $1775.00 stands
> > >> out
> > >> with different attributes to the rest of the spreadsheet.
> > >>
> > >> How can I have the $1775.00 show up when either cell is filled, i.e.,
> > >> when
> > >> either the cells has a value in it where the $1837.19 is now, or when
> the
> > >> one that has $62.19 is filled. The balance, obviously will show the
> > > result
> > >> as either positive or negative unless the balance is zero, so that
> issue
> > > is
> > >> taken care of in the formula itself. It's just to have the display
> come
> > > up
> > >> when either cell has a value in it, and that's where I'm stuck.
> > >>
> > >> I hope I've explained this well enough. TIA! D
> > >
> > > The conditional formatting is what needs changing. I'm kept coming back
> > > to
> > > the above post and I'm not certain I've been clear enough on that.
> > >
> > > Currently, I have these 2 conditions in the "balance" column:
> > > Condition 1: Formula is =(ISBLANK($B3)) -- cell formatting blanks out
> > > cell
> > > to conform to empty background of spreadsheet
> > > Condition 2: Formula is =NOT(ISBLANK($B3)) -- cell formatting pops up
> and
> > > shows cell contents with light yellow background and blue text, etc.
> > >
> > > But currently, this only works when B3 has a value entered (debit
> column).
> > > If there is nothing in B3 but something in C3, the cell is blanked out
> > > even
> > > though it shouldn't be.
> > >
> > > So I need to change the conditions above with $B3 to reflect the
> condition
> > > that it can be either _or_ both $B3 and/or $C3 that has a value whereas
> > > only
> > > B3 is currently taken under consideration.
> > >
> > > hth.
>
>
>