Copying Date columns and making them editable from reports

Rob Cooper

Hi,

Back again with what I hope is a simple fix.

Background - I am creating a global project tracker, used to manage and maintain over 2k current projects and ~100 new/ intake projects on a monthly basis.

Requirement - The intake form feeding the SmartSheet is asking for a Start Date (intake), Launch Date (intake) and Closed Date (intake). I have three additional columns called Start Date, Launch Date and Closed Date which will be maintained by the project managers updating their reports. I need to be able to copy the three dates captured at intake into the dates the PMs will be maintaining. The intake dates will be locked and will not be visible to anyone, but will be used to gauge differences in delivery dates upon completion.

I can't use the update Date workflow, as this just inputs the current system date and not the date I want copying over. I can use index, as this will always show the intake date and the project may be delayed and these dates will need to be different. I have created a formula to copy it over, but by doing this it will then not allow for the PMs to update the dates from reports, due to the column have a query in it.

Any suggestions here would be much appreciated.

Kind regards

Rob

Genevieve P.

Hi @Rob Cooper

What if you had one sheet which was specific to the Intake information, then one sheet you're using for the actual tracking with both the intake information and the additional columns?

This way you could set a Copy Row automation to copy a row over from the Intake sheet to the Actual sheet when a row has been added. This could copy the data for your initial 3 dates into the Start Date / Launch Date / Closed Date actual columns, which can then be edited by the PMs.

Then you could create three Date columns that aren't in the Intake form to house the original intake dates by using cross-sheet formulas to look into your original Intake sheet and return these dates. Since these three intake dates shouldn't be changed, it would be OK to have these be populated through a formula.

This would copy/paste the intake information into your actual columns, then use a formula to re-copy the original data into columns that won't change. Does that make sense? Do you think this would work for you?

If you need help building the cross-sheet formulas let me know! These would be INDEX(MATCH formulas so you would need a unique Project Number to match between the sheets.

Cheers,

Genevieve

Rob Cooper

Hi Genevieve,

First of all, thank you for taking the time to respond to my query.

If I was at the start of my build I would most definitely look into this more. Unfortunately I am about 2 months into a complex build, with the copying of these dates being the final piece. I honestly thought this would be simple, which is why I left it until the end.

All I want to do is copy three cells into a another 3 cells and make them editable within a report!

Genevieve P.

Hi @Rob Cooper

Thank you for clarifying. Without re-organizing your process, I can think of one other way to allow your PMs to update what dates are shown in the Actual Start/Launch/Close date columns.

Cells with formulas cannot be edited from Reports, as you've experienced. However you need your actual Start/Launch/Close date columns to be populated automatically, either with the original dates entered or with new dates from the PM, which would require a formula to do this automation.

Because of this, I would suggest having 3 helper date columns in your sheet for the PM to fill out as their "update" columns. Then your actual dates can have a formula that either returns the intake dates or the updated PM dates.

Ex. you would have 3 Start Date columns:

• Start Date (from Intake)
• Start Date (either showing intake or PM's date)
• Update Start Date (to allow PMs to edit).

In your Report, you would show the actual Start Date column which would be a read-only field because of the formula. Then you would have the "Update Start Date" column next to it, if the PM needed to change what date is shown in the Start Date. You can hide the "Update" fields in your sheet and only display them in the Report.

The formula I used in my actual date columns is as follows:

If the Update field is not blank, return that date. But if it is blank, return the Intake date.

=IF([Update Start Date]@row <> "", [Update Start Date]@row, [Start Date (From Intake)]@row)

Although this duplicates columns in your Report, it means that the actual Date columns can be auto-populated.

If this process won't work for you either, then you may want to look into solutions outside of the core Smartsheet application, such as using Bridge by Smartsheet to copy cell information, the Smartsheet API, or third party applications like Zapier.

Cheers,

Genevieve