Collecting data from a moving field. (I think)

[dobbin004]

Hello all,

I am building a balance sheet for my personal finances. New year, new start and all that jolly stuff. Instead of using a pre-made sheet I want to learn to make it myself.

I have set up a layout that auto fills in data in Column H when data is added to either column F or G, simple stuff. However I have a workbook that contains months of the year and each new month will pull the data from the last transaction from for example, cell H4.

However I would like to add a "watcher" that will follow column "H" and the rows.

So in short I like data from a cell in column "H" for example "H4" from sheet 1 to show on sheet 2 "H1" and if data is entered into cell "H5" excel will "see" this and report that data to sheet 2 "H1" instead.

Really sorry if that doesn't make sense.

[ArviLaanemets]

Why make this in such complicated way?

On some separate sheet, p.e. SetUp, store start balance, and name the cell containing it p.e. as nBalance0

On sheet Transactions, you'll have a transactions table, with headers like
TransactionTime, TransactionComment, TransactionType, TransactionValue. You may have a calculated column CurrBalance (like your column H) there too.

When you have this table for personal finances, then probably you can have transactions for several years in same table, you may consider the table too big when number of rows exceeds 20000. To avoid scrolling down for new entries, use autofilter to hide all entries older than p.e. a month.

Better is to enter TransactionTime always as date and time, with every transaction time unique - this allows to calculate CurrBalance independently from sorting. Otherwise you have to add additional column(s), and this is more work, than adding some time to date.

TransactionType is optional column. You can define different types to make it possible to get different reports.

TransactionValue has positive values for income, and negative for costs.

For CurrBalance you enter the formula (I use Table formula syntax here, on fly)

=Balance0+SUMIFS([TransactionValue],[TransactionTime],"<=" & [@TransactionTime])

I advice to leave at top of sheet some empty rows, where you can enter a SUBTOTAL formula to calculate the sum of filtered transaction values. This gives you a fast and flexible reporting tool. And use Freeze Panes feature to keep subtotal(s) and table headers always visible.

And of-course you can design as much of various report sheets as you want, where you can select a date period (year, quarter, month of year, week, day) and/or transaction type, and get data from Transactions table - formatted as you want.

When you feel, that you want to remove some older data from transaction list, you have to replace the value for Balance0 on Setup sheet with latest value for CurrBalance of records you plan to delete, and after that delete all earlier records.