Updating Sheets Dynamically off Master Sheet

Mike Rini

Hi!

I am trying to create a solution for one of my clients and am hitting a mental roadblock. What I'm trying to do is simply update multiple sheets based on adding a row to the Master Sheet. I don't want to use the Copy Rows function for a few reasons, and this would be something I could solve with a Report, but I can't use a report because it doesn't allow for formulas and other things needed.

Essentially, in the Master Sheet, I will have a list of dealers that will be changed month to month, and I need the Child Sheets to update their dealers based on that update. The challenge is that there is no finite list of dealers. It can grow/shrink, so I can't simply have one master list of Dealers.

I've included a visual of what I'm attempting for an output for additional context, but the struggle I'm having is that if the master is constantly in flux, how can the Child Sheets reference it? I'm looking for some way to create a Primary Key / Unique ID, and the only thing I can think of is creating a Reference Table in which a Unique ID would exist/be created for every dealer, that the Child Sheets could reference.

I think that'll work, but I was looking to see if there were any other thoughts out there from the community.

Andrée Starå

Hi @Mike Rini

I hope you're well and safe!

Not sure I follow!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mike Rini

Thanks for the response @Andrée Starå . It's a weird one to explain. Basically, in a Master Sheet, all the rows will get completely wiped out at some point, and then re-populated. Each Child Sheet is dependent on knowing what Unique IDs are in that Master Sheet in order to pull back other data (name, address, etc.), and since there's no constant reference point in the Master Sheet (i.e. - one month it can be Dealers 1-10, next month it can be Dealers 1,3,12 and 25) it's making it a challenge.

I think DataMesh may have helped solve it though. I'm just using it with a helper table to try and sync them up.