Change Cell Workflow across sheets

Terry P

I know that some of this can be achieved with linking and some manipulative formula but I think it would be great (at least for me ;-)) if there was the ability to change the content of a cell on one sheet from an automation on another.

My scenario:

I use a date/time based automation to copy pertinent cells from one of my sheets (that contains some open cells and some cells linked from another of my sheets - the Master Sheet) into a sheet/process owned by another team in our organization as a 'new row'. Part of the row is a 'status' cell (a drop down) that I push into the other sheet with the value 'open'.

That team 'does their thing' and eventually that cell gets changed to 'Approved' (or denied or a couple of other statuses) and it's this change I would like to get all the way back to the Master Sheet. There is a unique ID that could be used as a filter/identifier to make sure its the right row in the Master that is getting the change.

Maybe I could create further 'shadow sheets' and get cute with linking etc, but a nice simple "change a cell on another sheet" automation would be perfect!

What do folk think?

Meredith Rhodes

I like this idea too.

I have a master sheet that requires an 'Initial Submission Date' for a particular task for a particular project. I have another sheet that has a bunch of submission dates for a number of tasks for that particular project. I would love to automate entering that Initial Submission Date on the Master sheet when that specific submission date is entered for that project on the second sheet. Right now - I'm having the staff duplicate that entry.

Paul Newcome

Have you looked into an INDEX/MATCH type of formula?

Terry P

Hi Paul - I haven't specifically looked at index/match - the request is for a simple automation that doesn't require formula to achieve, not least because if I use a formula I then can't trigger any other automations from the change, I have to go to a time based/batch approach.

Also, one of the beautiful things about Smartsheets is how much you can achieve without the need for deep formula understanding ;-)

Have a great Day

Terry

Genevieve P.

Hi @Terry P

If I'm understanding you correctly, your scenario and request sounds to be the same as this this other Idea:

Copy Specific Columns From One Sheet to Another, Linked

If that's correct, please add your voice to that thread to keep all ideas/scenarios in one place for the Product team and I'll move this Idea to being a discussion. 🙂

You may also want to look into DataMesh which can add data from one sheet to another, based on a unique identifier, and create a Cell Link with that data. The only thing here is that it cell-links from the Source to the Destination, meaning you wouldn't be able to change that information in the Destination sheet. Here's more information on DataMesh if you're interested.

Cheers,

Genevieve

Terry P

HI @Genevieve P

I think they are symbiotic but not quite the same as I'm focusing here at the cell level, not columns.

In my scenario there are two completely different Sheets involved, one managing my process and one managing processes in another team - that team deals with multiple request types (one of which is mine) so not every row in their 'status' column applies to my work - hence the already identified need for a unique reference... solved as I use the reference auto generation field.

Copying the whole column wouldn't work and because a formula would be used I couldn't then trigger further workflow from the cell in question, other than by a date/time based automation.

Change a cell based on criteria already exists within a single sheet, the idea/ask here is to be able to do that across different sheets.

I hope this makes sense?

Cheers

Terry

Genevieve P.

Thanks for the clarification, @Terry P! I've updated the title of your Idea to help other members find it and identify the difference between the requests.

Cheers,

Genevieve

vdemattei

This would also be helpful for our organization. We track various different workflows back to an overarching workflow. It would be very helpful if an action in one of the sheets would update a field in the main sheet. Our specific use is tracking the application process for potential homeowners. Applicants are required to fill out different forms and complete different tasks that are each tracked in individual sheets. I have a main workflow sheet that would be updated from information in the other sheets as an applicant completes each of the forms.

I am working on using index/match to accomplish this, but I am not versed in these formulas, like the OP, and I have many uses for this functionality, so I agree it would be lovely to have it as an available function through Smartsheet instead of utilizing potentially complicated formulas to accomplish this.

CTops

Can I ask what you are currently using to achieve this with Index/match ? I am just starting out on my SmartSheet journey and need to change a specific cell in one SmartSheet when another team has 'done their thing' and produced the answer that populates a different sheet. I am working with thousands of rows across 10's of sheets and if there's a formula or any other solution to automate this it would be amazing and reduce a lot of time checking for errors!

I have been tasked with finding a solution as it could save ££££'s!!

Any help appreciated but I would like to add my voice to the request for specific cell updates.

Thank you

Terry P

Hi CTops

I'm in a similar situation, I haven't had time to get my head around index/match as yet - if I crack the code I'll post back... but a nice simple automation would still be great ;-)

Cheers

Terry

Paul Newcome

There are a number of ways to check another (source) sheet for a value and output something on the destination (working) sheet. One way is an INDEX/MATCH if you have a unique ID on every row that can be used to match the two rows together. This works best if there are no duplicates and if you are wanting to pull over the data that is in the source cell. If you have duplicates, you may need an INDEX/COLLECT combo.

If you are wanting to output something different than what is in the source cell (for example check a box on the working sheet if there is a specific date in the source sheet), I tend to lean more towards an IF/COUNTIFS.

@vdemattei, @CTops, and @Terry P If you are able to provide more details and screenshots (in a separate thread), we may be able to help come up with something. Feel free to "@mention" me in your post so I get notified and can possibly get to you a little faster.

Terry P

Hi All,

Thanks for all the feedback - I finally had a chance to investigate index/match, whilst powerful it doesn't solve my problem :-(

Why?

My issue is really 'creating new rows' without manually entering them or using a form. Index/Match would enable be to grab the data I want, but only from an existing dataset - you can't use it on a non-existing row without 'creating' that row as you enter the formula if you get my meaning.

What I really need, and something I think other folk could use too, is a way to 'auto-populate' a form on a sheet (access rights permitting) that would then spawn the new row and all the various automations on all the various sheets could execute BAU.

I believe DataMesh can get close to this or solve in full, but whilst part of a large organization we're a very small team and the purse strings are tight.

Anyway, thanks for all the efforts

Have a wonder-filled day!