Conditional Formulas/Formatting - Cell Sum/Subtraction

[spurtniq]

Here's my problem, based on my example:

I've got a checkmark in cell A1, which I refer
to in the Post Ref Column by "=A1".

If there's a checkmark in the Post Ref column,
then the entry is a balance (Either Debit or Credit).

There should ALWAYS be TWO and ONLY TWO entries.
One under the Debit OR Credit Column next to Post Ref.
The other under Debit OR Credit under Balance at Top
Right of the example image below.

If the Balance is a Debit, there should be two
Debit column entries. Vice Versa if the Balance is
a Credit.

The next row down, if the Balance above is a
Debit and the entry on THIS row is a Debit, then
Under Balance - Debit, there should be a running
Balance - Debit+Debit.

If the entry is a Credit, then Under Balance - Debit,
there should be a running Balance - Debit-Credit.

If the initial Balance is a Credit, just the opposite
applies. Subsequent Credit entries would be Credit+Credit.
Subsequent Debit entries would be Credit-Debit.

Examples:

Initial Balance:

Debit - 500
Balance (Debit) 500

Subsequent Entries

Credit - 200
Balance (Debit) 300 (500 - 200)

Debit - 400
Balance (Debit) 700 (300+400)

Initial Balance:

Credit - 500
Balance (Credit) 500

Subsequent Entries

Credit - 200
Balance (Credit) 700 (500 + 200)

Debit - 400
Balance (Credit) 300 (700-400)

If there are NO entries ALL cells should be blank.
If there ARE entries ONLY the affected cells should
have entries - ALL other cells should be blank.

What I'm trying to do is write conditional formats
for the Debit and Credit Balance cells respectively
which conforms to these conditions.

I haven't had much luck so far.

Anyone?

Thanx.


+----------------------------------------------------------------+
| Attachment filename: example.jpg |
|Download attachment: http://www.excelforum.com/attachment.php?postid=362261|
+----------------------------------------------------------------+

 

[immanuel]

How exactly would the checkmark in the Post Ref column affect your
calculations?

Based on my understanding of your post, it would seem that if there's a
checkmark, then there will be nothing in the first Credit or Debit column.
In other words, only a column under the balance would be populated if
there's a checkmark in Post Ref. Is that an accurate understanding?

In the meantime, try the following formulae:

Assuming Row 9 is your initial row (i.e. the column in which the checkmark
is located on your screenshot).

In H9:
=IF(F9<>"",F9,"")
In I9:
=IF(G9<>"",G9,"")

Then in H10:
=IF(OR(H9<>"",F10<>""),H9+F10,"")
And in I10:
=IF(OR(I9<>"",G10<>""),I9+G10,"")


If you could elucidate on what function the checkmark serves, I might be
able to refine the formulae to match your requirements.

/i.

 

[spurtniq]

bump