Master Table Updating Other Sheets/Tables

Question

Master Table Updating Other Sheets/Tables

Hello! I'm trying to find a way to have a master table with information that when updated with new rows of information updates other tables and/or sheets automatically with that newly added information. I am trying a pivot table but it doesn't pull the information over exactly, instead trying to summarize it and add mathematical values instead of just the text data.

I can do it in excel via data tables but would rather find a way to do this in numbers. Has anyone had success in something similar?

Screenshot of document/information style attached.

MacBook Pro 13″, macOS 14.2

Posted on Mar 24, 2024 2:08 PM

Reply

Answer 1

Badunit

Level 7

30,348 points

Mar 24, 2024 7:51 PM in response to Mama_Mia_Rose

If all you want is a copy of the master table, a table full of formulas such as those below can bring in the contents of the main table (which I am referring to as Table 1):

=Table 1::A

or

=IF(Table 1::A<>"",Table 1::A,"")

or

=Table 1::A&""

or

=IFERROR(Table 1::A&"","")

or other similar kinds of formulas, depending on the data.

Make the other tables a lot longer than the main table so they have room to grow. They will not add new rows automatically as Table 1 grows. You may want to add an extra column (which you can hide) with a formula in it that you can filter on to hide the extra rows.

Reply

Answer 2

Badunit

Level 7

30,348 points

Mar 24, 2024 4:35 PM in response to Mama_Mia_Rose

Neither "Get Data" or "Power Query" are available in Numbers. I thought they were for importing/linking and querying external data. Maybe the pro version of Office gives them more functionality than my "home" version does. I've never used any of it. So let's go about this a different way. You posted a table of data in your first post. Based on that table of data, can you post an example table that shows what you want one of the other tables to look like and what summarizing you want done in it? Also provide an explanation of the "query" that brings in that particular subset of data.

Reply

Answer 3

Mar 24, 2024 3:48 PM in response to Badunit

In Excel, under the 'Data' menu, there is a way to 'Get Data' that I was able to choose an existing table/range as a source of information for a new table and use the Power Query Editor if needed. When I have completed that initial data query setup, I am able to add rows and information on my master table and the linked tables using it as a data source will refresh with the updated information.

I was hoping there was something similar in numbers so I'm not having to update rows of information on multiple sheet tables. I am stubborn about using numbers for this so I'm able to access the spreadsheet data easily on my iPad,etc. or else I would simply continue on excel.

Thanks!

Reply

Answer 4

Mar 24, 2024 7:17 PM in response to Badunit

What I really need is simply the master table to update the other tables, so they would all essentially look as the master table does (the example I showed). I don't need it to calculate or do anything crazy or change in the new table, I just want it to pull in the updated row of data automatically and update accordingly.

I'm simply trying to avoid adding and typing new rows on every sheet. Seems like it should be something that is simple and numbers *should* have... but apparently not and I may need to update each page after all.

The only suggestions I saw previously used a pivot table, but I can't seem to adjust a pivot table to copy over data without attempting to count or sum some of the information and re-organize it all.

Reply

Answer 5

Badunit

Level 7

30,348 points

Mar 24, 2024 3:00 PM in response to Mama_Mia_Rose

How do you do this in Excel with data tables? I thought they were used for what-if analysis. Maybe by understanding what you are doing in Excel we can figure something out for you in Numbers.

Reply

Master Table Updating Other Sheets/Tables

Similar questions