Replace data in cells of moved row for formula of cells above in destination sheet

Mark Berg

I've created an automation for moving a row to another sheet. After moving I want in some cells that the original data that came from the source sheet will be replaced for a formula as in the above rows of the destination sheet. Until now it ignores these formula's and keeps the original data.

Is it possible to let this original data automatically be replaced by the above formula's?

Many thanks in advance,

Mark van den Berg

Andrée Starå

Hi @Mark Berg

Unfortunately, it's not possible now because if there is anything in the cell before they move, the formula won't auto-fill, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

It might be possible to create a workaround.

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, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic day!

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!

Mark Berg

Dear Andree,

Thanks for your answer. This project is a project in spare time, so that's why I'm answering a bit late.

I'm building a planning tool for us as a trial farm for a seeds company. This is a tool for our stakeholders to submit the tasks we need to execute. One of the tasks is sowing for young plant raising, and a subsequent task is transplanting the young plants. I want to have the opportunity to fill in one form the task for sowing as well as the subsequent task for transplanting. To have different column headers I've made a general 'execution date' column and for the transplanting task a 'planting date' column.

However, as an overview I want the subsequent planting task in one overview with all the other task to be executed. For this I made an automation to move all the sowing tasks with the subsequent transplanting tasks to a new sheet. In this sheet I want to have the execution date column to be changed in the value that's in the planting date column. Also I want the worktype to be changed from 'Sowing' into planting.

My idea was to extract from both sheets the information into 1 report. In this report the transplanting task, which was putted in as a subsequent, would appear as a normal task.

However, I made formula's in the existing rows in this new sheet, but still every new entry keeps it's source data and doesn't use the above formula's.

What would be helpful, and what I've submitted as an enhancement request, is that you can make a selection in the automation of columns that need to be copied or moved to another sheet. In that case I wouldn't have copied the execution date column to the new sheet and made a new one there with a reference to the planting date and a new column with the type of task.

A work around that I thought of today is to also move all the submissions to a new sheet. In this new sheet I create a new execution date column with a different name (eg date execution) and do the same in the planting sheet. This I will try shortly, than the reporting tool should work I suppose

Andrée Starå

@Mark Berg

No worries! My turn now! 😉

I think that Smartsheet is working on a feature to select which columns should copy/move.

I'd be happy to take a quick look.

Can you 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, andree@getdone.se)

Mark Berg

Hi Andree,

No problem to share it, but I don't know how I can do this. I've added the link below.

https://app.smartsheet.com/workspaces/6pmff78vxC3pp753q5V2pvmq5R7jwcwH9hwJHMj1

Because this is still in development it's all test information.

The flow is as following now:

All new demands come in in the 'werkopdrachten input file' via a form. After that is goes automaticly to 'werkopdrachten masterfile' and 'Plantopdrachten'. The info from masterfile en plantopdrachten are brought together in the report 'Open werkopdrachten totaal'.