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