Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Create a master table. When I edit that table, it edits all other tables

Hey guys. I’m trying to make a budget on numbers and I am not too familiar with it. I am looking to make a “master sheet” of sorts. I want to copy that sheet for each month, and be able to make changes to the master sheet’s format that will reflect on the other sheets. So, if I move a row on the master sheet, that row will move on all of the other sheets as well. Is this possible?

Posted on

Reply
Question marked as Helpful

Dec 17, 2017 2:31 AM in response to Tim101691 In response to Tim101691

It might be possible with a script, but in general spreadsheets don't "push" changes the way you describe. The tend to "pull" data from cells.


It's usually more efficient to keep like data together in one table rather than scattering it in multiple tables, and then extract summary "views" of that data as needed, either by using the built-in filtering capability, or by SUMIF, SUMIFS functions and the like.


If you're doing a budget, highly recommend you have a look at the Personal Budget template at File > New in your menu. It keeps the data in the 'Transactions' table on the second sheet and shows a summary of that data in the 'Summary by Category' table on the first sheet. It can easily be adapted to your specific needs.


If you need to shows multiple months you might have a look at an "expanded" version saved here (Dropbox download).


SG

Question marked as Helpful

Dec 17, 2017 6:01 PM in response to Yellowbox In response to Yellowbox

HI Ian,


I think you can simplify this formula with OR:

original: IF(Filter?::A$1="See all or choose a Month","Show",IF(Filter?::A$1=B2,"Show","Hide"))

revised: IF(OR(Filter?::A$1="See all or choose a Month",Filter?::A$1=B2),"Show","Hide"))

Or, change the table name from 'Filter?' to 'Show' and the first item of the pop-up menu to 'All' and use this version:


rev. 2: IF(OR(Show::A$1="All",Show::A$1=B2),"Show","Hide"))


Rgards,

Barry

There’s more to the conversation

Read all replies
Question marked as Helpful

Dec 17, 2017 2:31 AM in response to Tim101691 In response to Tim101691

It might be possible with a script, but in general spreadsheets don't "push" changes the way you describe. The tend to "pull" data from cells.


It's usually more efficient to keep like data together in one table rather than scattering it in multiple tables, and then extract summary "views" of that data as needed, either by using the built-in filtering capability, or by SUMIF, SUMIFS functions and the like.


If you're doing a budget, highly recommend you have a look at the Personal Budget template at File > New in your menu. It keeps the data in the 'Transactions' table on the second sheet and shows a summary of that data in the 'Summary by Category' table on the first sheet. It can easily be adapted to your specific needs.


If you need to shows multiple months you might have a look at an "expanded" version saved here (Dropbox download).


SG

Dec 17, 2017 2:31 AM

Reply Helpful (1)

Dec 17, 2017 4:56 AM in response to Tim101691 In response to Tim101691

Hi Tim,

I can look at specific portions of the sheet?

Here is an idea using a filter on a master sheet (table)

Green is placed as Conditional Highlighting for "Show", just as an illustration. Not necessary because you can hide Columns B and C

User uploaded file

Formula in the new Column B of Transactions =IFERROR(MONTHNAME(MONTH(A2)),"")

User uploaded file

IFERROR places "" (NULL) in a B row that contains no date in A


The added table "Filter" has a Pop-Up Menu

User uploaded file

Formula in C of "Transactions" =IF(Filter?::A$1="See all or choose a Month","Show",IF(Filter?::A$1=B2,"Show","Hide"))

User uploaded file

So, if you choose a month to filter the Transactions table

User uploaded file

Now apply a filter to Transactions

User uploaded file

To see this

User uploaded file

To see the whole Transactions table again, go to the "Filter" table and choose See all or choose a month

User uploaded file

Remember, you can hide Columns B and C

Regards,

Ian.

Dec 17, 2017 4:56 AM

Reply Helpful
Question marked as Helpful

Dec 17, 2017 6:01 PM in response to Yellowbox In response to Yellowbox

HI Ian,


I think you can simplify this formula with OR:

original: IF(Filter?::A$1="See all or choose a Month","Show",IF(Filter?::A$1=B2,"Show","Hide"))

revised: IF(OR(Filter?::A$1="See all or choose a Month",Filter?::A$1=B2),"Show","Hide"))

Or, change the table name from 'Filter?' to 'Show' and the first item of the pop-up menu to 'All' and use this version:


rev. 2: IF(OR(Show::A$1="All",Show::A$1=B2),"Show","Hide"))


Rgards,

Barry

Dec 17, 2017 6:01 PM

Reply Helpful (1)

Dec 17, 2017 6:33 PM in response to Tim101691 In response to Tim101691

Tim101691 wrote:


When you say summary views, do you mean I can look at specific portions of the sheet?


What I mean by summary views is the ability to extract summary statistics with SUMIF and its cousins.


You can see an example of that in column C of the SUMMARY BY CATEGORY table on the first sheet of the template. It extracts useful information from the Transactions table on the second sheet.


Aside from summary views you can also easily see subsets of your data using the built-in filtering capability. For example, you can easily do something like this in the Transactions table.


User uploaded file


SG

Dec 17, 2017 6:33 PM

Reply Helpful
User profile for user: Tim101691

Question: Create a master table. When I edit that table, it edits all other tables