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

Use case.

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

  1. When a project is requested it is automatically added to the "Intake" sheet through the form. Status field is New.
  2. When we agree that the team will work on it - we add the Unique identifier to the "Prioritization" sheet and the Resource sheet. Status is 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 a Status field here. If the work is accepted we change the Status to "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 the Status field 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.
  3. 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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @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, andree@getdone.se)

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

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

  • Rebecca Larkin-Martinez
    edited 03/18/20

    @Andrée Starå ,

    Our process is,

    1. via a form, a person requests a project. It generates a line in the project Intake and scoping sheet.
    2. <intake and scoping sheet pic here>
    3. 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.
    4. 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.
    5. <Prioritization pic here>
    6. 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.
    7. <resource pic here>
    8. 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å
    Andrée Starå ✭✭✭✭✭✭

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @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å
    Andrée Starå ✭✭✭✭✭✭

    @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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • katfooddfw
    katfooddfw ✭✭✭✭

    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å
    Andrée Starå ✭✭✭✭✭✭

    Hi @katfooddfw

    Can you describe your specific 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, andree@getdone.se)

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • katfooddfw
    katfooddfw ✭✭✭✭

    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
    • We have manipulated the conditional formatting on 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