Divisional Project Information/Tracking Solution

Courtney

Hello,

Each of our 9 departments has its own sheet that tracks project information, such as Project Name, Start Date, End Date. I am trying to:

1) When a department sheet is updated/adds a new project, the new row is moved to a separate sheet called Divisional Intake.

2) Each manager can view the Divisional Intake Sheet, and check a box if the new row/project will impact their area.

3) If the box is checked, the specific row will be copied back to the manager's department project sheet.

What is the best way to accomplish this? We want everyone to review all projects that are submitted in our division and be able to copy the project information back to their individual portfolio if it applies to them.

I've tried INDEX/MATCH, INDEX/COLLECT, helper sheets, Copy/Move automation (it copied all cells and that will not work), reports, and cell linking. I've also used Approvals, which sort of worked but is not the solution for us.

What is a better way to go about collecting this information? I was looking into WorkApps or trying Power Automate as a solution, but I'm a novice with each.

Any advice is greatly appreciated. Thank you!

Mike TV

@Courtney

What about using Update Requests automations instead? That way the managers don't have to remember to visit the sheet(s) daily. It will just be an update request in their email inbox that they get to at their convenience. You also wouldn't need another sheet to manage. With the Update Request, you can select which cells get included in their Update Request page they'll get.

Mike TV

@Courtney

What about using Update Requests automations instead? That way the managers don't have to remember to visit the sheet(s) daily. It will just be an update request in their email inbox that they get to at their convenience. You also wouldn't need another sheet to manage. With the Update Request, you can select which cells get included in their Update Request page they'll get.

Courtney

@Mike TV Thank you for responding! I was able to get it to work perfectly using that method, but our team is concerned about the number of emails they may receive as a result. They are probably going to stick with the additional sheet/logging in.

However, I hadn't used this type of automation before though and I'm glad I could learn something new!

Mike TV

@Courtney

You'd have to test it out but I believe what happens (with Gmail at least) is you get 1 email notification in your inbox and it has all 20 or more Request for Updates all stacked in the 1 email. At least that's what happens for me when they're notifications of the same type from the same sheet. They're like replies to a conversation I think.

They nifty thing though is the way they're formatted when you open them. It's much easier to read vertically and just the important relative cells that are pertinent to the review than viewing as a row on a sheet.

Courtney

Thanks Mike!