Google Sheets – How to Organize Data

google sheets

I have separate sheets based on month's of the year.

sheets

Each time I create a new month I copy the template sheet and fill in the data.
Therefore each sheet has the same layout it's only the data that changes.

I'm getting to the point where I would interested in the data over two months combined, right now I would have to go into sheet 1 (Feb-2015) and copy the field say B2 (say it's 5) then go into sheet 2 (March-2015) and copy B2 (it is 7) then add them together, 5 + 7 = 12.

There must be a better way of doing this? Can I combine all of the sheets then create some type of selector for the month? I'm not sure what to do and also not sure what 'spreadsheet keywords' I need to Google to find what I want?

Best Answer

Put all the data for all the months into ONE sheet. Use a column called Month to keep track of what month each row is for.

When you want to see one month's data only, click in the data area, then choose Data > Filter from the menus. This puts a small drop-down arrow in the header cells, by clicking on this you can control what filters are applied.

There is an example of this approach here: https://docs.google.com/spreadsheets/d/1ro-wEdk-Gvw0v-UltYlbjf4EMLq5sPeJYhM1zKMblt4/edit?usp=sharing

Short answer

Use arrays referencing the column headers and the all the rows from the desired columns and filter them:

=QUERY({A!A1:B1;A!A2:B;B!A2:B},"Select * Where Col1<>''",1)

Explanation

Google Sheets allow to specify arrays by enclosing the values between braces { , }. If your spreadsheet use point . as the decimal separator, use comma , to separate columns and semicolon ; to separate rows, other way use a semicolon to separate columns and a backslash \ as row separator.

In the proposed formula the array has three sets of values

A!A1:B1 is used to get the column headers
A!A2:B, B!A2:B are used to get all the cells in the columns A and B of the worksheets A & B from the second row to the last row.
The QUERY() function is used to filter the values to avoid the inclusion of the blank cells in the final result.

References

Google Sheets – Copy Data to Another Sheet Based on Month of Date

You don't need a script: the Filter command (which is different from filters and filter views) does this easily.

Suppose your master sheet is named Master and the closing date is in column B. Then in Sheet "Jan" you would enter

=filter(Master!A:Z, month(Master!B:B) = 1)

Here A:Z are the columns of Master sheet (of course there may be more, you can use A:ZZ, etc). The condition that month of the date in column B is equal to 1 means it's January.

In the sheet named "Feb" you would put

=filter(Master!A:Z, month(Master!B:B) = 2)

and so on. The edits to Master sheet will be immediately reflected in the month sheets.

Best Answer

Related Solutions

Google Sheets – Combine Two Worksheets and Preserve Updates

Short answer

Explanation

References

Google Sheets – Copy Data to Another Sheet Based on Month of Date

Related Topic