Conditionnal copy of a cell to another cell

Simon Lessard

Hello everyone!

I'm using a sheet to evaluate the ressources required for a bunch of quick projects.

Each project have it's own row and I have 3 columns for initial effort evaluation of every department (mec_eval, elect_eval, software_eval), plus 3 copies of those department effort columns that I use to track the remaining effort, updated each month (mec_real, elect_real, software_real),. I also have a Status column.

First, we enter the estimated effort of the project in the 3 columns (*_eval). Project Status is set to "Proposed".

When I change the Status to "Accepted", the project begins and I would like to have the values entered in *_eval columns copied into *_real. I don't want a dynamic link between cells *_eval and *_real because I want to update *_real with real values as the project moves on and keep record of the original evaluation we made for that project so *_eval should never change once Status is set to "Accepted".

It would be very useful if Smartsheet had automation Actions on cells, not only on rows.

Any thoughts on how to do this?

Thanks

Simon

Paul Newcome

You can use a basic formula to pull the eval values to the real cells temporarily. Then when one of the real cells is manually updated, it will delete the formula.

This would provide for an initial copying from eval to real but then allow changes to the real columns as the project progresses.

So in the [mec_real] column, you would simply put

=[mec_eval]@row

This will duplicate the eval row in the real row until the real row is manually updated.

Andrée Starå

@Simon Lessard Excellent!

I'm always happy to help!

Unfortunately, there isn't a public roadmap anymore, but I've heard chatter that they are thinking about how to handle requests and possibly the roadmap in the future.

Andrée Starå

Hi Simon,

Unfortunately, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

The third-party service, Zapier would be an excellent option for this scenario. Is that an option for you?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

L_123

I wonder if this can be accomplished with a second sheet,a cross-sheet reference, and a copy to workflow? I don't know if it would create a circular reference would be the issue. Also there would be a bit of lag between changing status to accepted and the sheet updating, as the background sheet would have to process before it could update.

I think this solution would work, but it would require a bit of effort and testing.

Andrée Starå

@L@123 That might work but then all the formulas would be removed on the row. (if any are used)

When it will be possible to select which columns should be copied I can see it work.

Paul Newcome

You can use a basic formula to pull the eval values to the real cells temporarily. Then when one of the real cells is manually updated, it will delete the formula.

This would provide for an initial copying from eval to real but then allow changes to the real columns as the project progresses.

So in the [mec_real] column, you would simply put

=[mec_eval]@row

This will duplicate the eval row in the real row until the real row is manually updated.

Andrée Starå

@Simon Lessard If it works to have formulas to start then @Paul Newcome's solution would be perfect.

Simon Lessard

Thanks everyone for your help.

I believe this is an acceptable workaround. I also submitted a feature enhancement request. I believe this would be great to be able to have Actions on cells, not only on rows.

It is surprising that Zapier can do it but not Smartsheet!?

Do you know if we can have a look on the product's roadmap? I'm wondering how Smartsheet is evolving. I've seen this link to suggest Enhancements in a lot of threads but I am curious to see if they really take those into account (I bet they do) and how often the release new versions.

Andrée Starå

@Simon Lessard Excellent!

I'm always happy to help!

Unfortunately, there isn't a public roadmap anymore, but I've heard chatter that they are thinking about how to handle requests and possibly the roadmap in the future.

Paul Newcome

@Simon Lessard

Happy to help! 👍️

In addition to what Andree said regarding the roadmap, I will say that regarding new versions updates are fairly regular. I do know that 2019 was pretty busy with new features being rolled out relatively frequently.

Simon Lessard

Good to know!

Thanks

Andrée Starå

@Simon Lessard Have a look here to see the history of updates. Everything isn't included but it will give you a sense.

https://www.smartsheet.com/content-center/product-news/release-notes/release-notes

https://www.smartsheet.com/content-center/product-news/release-notes