=IF...Array?
Posted by Drew Lien on October 29, 2001 12:46 PM
I'm making a General Ledger that is an on-screen
check book. The entries are:
9-A---B------C----D--------E--------F------G-------H-------I---
10 X Check# Date Acct# Description Debit Deposit Balance Cleared
In I10, I put the formula =IF(A10="","OPEN", F10).
What this does is display "OPEN" when a check is
written. This means the check has not yet been cashed.
Then, when you call the bank's voice-mail, and the
check is cashed, you enter "X" in column A10. This
results in the "Cleared" column of cell I10 showing
the amount of the check that cleared.
MY QUESTION IS...I also want the "Cleared" column
to reference deposits. Not the dollar amount, but
the word "POSTED".
1) Currently, when you enter a deposit, the "I" column
displays "0.00". This is good.
2) So, if row 10 is a deposit, and you then enter an
"X" in A10, I want the "Cleared" column I10 to
display the word "POSTED".
This, in addition to my original formula for checks.
Any and all help will be most appreciated! I thank
you kindly,
-Drew
Posted by Mark W. on October 29, 2001 1:13 PM
Question...
What prevents you from recording a Check and a Deposit
on the same row?
Posted by Drew Lien on October 29, 2001 5:06 PM
Re: Question...
Thanks for taking an interest....the
Check (Debit, Column F) and Deposit (Column G)
are indeed on the same row. You enter either
one or the other, beginning with Check# (Column B)
and Date (Column C).
But, it's one or the other; you can't enter both
a check and a deposit on the same row.
-Drew
Posted by Mark W. on October 30, 2001 5:07 AM
Re: Question...
What prevents you from entering both? And if such
entries occur how do you want the value in column I
to be affected?
Posted by giacomo on October 30, 2001 5:22 AM
I think this is what you're looking for:
=IF(A10="X",IF(F10<0,F10,"POSTED"),"OPEN")
This assumes that your Debit is a negative number.
Posted by Drew Lien on October 30, 2001 11:59 AM
Re: Question...
debit on row 10, and a deposit on row 11,
and so on.
If for some reason $500 was entered in row 10
in column F's "DEBIT", and $500 was also
entered in row 10, column G's "DEPOSIT" column,
the two should cancel each other out and the
previous balance from row 9, column H's
"BALANCE" should repeat.
-Drew
What prevents you from entering both? And if such
Posted by Mark W. on October 30, 2001 2:06 PM
Re: Question...
My checkbook register allows me to enter check
and a deposit on the same row. : It's just like your checkbook...you enter a
