Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with personal budget spreadsheet
Here's my dilemma: I have separate worksheets for different aspects of my budget (i.e. spending money, groceries, gas, etc.) I also have a separate sheet that shows items that have yet to clear my checking account, and this all ties back into the main spreadsheet which shows the budget as a whole and a bottom line of extra I have at the end of each month to put in savings. I have added a column to each sheet that will have a "Y" or "N" which indicates if the charge listed in A=Merchant B= Date and C= amount spent has cleared my account yet. If it has a "N", I would like those 3 columns (A4:C4 for example) to be copied over to the 'yet to clear' sheet in the next blank row. Once it becomes a "Y", that row disappears, so the remaining charges yet to clear are moved to the top of the list. Is this possible? Thanks in advance. -Scott -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=509218 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with personal budget spreadsheet
Here's one non-array formulas play
which could achieve exactly what you're after A sample construct is available at: http://cjoint.com/?chjj5BOkFe Auto copy rows to another sheet_darkwood_wks.xls Assume source data in sheet: X, cols A to C, data from row2 down. The criteria col = col E, wherein the "Y", "N" will be tagged for the lines in X In another sheet: YetToClear With the same headers in A1:C1: Field1, Field2, Field3 Put in A2: =IF(ISERROR(SMALL($D:$D,ROW(A1))),"", INDEX(X!A:A,MATCH(SMALL($D:$D,ROW(A1)),$D:$D,0))) Copy A2 to C2 Put in D2: =IF(X!E2="","",IF(X!E2="N",ROW(),"")) (Leave D1 empty) Select A2:D2, copy down to cover the max expected extent of data in X The above will auto-return only the lines for cols A to C from X where col E = "N", all lines neatly bunched at the top. If the tagging "N" is changed in X to "Y", the particular line will then disappear from YetToClear, and the remaining lines will "move up" (and vice versa, if the tagging is changed from "Y" to "N") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "darkwood" wrote in message ... Here's my dilemma: I have separate worksheets for different aspects of my budget (i.e. spending money, groceries, gas, etc.) I also have a separate sheet that shows items that have yet to clear my checking account, and this all ties back into the main spreadsheet which shows the budget as a whole and a bottom line of extra I have at the end of each month to put in savings. I have added a column to each sheet that will have a "Y" or "N" which indicates if the charge listed in A=Merchant B= Date and C= amount spent has cleared my account yet. If it has a "N", I would like those 3 columns (A4:C4 for example) to be copied over to the 'yet to clear' sheet in the next blank row. Once it becomes a "Y", that row disappears, so the remaining charges yet to clear are moved to the top of the list. Is this possible? Thanks in advance. -Scott -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=509218 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with personal budget spreadsheet
Hi
Not just the answer to your question, but an advice. Your setup with separate sheets for various 'items' is too hard to manage - you'll end up with very complex functions on summary sheet, and whenever you add a new 'item', you have to redesign all. My advice is to keep all entries in a single table. Something like this: You must have a separate sheet p.e. Accounts AccNum, AccName, AccNum , where in column C is the formula like (in C2) =IF(A2="","",A2) , and columns A:B are formatted as text. In this table you define various accounts (your 'items'). My advice is, you group accounts wisely - then it is easy to generate various summary reports later. P.e. you can declare, that all payments are between '1000' and '1999', and all income accounts are between '2000' and '2999'. And fill the accounts table like this (keep account names unique) 1000 electricity bill 1001 1002 gas bill 1002 .... 1101 car tanking 1101 .... 1201 food 1201 .... 2000 starting balance 2000 2001 salary 2001 .... Define dynamic range, which includes all non-empty entries on sheet Accounts in columns B:C (AccTbl2). On your data entry sheet (Transactions), you have a table like Date, AccName, Sum, AccNum , where in column B you use data validation list with source=INDEX(AccTbl2,,1) , and in column D you use VLOOKUP to return according account numbers from range AccTbl2. Somewhere at top your Transactions sheet, you can have a cell, where current balance is claculated =SUMPRODUCT(Sum,--(AccNum="2000"))-SUMPRODUCT(Sum,--(AccNum<"2000")) , where Sum and AccNum are dynamic ranges in Transactions table. Your budget table is almost ready. You only have to design various report sheets, like BudgetMonthly, BudgetAnnual, etc., where you select the year or month (and/or some other criteria), and to where data from Transactions sheet are calculated accordingly selected criteria. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "darkwood" wrote in message ... Here's my dilemma: I have separate worksheets for different aspects of my budget (i.e. spending money, groceries, gas, etc.) I also have a separate sheet that shows items that have yet to clear my checking account, and this all ties back into the main spreadsheet which shows the budget as a whole and a bottom line of extra I have at the end of each month to put in savings. I have added a column to each sheet that will have a "Y" or "N" which indicates if the charge listed in A=Merchant B= Date and C= amount spent has cleared my account yet. If it has a "N", I would like those 3 columns (A4:C4 for example) to be copied over to the 'yet to clear' sheet in the next blank row. Once it becomes a "Y", that row disappears, so the remaining charges yet to clear are moved to the top of the list. Is this possible? Thanks in advance. -Scott -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=509218 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with personal budget spreadsheet
Clarification:
The earlier suggestion essentially presumes only 1 master sheet (i.e. sheet X) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a spreadsheet to budget | Excel Worksheet Functions | |||
Bi-Weekly Personal Budget Template | Excel Discussion (Misc queries) | |||
Spreadsheet merging problems | Excel Worksheet Functions | |||
Find and Replace | Excel Discussion (Misc queries) | |||
Personal format of an excel spreadsheet as a template? | New Users to Excel |