Cell values export from one project to another

Cyril_H

Hello,

We are currently managing a construction project with smartsheet and have created a Gantt Diagram and a few Dashboard to monitor several KPIs.

The dashboards are updated automatically with every changes in the master project and provide up-to-date information during meeting.

I would like to add curves to the dashboard which would show the % completion of different tasks over time and finish date of said tasks.

These curves will allow us to spot missed milestones, or work disruption which could be adressed and discussed in the meeting. Therefore, i would need an "image" of the master project at different dates.

In Excel, I would create a macro which would export once a day or week several cell values in a different sheet and create the graph from there. One export = one point in time.

I'm pretty new to Smartsheet and I'm encoutering difficulties to simply copy/past cell values from one project to another (the Smartsheet ressources tell me to use a formula, then a cross sheet reference but i don't need any formula, just cell values).

Is there a way with Smartsheet to export cell values from one project to another without using formula? Can it be done automatically and periodically? How?

Smartsheet seems to be able to periodically export entire row from one project to another but it's a bit overkill in regard to my need.

I hope my demand is clear and understandable. Thanks in advance for your help.

Best Regards.

Cyril.

Paul Newcome

Instead of trying to capture the TODAY function on the source sheet to have it transferred to the target sheet, you can add in a system generated Created (date) type column to the target sheet that will capture the date the row was copied in to it. For display purposes, you could create an additional date type column on the target sheet that has a formula to pull just the date from the date/time stamp such as...

=DATEONLY([Created (date]@row)

Paul Newcome

While it does seem like overkill, the Copy Row automation seems like it may be the best option for you. Otherwise you would need to set up a sheet that has dates and use formulas similar to the ones you are currently using but include in them date ranges.

Cyril_H

Thanks for your reply Paul.

As suggested, I've organized my data on an only row in order to rely on Copy Row automation. I've then created a new column in the master project and used the "Today()" formula to track the date of export.

I let the automation run this weekend as a test. It seems that it's not possible to update the formula before copying the rows from one sheet to another without prior opening of the master project. Is there a way to make sure the date is automatically updated before the export?

Thanks for your help.

Paul Newcome

I'm not sure I follow exactly what you are trying to accomplish... What is the purpose of the TODAY function?

Cyril_H

I'm trying to copy paste datas from one sheet to another sheet to keep track of changes and plot them in that fashion :

The cell with the TODAY formula would be copy/paste too in the new sheet and be equivalent to the X-axis.

I used the TODAY formula because i thought this formula would be updated before the copy/paste action. However, it seems the formula is not updated because the sheet is not open.

In the pictures presented previously, there are 3 rows with the date "28/02/20" but there were 3 copy/paste sequences since last friday (saturday 29/02, sunday 01/03 and monday 02/03).

Paul Newcome

Instead of trying to capture the TODAY function on the source sheet to have it transferred to the target sheet, you can add in a system generated Created (date) type column to the target sheet that will capture the date the row was copied in to it. For display purposes, you could create an additional date type column on the target sheet that has a formula to pull just the date from the date/time stamp such as...

=DATEONLY([Created (date]@row)

Cyril_H

Thanks, I followed your recommandation and it's working perfectly!

Paul Newcome

Excellent. Happy to help! 👍️