Automation - can you change a cell value on another sheet?

Didi Aboyeji

Question: Please can you change a cell value on another sheet using Automation? Or perhaps a formula?

Reason: I want the project intake sheet to get updated automatically.

Details:

I am using the Project Management template on Smartsheet. In the Intake sheet (shown below) the BLUE columns are what each individual will need to create a project. Saving the entry generates a unique identifier – the project ID. This is good.

The Project Metadata sheet uses this project ID to populate cells. For example it uses the formula below:

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Name]$1, {Portfolio Summary Header Row}, 0))

Is it possible to create an Automation on the metadata sheet so that when the row is modified values in a specific cell can be copied over into a cell in another sheet? So, I modify the GarageRenovation project and my project status is sent over to the Project Intake sheet to overwrite whatever value was there. Effectively keeping the Project Intake sheet up to date automatically.

Alternatively, can I get the intake sheet to use whatever value exists in Project ID to search the entire workspace for a Project Metadata sheet with the same ID? Once found, the project intake sheet can reflect whatever date, project status, dashboard link, etc. that exists on the Project metadata sheet.

So instead of a manual link in from the metadata sheet like below:

It will be a formula like this:

=INDEX({All metadata sheets in this workspace}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Status]$1, { All metadata sheets in this workspace Header Row}, 0))

Automating this will reduce the chance for error and aid in adoption of Smartsheet by our team. Having to ask project managers to manually link cells from the project metadata every time a new project is created will deter adoption.

Thank you.

Andrée Starå

Hi @Didi Aboyeji

I hope you're well and safe!

Have you explored using the Premium App, Control Center?

It will automate almost everything.

More info: 

https://www.smartsheet.com/marketplace/premium-apps/control-center

Is that an option?

I hope that helps!

Be safe and have a fantastic 4th of July weekend!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Didi Aboyeji

Thanks @Andrée Starå . This is certainly what I need.

Andrée Starå

@Didi Aboyeji

Excellent!

You're more than welcome!

Let me know if I can assist in any way. I'd be happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Lin Sims

@Andrée Starå, is it possible to do this without getting the premium app? I don't think my company is willing to buy this just for me.

In my case, I'm tracking documents from initial request to release. I have a form set up to ask if the document is new or an update. If it's a new document, I have to create a part number for it. I don't want to do this on the same sheet that is tracking requests because that will create new numbers for all documents, whether new ones or updated, and updated documents don't get all-new numbers.

I need to take information from several cells on the sheet tracking document requests and copy them to another sheet that generates the number for the brand-new document, including the value in the primary column that numbers the requests as they come in (RQID). I thought to use VLOOKUP, but that requires a match to lookup against. I can't use the RQID for this since every new request gets an RQID, including the update requests for existing documents. I can't use the document name, either, since it's a new one that hasn't been named yet.

Basically, if the value in the column for New Document is Yes, I want to copy information from 4 or 5 non-contiguous columns to a separate sheet which will generate the new document number.

Andrée Starå

Hi @Lin Sims

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Would that work/help?

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

Lin Sims

@Andrée Starå, thank you for asking, I am both well and safe and hope you are the same.

You answer might work, but I don't yet have the background knowledge to say 'yes' yet.

Do you have a link to the solution you presented with the 2 intermediary sheets?

I've gotten fairly good at using VLOOKUP. I need to learn INDEX/MATCH, I guess.

I just tried to use Data Mesh, but it requires unique matches; since I only want it to happen when this is a request for a brand-new document, that's going to be tricky since that's the deciding factor.

Thanks.

MelodyD

I have had to use 3 helper sheets to grab a new project intake "Project ID" it was very difficult and can only capture the last Project ID giving enough time for the copy and paste feature to work.

However, the idea of going back the other way once the data is filled to create a master Project Meta Data sheet is complex because the "Project Dashboard" only changes to the project as a new link address once it is copied into the project workspace. I may have found a solution through a checkbox formula that can automatically trigger a workflow (thanks to @Paul Newcome .

Here is the idea and if you come up with a solution faster than me please let me know. - So when you copy the template and artifacts over to your project workspace the start date and end date as well as the dashboard link are only attached to the dashboard within the folder - hence the point, but what if you put index(match) for the project ID from the intake sheet to the dashboard which will be automatically populated by the metrics sheet and add another indicator that can trigger your workflow through the checkbox formula on the now connected metrics sheet to then trigger it on the metrics sheet to trigger after the connection is made (before copying the template) add a column, make it a checkbox that says - when Project ID matches the dashboard and when (in my case the project manager) = the dashboard - check the box. The action should copy to your master metric sheet once the dashboard link is setup or a project ID is added to the metric sheet. But you want to delay the trigger until after you copy and paste the template. The issue is that the location change is what updates the dashboard link - so I'm still working on that @Paul Newcome is brilliant when it comes to the advanced functionality - if he says it can't be done, it probably can't be done.

I wish I could just use Smartsheet API and code this because sometimes it feels like figuring out these work-arounds can take the same time.