I want sync a field across multiple sheet, being able to edit it from any of those sheets

Rebecca Larkin-Martinez · 03/13/20

Use case.

4 sheets Intake Sheet, Prioritization sheet, Resource sheet & Overall Status Sheet

When a project is requested it is automatically added to the "Intake" sheet through the form.Status fieldis New.
When we agree that the team will work on it - we add the Unique identifier to the "Prioritization" sheet and the Resource sheet.Statusis changed to "Scoping". We want the scoping status to be reflected on the prioritization sheet AND the Intake sheet AND we want to be able to update the status from either sheet.
1. We prioritize this new work within the current in flight work there is aStatusfield here. If the work is accepted we change theStatusto "Staffing". We want the scoping status to be reflected on the prioritization sheet AND the Intake sheet AND we want to be able to update the status from either sheet.
2. We add the unique identifier to our Resource sheet and look at who has availability. When it is staffed Using theStatusfield on the Resource sheet we change it to Initiated. We want the Resource status to be reflected on the prioritization sheet AND the Intake sheet AND we want to be able to update the status from any of the sheets.
We add the unique identifier to the Overall Status sheet - this captures data from inflight projects the status of Intiatied is reflected here in the status column. As the project progresses we want the Overall Status sheet, Prioritiazion status field, Resource status field and intake status field to be in sync.

Workflows are not sophisticated enough to update specific fields across sheets

Formulas seem to work but they are unidirectional

Paul Newcome · 03/13/20

The only way this could possibly work would be to use a sheet and multiple reports for the other portions.

Andrée Starå · 03/14/20

@Rebecca Larkin-Martinez

Hi Rebecca,

It might be possible to develop a solution.

How many projects could there be?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome · 03/16/20

@Andrée StaråI am pretty curious about your solution for multi-directional cell linking that can be updated from either end.

Rebecca Larkin-Martinez · 03/18/20

Andre, I will work on this tonight and see if I can get you the info you need.

Rebecca Larkin-Martinez · 03/18/20

Resource Sheet.png

Prioritization Sheet.png

Intake and scoping sheet.png

@Andrée Starå,

Our process is,

via a form, a person requests a project. It generates a line in the project Intake and scoping sheet.
From there we assign an Intake Owner, and through an update request they complete the scoping. Other fields for the Budget will want to be copied and pasted and the original documentation intake updated if there are changes later in the project life that we would capture in the Health sheet not yet determined to be a sheet report etc.
The Intake Owner then copies the ID, Effort Name, Intake Owner, and State from the Intake sheet to the Prioritization Sheet, where they assign scores and prioritize this work against the other requests.
<这里优先级图片>
The Leadership team Copies the ID Effort Name and State from the Prioritization sheet to the Resource Sheet where they look to see who could execute the project based on potential begin date, estimated Size and duration. Using the Sheet Resource option the new project is assigned.
At some point the Health of each project will want to be captured and again there will be redundant work of copying and pasting.

Andrée Starå · 03/31/20

@Rebecca Larkin-MartinezWill it be multiple sheets by project (except the Intake sheet) or one combined?

Rebecca Larkin-Martinez · 03/31/20

@Andrée StaråIt will be multiple sheets - the intake, prioritization and resource sheets are multiple projects - the only one that will be per project will be the actual Project Schedule/Health sheet. That one will have additional information for project specific metrics. Thank you. On the 3 sheets in the process they projects are connected by the unique ID.

Andrée Starå · 05/16/20

@Rebecca Larkin-Martinez

Excuse the late response.

I had a solution in mind, but thinking about it again, we must have some priority to make it work, and we will need multiple so-called helper columns.

What do you think?

katfooddfw · 07/24/20

Hi@Andrée Starå! I'm interested in this resolution, as well. Were you able to figure it out?

I have a similar need. At this point, however, it is just for two sheets vs. four.

Thank you, as always!!

-Kathrine

Andrée Starå · 07/29/20

Hi@katfooddfw

Can you describe your specific process in more detailand maybe share the sheet(s)/copies of the sheet(s) or somescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

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 help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

katfooddfw · 07/30/20

Hi@Andrée Starå

So, this is complicated. We initially started with trying to have separate sheets with separate forms that would be able to populate rows but have since changed our approach.

Now we have a "main" sheet. It is populated by a form from account executives. We are trying to create multiple sheets that can then merge into a report that can be viewed as a calendar. We've got our sheet/report system worked out, but can't figure out how to get the other sheets to update dynamically. Right now we are working with cell linking, but it only goes one way. If someone make a change to the report it only updates to the corresponding sheet.

Main sheet is populated by a form
3 Independent sheets (Air Date, Record Date, Zoom Test Date) are pulling data from the main sheet through cell linking
1 Report is created that pulls in the content from the 3 independent sheets
我们有操纵的条件格式each sheet to color code appropriately on the report
UP TO THIS POINT WE'VE FOUND A WORKAROUND FOR OUR NEEDS
If a change is made on the report, where you can see all of the info at one time on a calendar, it will update the corresponding sheet. This is important because we only can have a certain number of things happening on a certain day. The calendar makes it easy to see.
It does not go back to the original main sheet and update
ALSO, the other sheets do not update as additional lines are added to the main sheet from form submissions
Therefore it isn't helping with our workflow

The idea is that we would have the ability for AE's to easily see what date/times are available. So, how many units are booked for a specific day. PRIOR to filling out the form. The form acts as the initiation of the project but we want the account executives to know what is available.

I hope that makes sense.

I'm going to share the sheets with you.

Thank you!!!

-Kathrine

I want sync a field across multiple sheet, being able to edit it from any of those sheets

Answers

Categories