A couple of suggestions and a possible solution. First - as Toppers has
said, you can either have a formula or a value in a cell. If your current
total cell has something like
=182
in it, then you either change that to = 229 or to = 182+47
or if it has cell references like = C3 + C9 + C10 and your 47 is in C15 you
have to modify it to =C3+C9+C10+C15
That becomes tiresome and certainly doesn't make full use of the
capabilities of Excel.
Suggestion #1: put your totals for categories at the TOP of your worksheet -
they'll always be in ready view and modifying formulas to them will be easier
in the long run.
Suggestion #2: you probably already have this, but if not, you should ... in
the individual budget entries down the sheet, you should have a column for
your categories and you should be consistent in the entries in it. Consider
creating a list of valid categories and then using Data Validation in the
cells in that column so you can pick from the list, guaranteeing that the
entries are consistent.
Now, if you follow suggestion #2 you can set up some formulas that will need
very little maintenance in your totals section, whether at the top or bottom
of your sheet.
Lets say up in the top you have places to show the total expenses in the
categories: you have a cell that has the name of the category and next to it
you have a cell where you want to see the total right next to it (lets say
category name 'Groceries' is in A1, and you want the total for that category
in B1 next to it).
In your data entries section down further on the worksheet, the categories
are in column C, just to keep column letters separate and explanations
clearer. The amount of an expense in one of those 'daily' entries is in
column D. Those entries start at row 10 and go on down the sheet from there.
In cell B1 you could put a formula like this:
=SUMPRODUCT(--(C10:C100=A1),D10:100)
That will give you a total amount of all entries in the daily entries for
category Groceries, and it will update automatically as you add more entries
to the sheet.
You can set the same formula up for other categories: the formula would
always be the same except for the A1 reference - that will change to the cell
with the name of the category in it in the totals section of the worksheet.
The only thing you have to watch out for is making entries beyond the end of
the range for C and D in the formula - you'll need to check on that from time
to time and make it a larger number, such as C10:C150 and D10
150. You can
initially include a good sized number of additional, empty rows without harm.
Making it really large can affect performance of the worksheet. Although
you could set them to C10:C65535 and D10
65535 and never have to worry about
changing them again (65536 is max row number in Excel 2003). But as I said,
there'll be a performance hit for having to look at all those rows even
though most will remain empty.
