Pull data from multiple sheets in Numbers

Typically if you want to create "reports" from a set of data, all the data needs to be in the one place. Right now yours is separated into 12 different tables. The first thing would be to get it all copied into one table, using formulas of course. Once it is there, see if the Categories feature gives you the reports you need.

Below shows one way to get it all into one table that allows sorting and categorizing. I based this example on the Personal Budget template. The template uses two sheets (Budget and Transactions) so the first thing I did was move it all to one sheet. Then I made duplicates of that sheet for the first few months of the year and renamed the sheets to be the month names. One more duplicate is the 2021 sheet that collects all the data from the other sheets. This is a real rough mock up and my focus was on the Transactions table. I didn't bother to change the data much so every month has the same transactions.

The numbers in the Month column (column E) are numbers, not formulas.

The numbers in the Row Index column (Column F) are numbers, not formulas.

Each of the monthly Transaction tables in this template had 27 rows, of which we want the data from rows 2 though 27. The screenshot shows all of January and the first few rows of February. You'll do the rest of the months the same way. If your tables have one header row and 200 rows total (with no footer row(s)), you'll have the numbers from 2 through 200 for each month. You'll want them to have more rows than you think you will ever need.

Formula in the Address column (column G) =MONTHNAME(E)&"::Transactions::A:D"

The Transactions tables have columns A:D so this will create a reference (as text) to the entire table.

A2 =INDEX(INDIRECT($G),$F,COLUMN())

Fill across to column D and then all of those columns down to the end of the table.

You would hide columns E-G when it is all set up but I left them visible.

Here is a view of it categorized with only one category expanded and using Subtotal to sum them up.

If Categories doesn't float your boat, there are other ways to view the data and create "reports".

I realize your document is probably much different than this one I created but maybe this example can get you started.

Here's an expanded 'Personal Budget' template (Dropbox download).

You can easily adapt it by changing the Categories in column A of the table on the Budget sheet and matching those categories exactly in the Pop-up Menus in column E of the Transactions table on the second sheet. The easiest way to change the Pop-Up Menus in the cells is to select E2 and go to the Data Format dropdown in the Cell tab in pane on the right and edit the menu.

Then select that cell, command-c to copy, select the cells below, and command-v to paste.

Since the Transactions table will contain all the transactions for that year, to get your report all you you need to do is filter on the Category column for "home expense", etc. Click by the column letter and. choose from the contextual menu.

SG