Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

=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


Check out our Excel Resources

Question...

Posted by Mark W. on October 29, 2001 1:13 PM
What prevents you from recording a Check and a Deposit
on the same row?


Re: Question...

Posted by Drew Lien on October 29, 2001 5:06 PM
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



Re: Question...

Posted by Mark W. on October 30, 2001 5:07 AM
What prevents you from entering both? And if such
entries occur how do you want the value in column I
to be affected?



Re: =IF...Array?

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.


Re: Question...

Posted by Drew Lien on October 30, 2001 11:59 AM

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



Re: Question...

Posted by Mark W. on October 30, 2001 2:06 PM
My checkbook register allows me to enter check
and a deposit on the same row. : It's just like your checkbook...you enter a



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.