Return list of uncleared checks

B

bem

I have a check register set up in Excel list form, but it is getting pretty
large and cumbersome to navigate. Is there a function/formula that would
return a running list of uncleared transactions...I would like to have an
at-a-glance report that lists uncleared transactions. It would be nice to
see payee and amount but amount would suffice.

If this requires an array formula, please speak s..l...o...w...l...y....

Thanks,
bem
 
R

Ron Coderre

Have you tried using an Autofilter to view only the uncleared checks. You
must have a method for flagging the cleared checks (clear date, "x" in a
cell, etc) that you could use for filtering, right?

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
B

bem

I appreciate the advice, but I was hoping for something a little more
automatic. I know how to use autofilters and pivot tables; I was hoping to
create a separate sheet in the workbook that was linked to the register...may
be I am going about this the wrong way...if I use a "form" and link to the
primary register...uncleared transactions could "live" on that page and once
cleared move to the register?

I need to think about this some more....or learn to use Access.

Thanks for trying, though.
bem
 
B

Biff

Hi!

This can be done easily without filters, pivot tables or VBA code but the
performance would depend on how "large" the file is, ie: How many rows of
data need to be searched for uncleared transactions and how many
transactions need to be compiled.

I can put something together for you if you can send me a sample of your
register. If you're interested let me know how to contact you.

Biff
 
J

jaf

Hi Bem.
I use a sheet with columns setup like this...
(vertical separators use for columns)
DESCRIPTION| CK# |ACCT#| DATE| MEMO| ACCOUNT NAME| DEBIT| POSTED |CREDIT
|BALANCE |Outstanding Debits |Outstanding Credits| Statement Ending Balance

I put the formulas below on separate lines. Hopefully they will copy & paste
easier.
Paste these into row 1000 starting in column J. My data starts at row 5 so
"$5" may have to be changed to match yours.
You can copy them anywhere after. Your columns may be different. The will
correct as long as you get them in the right place first.

Formulas starting with the balance column (J) are..
=J999+I1000-G1000
=IF(OR(H1000="R",H1000="C"),"",IF(OR(B1000="",C1000<>2,C1000<>20),G1000,""))
=IF(OR(H1000="R",H1000="C"),"",IF(I1000="","",I1000))
=IF(OR(H1000="R",H1000="C"),"",J1000+(SUM(K$5:K1000)-SUM(L$5:L1000)))
=IF(OR(H1000="R",H1000="C")," ",(M1000-(SUM(K$5:K1000))+SUM(L$5:L1000)))

What they do is look at column "Posted" which = blank, C (Cleared), or R
(for reconcile when the statement arrives).
I use C to mark transactions if I view my account online.
The "Statement Ending Balance" should match what your bank sees as your
current balance.

Working with them is pretty simple. Just look.
If there are outstanding debits or credits they appear. Otherwise the cells
appear blank.
When you reconcile change the posted column to R as you review each item on
the statement.

On my sheet the ACCT# is used to get the memo, description and account name
data from a table.
 
B

bem

The register is formatted thusly
TYPE (this is for a three letter code I use), CHECK NUMBER, DATE, PAYEE,
ACCT. NO.(from chart of accts.).CLEARED, CREDITS, DEBITS, BALANCE

So...thats nine columns. I have a thousand (and growing) rows. In my
cleared column I use "Y" for cleared, "N" for uncleared, and "XX" for voided
checks.

As for the number of transactions to be compiled---not many per month--a few
deposits and may be a half dozen checks.

I can be reached at ---(ignore spaces) t b i w v (at) y a h o o (dot) c o m

bem
And have I mentioned how much I love you guys...all of you...that take us
feebs by the hand and guide us to the light with your tech wisdom..Thank You
All!!
 
B

Biff

Sent an email.

Biff

bem said:
The register is formatted thusly
TYPE (this is for a three letter code I use), CHECK NUMBER, DATE, PAYEE,
ACCT. NO.(from chart of accts.).CLEARED, CREDITS, DEBITS, BALANCE

So...thats nine columns. I have a thousand (and growing) rows. In my
cleared column I use "Y" for cleared, "N" for uncleared, and "XX" for
voided
checks.

As for the number of transactions to be compiled---not many per month--a
few
deposits and may be a half dozen checks.

I can be reached at ---(ignore spaces) t b i w v (at) y a h o o (dot) c o
m

bem
And have I mentioned how much I love you guys...all of you...that take us
feebs by the hand and guide us to the light with your tech wisdom..Thank
You
All!!
Click to expand...
 
Top