How to loop back information to source sheet.

Pär Tindervik · 03/11/21

Hi all,

Is there a way to push/copy cell information from one sheet to the source sheet. We have a master record with the master data for all projects. Each project then collects some of the information from the master record including approved budget based on the project ID column to the project template sheets. The project then consumes time and resources generating a cost/allocated budget in the project sheet. What we would like to do is to bring back the allocated budget to the master data sheet and to the correct project ID

As projects are added continuously with and new project ID´s and corresponding project sheets we can not hard code a cell reference but would rather for the project sheet to push the allocated budget from the project sheet to the master record sheet as that sheet will always be on the same path.

Hope this makes sense, if not please let me know and i will try to explain mer elaborate.

/Pär

Andrée Starå · 03/11/21

Hi, Hej! @Pär Tindervik

I hope you're well and safe!

Have you explored Control Center?

A solution that would be perfect for this use-case is the premium add-on, Control Center.

Is that an option?

More info:

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

Would that work/help?

Let me know if you'd like more information.

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!

Pär Tindervik · 03/11/21

Hej Andrée, and thanks for the quick reply.

Nope, have not explored control center yet. Will do so tomorrow and get back to you if it will accommodate our needs.

Ha en bra kväll så hör vi! / Have a nice evening!

Br Pär

Pär Tindervik · 03/11/21

Hello again,

Had a quick look and the control center does not accommodate the needs we have at this point. So the question still remains open.

Br Pär

Andrée Starå · 03/11/21

@Pär Tindervik

Happy to help!

What are your requirements more specifically?

Also, why don't you think Control Center would accommodate them? I think it would! 😉

Feel free to contact me at andree@workbold.com if you want to continue in Swedish. Of course, it would ok to continue here as well.

Pär Tindervik · 03/12/21

Hi, all

Found a solution to this problem by twerking smartsheet a bit. Since the master record had numerous multi tier project templates connected to it and we wanted to collect data from these to be presented in a rollup based on the aggregated info from the project, for example we want projects that are at risk for delay or becoming over budget to be displayed.

Using INDEX function to collect data from the master record to the project data sheet (approved budget, project sponsor etc.) It will collect allocated budet, hours and so on from the project template.
Run a workflow on the project data sheet that will copy the information from the project data sheet of all projects to a "data collection" sheet in the master data folder. The work flow will be triggered as soon as any of the information in the project data sheet will change.
This till render multiple lines in the data collection sheet, this will cause a problem using Index to retrieve the information back to the master data sheet. This was solved by adding a creation date column. Next we created a checkbox column and named it "old row". Using and IF function including MAX and COLLECT we where able to populate the checkbox if there was another newer row with the same project ID in the sheet.
As we would be using an INDEX funtion to collect data from this sheet we needed to remove the oldest row. As Smartsheet currently don´t hade the possibility to automate the deleting functionality the was solved by creating a workflow that moved the old line in to yet another sheet called "the scrap heap" when the "old line" box was checked.

This gave us the functionality that we where aiming for. Maybe not the most glorious solution but it serves it purposes :)

Hope the above explanation makes sense, if any one would like for me to post the formulas used, just let me know :)

Br Pär

Andrée Starå · 03/12/21

@Pär Tindervik

Excellent!

Glad you solved it, and thanks for sharing!

Darvin Antonio · 03/12/21

https://community.smartsheet.com/discussion/comment/283373#Comment_283373

Hi Par,

Can you share your formula? I’m trying to do the same thing!

Thank you,

Darvin

Pär Tindervik · 03/12/21

Hello Darvin

https://community.smartsheet.com/discussion/comment/283376#Comment_283376

From Master record to Project data sheet I used INDEX
From Project data to data collection i Used a workflow with copy row
To set the checkbox as marked to identify an old post i used "=IF(Created@row <> MAX(COLLECT(Created:Created; [Project ID]:[Project ID]; [Project ID]@row)); 1)3"
To delete old rows I used a work flow to move row to Scrap heap sheet if checkbox was checked.
I then used INDEX to collect data and populate columns from the "data collection" sheet to the "Master record" sheet

There are two things to watch out for with this solution:

IF you make changes to the project sheet that will generate changes in the Projec data sheet within the same minute it will generate double lines in the data collections sheet and the index fomula will pick up only the first one.
The "scrap heap" sheet need to be emptied (delete all rows) from time to time....

Hope the above helps,

BR Pär

How to loop back information to source sheet.

Answers

Categories