help with personal budget spreadsheet

[darkwood]

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

 

[Max]

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 A22, 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")

 

[Arvi Laanemets]

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.

 

[Max]

Clarification:
The earlier suggestion essentially presumes only 1 master sheet (i.e. sheet
X)

---