Google Sheets: Link/Sync Two Cells Dynamically (based on a third column’s data)


I want to preface this by saying this is NOT a duplicate of Sync two cells value. It’s similar, and uses the OnEdit() Function of Google Sheets, but the solution presented in that thread only works statically. This is an extension of that issue, not the same issue.

Let’s act like I work for Twitch for a second. My company uses a GSheet to keep track of all of our Monitored Streamers for the day, which includes regular streamers, partnered streams, and third-party pulls from other sources which require manual interaction. Each row includes the date, time, the name of the streamer, the stream ID, and the status – On Air, Delayed, Done.

Now, we keep the “Pulls” group on a separate subsheet, since those require actual effort on our part to go on air on time, as opposed to the normal streams which go off automatically. The “Pulls” subsheet is similar to the Monitored Streamers subsheet in that it has the date, time, name, and status of each Pull stream, but it also includes the encoder information on the same row, hence the need for a separate sheet.

Now, the way we have this done currently is that the status can only be changed from the main “Monitored Streamers” sheet, but the status is reflected in the appropriate box in “Pulls”. (Done with this function: =IF(E9="","",IF(COUNTIF('Monitored Streamers'!E:E,E9)=0,"Not Found",VLOOKUP(E9,'Monitored Streamers'!E:G,3,FALSE))), which from what I understand finds the stream ID in column E then grabs and prints the status from col G, or prints “Not Found” if it can’t find the stream ID on the MS sheet.)

This is kind of awkward, since it means that when I – someone who only works with pull broadcasts – want to mark a stream as “on air”, I have to copy the stream ID from the “Pulls” sheet, go to the “Monitored Streamers” sheet, find the stream in question, mark it there, and then go all the way back to the “Pulls” sheet and repeat the process. It would be much easier if I were able to just update the status on the “Pulls” sheet and have it affect the “Monitored Streamers” sheet automatically, without disrupting the ability to change the status of the “Monitored Streamers” sheet directly.

Now, I read through Sync two cells value, but as I stated before, the answer for that thread only works on static cell placement and hardcoded sheets. Our rows move constantly, since we’re always adding and removing streams from the sheet, so this solution won’t work.

Could someone please either write a script or teach me how to write a script that links the cells in column X on subsheet 1 and subsheet 2, iff the data in column Y on S1 matches column Z on S2?

EDIT:

Actually, now that I’m reading the linked topic again, I’m realizing that what I’m really asking for is a version of that script that has abstractions of the cell ranges as opposed to hardcoding the whole thing, preferably through function parameters so we can link multiple different cells across both sheets. (Sometimes we have to change the stream ID or date and whatnot, and it would be awesome if all of those synchronized across both sheets while still allowing both to be edited.)

Thing is, I don’t know the first thing about GApps scripting, so I don’t know how to do that myself.