Linking / Updating Sheets

Mike Rini

Afternoon All -

I have a slightly complex question I wanted to pose to the community to see if I could get some thoughts.

I have two sheets that I want to have "talk" to each other. One Sheet (Master Sheet), is where our team spends their day looking for requests to come through. In a second sheet (Change Requests Sheet), we allow people to submit changes to their original requests (e.g. - change an End Date, Budget, etc.).

In the Change Request sheet, their can be many different changes requested over time - budgets updated multiple times, end dates extended, etc.), so we have a 1-to-Many relationship between our Master Sheet and Change Request Sheet.

What I am trying to do is notify the Master Sheet when a Change Request has come in - automatically - so the team doesn't have to bounce between sheets.

My challenge is this - how can I only pull in the newest (or not completed) Change Requests to the Master Sheet? I can use a Join(Collect()) to pull in all changes, but I can't figure out a way to essentially have the Master Sheet notify the Change Request sheet that the change has been completed, and doesn't need to be shown anymore on the Master Sheet.

I feel like there could be an opportunity to use an Update Field or something, but am struggling with the workflow.

Any and all help would be greatly appreciated.

Thanks,

Mike

James Keuning

You can add a field to the change request sheet that indicates that the change is complete, and then another column that shows the name of the change, but only for changes that have not been completed. Combine those two into one field that shows the name for incomplete changes. Then you can point your COLLECT at that field.

Mike Rini

Amazing! That's perfect - can't believe I didn't think of that. I tested it out, and it works exactly how I want to show it in the Master.

Now, any thoughts on how I can inform the Change Request sheet that - that particular Change Request - has been completed? Knowing that there's likely a 1-to-Many relationship (Master to Change Request).