Data updated bidirectionally???

Options

Please Help!

We utilize an intake form/sheet that, upon meeting specific criteria, is automatically copied onto one of three designated "Master" sheets. All subsequent actions and tasks are performed within these Master sheets, Is it possible to then have the Mastersheet updates are reflected back to the original intake sheet?

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/01/24 Answer ✓
    Options

    Hi @CLT

    One way to have the Master sheet updates reflected in the original intake sheet is to add similar columns and use a cross-sheet reference formula to get the updated data from the master.

    In the demo dashboard below, the intake sheet, Issue Intake Sheet, has new columns, _Summary, _Description, and _Status, similar to the original columns, Summary, Description, and Status.

    In this example, workflow automation copies newly added rows to a specific master sheet, Issue: High Priority. (The criteria is Priority. We should have a Medium and Low-priority sheet, but I created only the high-priority sheet for the demo purpose.)

    Using the Issue ID as a key, the intake sheet gets the updated data from the master sheet. In the image below, Issue ID 4321 has a new Status value, "In Progress". The intake sheet gets the updated value with the following formula;

    =IF(Priority@row = "High", JOIN(COLLECT({Issue: High Priority Range Status}, {Issue: High Priority Range Issue ID}, [Issue ID]@row)))

    For _Description, _Status, and Actions Taken, you must change the {Issue: High Priority Range Status} range to an appropriate range like {Issue: High Priority Range Description}.


Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/01/24 Answer ✓
    Options

    Hi @CLT

    One way to have the Master sheet updates reflected in the original intake sheet is to add similar columns and use a cross-sheet reference formula to get the updated data from the master.

    In the demo dashboard below, the intake sheet, Issue Intake Sheet, has new columns, _Summary, _Description, and _Status, similar to the original columns, Summary, Description, and Status.

    In this example, workflow automation copies newly added rows to a specific master sheet, Issue: High Priority. (The criteria is Priority. We should have a Medium and Low-priority sheet, but I created only the high-priority sheet for the demo purpose.)

    Using the Issue ID as a key, the intake sheet gets the updated data from the master sheet. In the image below, Issue ID 4321 has a new Status value, "In Progress". The intake sheet gets the updated value with the following formula;

    =IF(Priority@row = "High", JOIN(COLLECT({Issue: High Priority Range Status}, {Issue: High Priority Range Issue ID}, [Issue ID]@row)))

    For _Description, _Status, and Actions Taken, you must change the {Issue: High Priority Range Status} range to an appropriate range like {Issue: High Priority Range Description}.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!