Automation and/or formula in one sheet to change cell in another sheet?

Tierra L.

I have ten sheets in our workspace, one is the master sheet that leadership uses to approve department requests, the others are department specific spreadsheets that are populated by a request form for each department to manage their requests.

Each department specific spreadsheet has a status field to track where their requests are within the approval process (ex: A dropdown with values like Submitted, Reviewed, Confirmed, etc).

Once the request is in "Confirmed" status, the request row is copied over to the master sheet for final leadership approval.  Once it is approved by leadership, we want to have an automation and/or formula driven by either the department name or the request ID to update the status cell on the department specific spreadsheets to "Approved" status rather than having to manually update the status.  

Is this possible? If so, how?

Also, is it possible to generate an email to the department heads and the original requestor based on the request ID or department when leadership approves, puts on hold, or denies a request?

Thanks in advance for any help you can provide!

-TL

Jeremy_D

Hi, you could use an Index Match formula and a helper column with a workflow.

First of all use an INDEX MATCH formula like this in a helper column

=index({Approval Status} , match([Request ID]@row, {Request ID on Approval Sheet} , 0)

Then create a workflow that says when this column is approved it updates the status. The workflow will look like this:

Then

You can then set up an automation by setting the trigger and who to alert by going to automations and creating a workflow and referencing fields. Something like the below:

Hope that answers the question!