We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Budget with Monthly Sheets and YTD Totals

asked 2021-01-30 18:41:30 +0200

ftv gravatar image

updated 2021-01-30 19:13:00 +0200

Opaque gravatar image

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

edit retag flag offensive close merge delete

Comments

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

ftv gravatar imageftv ( 2021-01-31 21:43:47 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2021-01-30 23:09:08 +0200

@ftv, Suggestion for a spreadsheet for launches and another for a summary.

image description

New Category, launch at a Resume.

image description

Test file

-----------------------------------------------------------------------------

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more
0

answered 2021-01-30 21:26:19 +0200

Lupp gravatar image

updated 2021-01-30 21:34:47 +0200

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.

C:\fakepath\anothetSimpleBudgetOrTransactionSheet.ods

edit flag offensive delete link more
0

answered 2021-01-30 19:26:33 +0200

Opaque gravatar image

updated 2021-01-30 19:34:14 +0200

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.

edit flag offensive delete link more

Comments

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.

ftv gravatar imageftv ( 2021-01-31 21:46:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-30 18:41:30 +0200

Seen: 36 times

Last updated: Jan 30