Automating Cell Link

John Beattie

I've created a workflow to handle project requests that come in to our department. We have a master sheet where all new requests are entered (either manually or from a webform) then each of our 3 team members have an individual sheet. When the new project is assigned, the row is copied to the individual's sheet (to whom it was assigned).

Is there a way to automatically link the status and progress cell back to the master sheet so when a change is made to the individual sheet, the master sheet will change as well? I'd like to avoid having the individuals have to manually link cells every time they were assigned something.

I'd even be happy with a replace function where the whole row was copied back to the master and replaces the original row (doesn't look like that exists).

I do have approval automation set up for the status cell I'd just like to link cells as well to cover all bases.

Thanks!

Andrée Starå

Hi John,

Does the different requests have anything unique that identifies them?

We could maybe use that and cross-sheet formulas combined with VLOOKUP or INDEX/MATCH to link them together.

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @WORK BOLD

✅Did my post help 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!

John Beattie

Each project has a number, assigned sequentially in the master, that stays with the project as it is copied.

I'm going to have to look up what a VLOOKUP or INDEX/MATCH is before I can tell if it would help.

I'm a creative coming up with a technical process, so you're going to need to be gentle and speak slowly.

Andrée Starå

Ok.

Here's some more information. Ask away if you get stuck!

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Jenna Pack

I've created a workflow to handle project requests that come in to our department. We have a master sheet where all new requests are entered (either manually or from a webform) then each of our 3 team members have an individual sheet. When the new project is assigned, the row is copied to the individual's sheet (to whom it was assigned). <\/p>

Is there a way to automatically link the status and progress cell back to the master sheet so when a change is made to the individual sheet, the master sheet will change as well? I'd like to avoid having the individuals have to manually link cells every time they were assigned something. <\/p>

I'd even be happy with a replace function where the whole row was copied back to the master and replaces the original row (doesn't look like that exists).<\/p>

I do have approval automation set up for the status cell I'd just like to link cells as well to cover all bases.<\/p>

Thanks!<\/p>","bodyRaw":"[{\"insert\":\"I've created a workflow to handle project requests that come in to our department. We have a master sheet where all new requests are entered (either manually or from a webform) then each of our 3 team members have an individual sheet. When the new project is assigned, the row is copied to the individual's sheet (to whom it was assigned). \\nIs there a way to automatically link the status and progress cell back to the master sheet so when a change is made to the individual sheet, the master sheet will change as well? I'd like to avoid having the individuals have to manually link cells every time they were assigned something. \\nI'd even be happy with a replace function where the whole row was copied back to the master and replaces the original row (doesn't look like that exists).\\nI do have approval automation set up for the status cell I'd just like to link cells as well to cover all bases.\\nThanks!\\n\"}]","format":"rich","dateInserted":"2020-02-13T15:44:34+00:00","insertUser":{"userID":118628,"name":"John Beattie","url":"https:\/\/community.smartsheet.com\/profile\/John%20Beattie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-03-13T14:37:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/65988\/automating-cell-link","embedType":"quote","name":"Automating Cell Link"}"> https://community.smartsheet.com/discussion/65988/automating-cell-link

Did you ever get a more straightforward solution to this problem? I have the same question.

Andrée Starå

Hi@Jenna Pack

I hope you're well and safe!

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!

Be safe and have a fantastic week!

Best,

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

✅做我的帖子(s)帮助或回答你什么stion or solve your problem? Please support the Community bymarking 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!

Jenna123

Hi,

I am trying to auto-populate multiple cells (Linked to show real time changes) in however many rows necessary from another sheet. The workflow should link the info on the trigger of filling in a cell value in the source sheet. Is this possible?

I understand I could set up a workflow to "move" a row, but this does not link the values, and I don't necessarily want all the columns transferred to the other sheet.

Thanks!

Andrée Starå

Hi@Jenna123

I hope you're well and safe!

Do you want to link/show the same information that's in the Main (source) sheet in another sheet (destination)?

If yes, 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.

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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

✅做我的帖子(s)帮助或回答你什么stion or solve your problem? Please support the Community bymarking 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!

Robin Herrick

Hi,<\/p>

I am trying to auto-populate multiple cells (Linked to show real time changes) in however many rows necessary from another sheet. The workflow should link the info on the trigger of filling in a cell value in the source sheet. Is this possible?<\/p>

I understand I could set up a workflow to "move" a row, but this does not link the values, and I don't necessarily want all the columns transferred to the other sheet.<\/p>

Thanks!<\/p>","bodyRaw":"[{\"insert\":\"Hi,\\n I am trying to auto-populate multiple cells (Linked to show real time changes) in however many rows necessary from another sheet. The workflow should link the info on the trigger of filling in a cell value in the source sheet. Is this possible?\\nI understand I could set up a workflow to \\\"move\\\" a row, but this does not link the values, and I don't necessarily want all the columns transferred to the other sheet.\\nThanks!\\n\"}]","format":"rich","dateInserted":"2021-09-16T23:21:53+00:00","insertUser":{"userID":139995,"name":"Jenna123","url":"https:\/\/community.smartsheet.com\/profile\/Jenna123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n65S9PRP4PA0M.png","dateLastActive":"2023-08-25T19:42:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/305297#Comment_305297","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/305297#Comment_305297

Is this possible? No.

Andrée Starå

Hi@Robin Herrick

I hope you're well and safe!

Yes, it's possible!

Please see below.

• If yes, 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.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅做我的帖子(s)帮助或回答你什么stion or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Robin Herrick

@Andrée Staråfair enough. The question at the start of this thread was the other way round, which isn't possible.

"Is there a way to automatically link the status and progress cell back to the master sheet so when a change is made to the individual sheet, the master sheet will change as well?"

Andrée Starå

@Robin Herrick

That's possible as well but depends on the specific need.

A workaround would be to copy the new row to the other sheet and then use cross-sheet formulas to get the information from the other sheet.

Make sense?

Would that work?

✅Remember!做我的帖子(s)帮助或回答你什么stion or solve your problem? Please support the Community bymarking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Robin Herrick

I don't know, but until Smartsheet implements a relational database back end, people are always going to be looking for workarounds to these types of issues.

Andrée Starå

@Robin Herrick

Yes, a relational database structure would be a complete game-changer.

Carsonelli

我有一个类似的问题这里的其他人。我哈ve a master sheet that is designed to track progress through an employee onboarding process where they have to undergo two rounds of reviews. The reviews themselves will be completed on their own sheets, but I am linking those sheets in the Master sheet so users can easily navigate to their trainee's information (see image).

In the individual review sheets, I have a summary cell that counts the number of findings identified in the review. It is the same cell in every individual review sheet (see image).

I would like to see this count on the master sheet. Currently, I'm using the "Link from Cell in Other Sheet" feature to view this number in the master sheet. But when onboarding 100s of employees, this can become quite tedious to link for every sheet.

Since I already have the sheet name referenced in the Master sheet, is there a formula I can use and apply to the entire column to pull the finding count from it's respective sheet into the Master sheet automatically?

Thank you!

bobbrown

This is very similar to our use case. We have a Dashboard, that leverages reports, that points to a Master Data Sheet. Each row points to an individual master sheet for a particular project. Our difference is we have almost two dozen cell links for each project. It would be awesome if we could eliminate the repetitive cell linking exercise.