Budget with Monthly Sheets and YTD Totals

ftv · January 30, 2021, 5:41pm

Hi,

I’d like to “easily” create a summary sheet of my monthly expenditures in the following format:

Category   Jan   Feb   Mar   Apr   etc
Mortgage   1800
Oil              350
Electric
Groceries    180

etc

I have 12 sheets, one per month, in the following format:

Date   Mortgage   Oil   Electric   Groceries
1/1     1800
1/3                     350
1/3                                           120
1/6                                             60

etc

I used the Paste Special with Transpose to copy the categories from the summary sheet’s row to each of the month’s column headings, and I created my first totals entry (Jan Mortgage - =SUM(Jan.B:B) in the summary sheet. Is there a way to Copy/Paste Special or otherwise create the remaining Jan category totals, and then likewise copy those with the appropriate adjustments to the remaining monthly columns?

Thx,
Frank

[Edit - Opaque] Changed tables to pre-formatted text for readability

ftv · January 31, 2021, 8:43pm

I appreciate the suggestions and advice thus far, but I believe my original layout is easier to follow for my better half. I have discovered that once I have the formulas for January in my Totals sheet, I can simply cut/paste that column into the remaining monthly column totals, and then do a find/replace in each column to adjust for the month.

Now the question is if I can somehow automate the values for January. This is what they should look like for the first couple of cells in the summary sheet:

B2 → =SUM(Jan.B:B)
B3 → =SUM(Jan.C:C)
B4 → =SUM(Jan.D:D)
etc.

Can I enter "=SUM(Jan.B:B) for B2 in the summary sheet in a way where cut/paste into the cell below it (B3) will automatically adjust to “=SUM(Jan.C:C)” or its equivalent?

Thx,
Frank

anon73440385 · January 30, 2021, 6:26pm

Hello,

assuming your overview month headers start in B1 (i.e. B1 is Jan; C1 is Feb … and your monthly tables are exactly named like these header columns, you may use:

Mortgage: =SUM(INDIRECT(B$1 & ".B:B"))
Oil: =SUM(INDIRECT(B$1 & ".C:C"))
…

These formulas can be copied to cells in all other columns.

But please be aware: ".B:B", ".C:C" ,… etc. are text strings and won’t adapt if you change the design of you month-sheets (that’s why some people call using function INDIRECT() “evil”; my advice is a bit more pragmatic: Use it only, if you don’t expect a redesign of you monthly sheet in the near future and/or do not insert column but add additional data right to the last used column)

Hope that helps.

ftv · January 31, 2021, 8:46pm

Thx, but I tried these and I got an error so probably did something wrong. Please see my comment to my original question, maybe this approach applies but I need additional guidance.

Lupp · January 30, 2021, 8:26pm

The sheet-per-month+overview approach has so many disadvantages that I wouldn’t advise any longer in cases aiming at that design. That’s only paper-think.

Many experienced users may strictly advise you to shift to a database. But I I tend to still consider that in cases of rather small numbers of transactions / rows and of branches (columns) a spreadsheet will do i in a more familiar way, and may also meet the needs if a reliable backuping can be assured.

Never ever try to get an overview changing the assignment of categorizing properties from columns to rows or reversely. Nothing to expect but a mess!

The attached example may show you a possible way for a budget / transaction sheet with up to a few thousand rows of data and up to a dozen or so standard branches. The upper part is for the oveview, the lower part for the (by default adjacent) data rows. Do not enter any values into column H or right of it. With very rare exceptions everytning there is done automatically. If you overwrite one of the formulas with a constant, an alerting color will show based on ConditionalFormatting. The cells of the same columns in the upper part must be taboo.

Sheets containing the relevant functionality shall not be formatted for printing!
Spreadsheets simply are functionality! But if you urgently need prettyprints, create extra sheets for them and pull the values you need in by extremely simple formulas from the cells in the working sheet where they already were calculated.

anothetSimpleBudgetOrTransactionSheet.ods