Replace data in cells of moved row for formula of cells above in destination sheet
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
Answers
-
Andrée Starå ✭✭✭✭✭✭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 detailand maybe share the sheet(s)/copies of the sheet(s) or somescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])
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 bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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å ✭✭✭✭✭✭
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,[email protected])
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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.
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'.
Help Article Resources
Categories
If \"Yesterday\" and \"Today\" are the only text values you end up with, then this formula would do what you're after:<\/p>
=IF([Last Updated]@row = \"Yesterday\", TODAY(-1), IF([Last Updated]@row = \"Today\", TODAY(), [Last Updated]@row))<\/p>
Ironically, typing Yesterday\/Today directly into a date column in Smartsheet will give the relevant date (as will last\/previous\/next <insert day>) but I don't know if the Jira integration would support that or you'd need to use a helper column with the formula as above.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108819,"type":"question","name":"Fill in a cell based on another cell's value?","excerpt":"Hello, I'm tracking participation of a program on my sheet as a \"participation %\" column. There is another column called \"participation status\" to make it easy for the managers to know if their employee is on track with participation or not. I'm trying to set up the participation status column so that if the participation…","snippet":"Hello, I'm tracking participation of a program on my sheet as a \"participation %\" column. There is another column called \"participation status\" to make it easy for the managers to…","categoryID":322,"dateInserted":"2023-08-10T20:29:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T21:09:33+00:00","insertUserID":164943,"insertUser":{"userID":164943,"name":"ciera_wolinski","url":"https:\/\/community.smartsheet.com\/profile\/ciera_wolinski","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zB8mc3uvxuo!pXcnKVOwqro!qMp2YpGJXFb","dateLastActive":"2023-08-10T21:06:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164943,"lastUser":{"userID":164943,"name":"ciera_wolinski","url":"https:\/\/community.smartsheet.com\/profile\/ciera_wolinski","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zB8mc3uvxuo!pXcnKVOwqro!qMp2YpGJXFb","dateLastActive":"2023-08-10T21:06:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":46,"score":null,"hot":3383402334,"url":"https:\/\/community.smartsheet.com\/discussion\/108819\/fill-in-a-cell-based-on-another-cells-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108819\/fill-in-a-cell-based-on-another-cells-value","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108819,"commentID":390127,"name":"Re: Fill in a cell based on another cell's value?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390127#Comment_390127","dateInserted":"2023-08-10T21:09:33+00:00","insertUserID":164943,"insertUser":{"userID":164943,"name":"ciera_wolinski","url":"https:\/\/community.smartsheet.com\/profile\/ciera_wolinski","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zB8mc3uvxuo!pXcnKVOwqro!qMp2YpGJXFb","dateLastActive":"2023-08-10T21:06:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T21:09:38+00:00","dateAnswered":"2023-08-10T20:58:08+00:00","acceptedAnswers":[{"commentID":390125,"body":"
=SUMIFS([Actual Charges]2:[Actual Charges]60, [SOW Reference]2:[SOW Reference]60, \"IPO PMO\")<\/p>
