Can you have a sheet copy all changes to another sheet? (google sheets)

[dappolan]

Hello all,

I'm working right now to make a more dynamic workbook for my organization for tracking events. I want to make new sheets for different teams to track information specific to each team. I set up a new sheet and successfully set up columns to import data from the equivalent positions in the first sheet. The problem, though, is that when we insert a new row for a new event added in between existing events, the new row isn't populated in the secondary sheet. Is there a way to make a second sheet mirror all changes made to a first? 

Thank you in advance for your help. 

[#2]

@dappolan 

What you describe is certainly possible, probably in multiple ways. But I wonder if it is possible for you to share either the actual workbook OR, if that contains confidential/proprietary data, a mockup. So much depends on how your data are actually arranged, entered, retrieved.

From what you have described so far, it sounds as if you have a master sheet of some kind, and then multiple secondaries, one for each team. It might well make more sense to have that master sheet continue to be the repository of the active data--the place where you enter raw data Inputs--but have just a single Output sheet where (using FILTER or some other comparable function), the data for any given team is displayed "on demand"    

That would be easier to determine if we could see an actual or a mockup of the actual. Post your file on OneDrive or GoogleDrive and paste a link here that grants edit access.

[#3]

Hi John, thank you so much for your response--I started looking at filters, but I'll have to do some cleanup work on the sheet before I can implement them. When the document was originally created, cells were merged to make "cleaner" displays for date fields. I've eliminated the merged fields in the active portion of the sheet, but there are hidden rows that I'll have to reopen and clean. Once I take a look at that I'll make a duplicate of the workbook and wipe the identifying data to prepare it for upload. I greatly appreciate your time and your willingness to share your expertise with me as I begin my self-studies in Excel and organizational/data management.

[#4]

Hi John, I'm having a little trouble with the filter (It's not seeming to hide the data when I apply it), but I've cleaned up the duplicate for sharing, leaving in some example items to show the formatting. Here is the link: https://docs.google.com/spreadsheets/d/1V5-8yjg9fzIbL29gbq-4_eNlFcGi9QCXBQF1be9h6zU/edit?usp=sharing

*2:14PM EST: changed access to allow anyone with the link to open

[#5 · Accepted solution]

@dappolan 

Just looking at what you have. You'll need to do bit of cleaning up in order for the FILTER function to work. Among other things,

• you're mixing altogether different categories of data into the "Event" column. For example
  • Cell D184 contains the value June 2023
  • Cell D185 contains Thursday, June 1, 2023; Cell D187 contains Friday, June 2, 2023

Dates should be their own column, with the date repeated for any events that are on the same date. No need to have a separate column for month, because that can be handled by FILTER (whether the function FILTER or the icon on the Data Toolbar)

• Do away with empty rows. They make it easier for the human eye to read breaks in data, but they're not helpful in using Excel to its best advantage. 
• Learn how to create and use Excel Tables--they're a foundation on which a powerful database can be built. One of the rules--you'll see it in item #1 on this linked reference--is to eliminate blank rows. But read the rest of the page as well.

I see that you're using Conditional Formatting to differentiate rows based on the name in the "Planner" column -- meaning also, I'm guessing, a different "team."  You've done a good job with the Conditional Formatting per se (it can be tricky), but what all this means is that you're also mixing into one worksheet both input and output (or a part of each). In general, we're better served by keeping the Input clean (at the very least until all of the desired functionality has been created); and by keeping the Output separate. It's at the Output end of things that color can be added for emphasis, clarity, etc. 

And if the Input is a good, clean, well-designed Excel Table (which you'll have after cleaning up a few things as noted), then a separate Output tab will be readily created.

And here's a good YouTube video on the FILTER function, along with some related Dynamic Array functions. You will need a relatively new version of Excel (or subscribe to Microsoft 365) for this to work; it's well worth it.

By all means, feel free to come back with more questions or for help implementing the FILTER function; but I do recommend cleaning up your database first.

[#6]

John, thank you very much for all your help, and for your kind words on the conditional formatting. Yes, there's definitely work to be done. I'm working to try and transition as smoothly as possible from our team using Excel as a very rudimentary 'at-a-glance' planner to utilizing more advanced features and tracking and presenting data in a more powerful way while keeping things easy enough to manage for my less data-oriented coworkers. I'll have a lot of work ahead of me, but I've pinned the notification on your reply in my inbox so if I have questions once I've cleaned things up, I'll come back! Thank you so much again for your help.

[#7]

You're very welcome!