Create budget spreadsheet to track highs and lows of my account balance over the month

[socrates777usa]

Hello all. I am a novice with excel. I am creating a budget spreadsheet for my personal income and debts. To track highs and lows of my account balance over the month. I am doing this over a 12-month period with each tab showing a single month. From this I am able to make prediction and see each month’s actual purchases and incomes. I can also pull from each month and get yearly reports/charts of highs and lows.

To set up each page, I create a paper calendar layout for each month. Each square has from top to bottom, starting balance for the day, incomes for the day, total of incomes for the day, debts for the day, total of debt for the day and an ending balance. Predominately using autosum to get the total amounts. I have other info that I am pulling, like weekly income/debts, daily balances charted on a chart, etc.

After I have created each day and given the correct day and date, I have to manually auto sum the daily balances to a 1-31 (each day of the month) list that I can reference for the charts. This is the piece that I am struggling with.

After I have created the first month, I copy and paste it to each subsequent month, adjust the days/dates and join the previous month's last day ending balance to current month's starting balance. I am having to re-autosum the chart references manually. This is needed because each daily total is in a different cell from month to month.

I was thinking that maybe excel has a way of marking the cells so that I can mark the daily totals with, I don't know, 1stday or 123 and the references for the chart would go the whatever cell had that mark and autosum that cell to the list.
I don’t know if what I am asking is possible, but I have a feeling that I will be nudged toward scripting. I don’t mind coding; I have done some pearl, html and a very little PHP. I know that I will need to learn scripting, but I kind of want to get this budget up quickly. I can come back and learn scripting afterwards.

Thank you for any suggestions or guidance.

[BadlySpelledBuoy]

Hi and welcome

If I've understood you correctly then what you're asking can be done without the use of VBA (scripting), in several different ways. You could use an named range on the total cell for each day/page. You could add a total cell to each page in a specific position then refer to that cell each time because it will never move.
However, just because you can, doesn't mean you should.

What you're doing with this data layout is making life difficult for yourself. It's a common approach to projects like this for new spreadsheet users.

A better approach would be to have all the data, incoming and outgoing, for all days in one long list. You can easily perform your calculations from that list using a variety of easy to use formulas and can even use it to populate your calendar view approach if you're completely sold on having that.

Follow the instructions in the yellow banner at the top of the page and attach a sample workbook (not pictures of one) using some dummy data and show us what your expected output looks like. Manually generate the numbers if necessary, but explain where totals come from etc. We can help better when we can see the issue in context.

BSB

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

[JohnTopley]

Attached is a format used over many years: data for Credit/Debit cards is entered on the day of purchase (normally!!) and all totals are updated so you have a very good check on your balance at any point in the month which will also reflect any future regular monthly payments due.

I download a monthly file from my bank and reconcile this with my personal file (small VBA routine). "mismatches" occur when you have variable monthly payments (such as mobile/cell) phone because of variable usage but this is normally a very small difference.

The format is of the form proposed in the reply from BSB in post #2 with a sheet per month.