Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return list of uncleared checks
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return list of uncleared checks
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 "bem" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return list of uncleared checks
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 "Ron Coderre" wrote: 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 "bem" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return list of uncleared checks
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 "bem" wrote in message ... 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 "Ron Coderre" wrote: 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 "bem" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return list of uncleared checks
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="",C100 0<2,C1000<20),G1000,"")) =IF(OR(H1000="R",H1000="C"),"",IF(I1000="","",I100 0)) =IF(OR(H1000="R",H1000="C"),"",J1000+(SUM(K$5:K100 0)-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. -- John johnf202 at hot mail dot com "bem" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return list of uncleared checks
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!! "Biff" wrote: 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 "bem" wrote in message ... 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 "Ron Coderre" wrote: 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 "bem" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return list of uncleared checks
Sent an email.
Biff "bem" wrote in message ... 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!! "Biff" wrote: 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 "bem" wrote in message ... 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 "Ron Coderre" wrote: 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 "bem" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions | |||
Ooh .. Linking a list to a list to an output cell | Excel Discussion (Misc queries) | |||
counting unique instances of text in a list | Excel Worksheet Functions | |||
Lookup values in a list and return multiple rows of data | Excel Worksheet Functions |