Update cells with regards to Date@row

smartsheet756

I have a formula =IF(TODAY() = Date@row + 1, {insertDATA}, ""). Once the date hits the cell updates and I run a workflow to lock this row, however once another day passes this cell becomes invalid. How can I get this updated value to permanently stay?

Paul Newcome

What are you using in the {insert data} portion of the formula?

Kaveri Vipat

Hi smartsheet756 

I hope you are doing well, 

As I am assuming if your criteria will match then you are getting a reference from a single cell “insertDATA” Then your formula is working fine but if you are taking reference from a column then it will give an error, so I want to know what reference you are taking from a column or a single cell and if you can add some screenshot so it is helpful for us. 

I would suggest you if you were getting references from a single cell. You have to create automation and use change a cell action and paste the value in the text box which is in the “insertDATA” cell to record the value using another column.  

I hope this is useful to you, please let me know if any changes are required.  

Have a Good Day. 

smartsheet756

The data currently there was populated directly by me so ignore it. Yes, {insertData} is a single cell reference from another sheet that will change every Monday. Every Tuesday (hence why I use Today()+1) I want the rows to populate week by week. The issue I am having is I cant get the value to permanently stay, it is lost when the IF statement is false. How would I correct this?

Paul Newcome

Is you screenshot of the source data for your {reference}?

smartsheet756

No this isn't my source data, the source data is a cell in a completely different sheet.

Paul Newcome

Are you able to provide a screenshot of the source for the cross sheet reference? Is there a reason you are creating a cross sheet reference for a single cell instead of referencing an entire range?

smartsheet756

So as you can see from the previous screenshot every week for the entire year there is a percentage. Every week I get new data, the other sheet calculates the percentage of this data that meets a certain criteria and displays it in a single cell (as I said the populate data was inputted directly by me hence why its not lost). When the data updates next week this single cell will change value. I need to permanently store the value of this cell week by week to generate a graph to see trends.

Could I potentially do this using some form of workflow that every week at a certain time copies the value to the next available row so that I am not using the Today() function and losing data? If needed I could incorporate it all into one sheet however as I do way more than this with the data I would like to keep things separate.

Thanks

Paul Newcome

I think I am understanding a little bit better now. I would suggest a copy row automation set to trigger on a weekly basis, but it would have to be sent to a separate sheet. So you can continue to use the TODAY function to maintain the current data, and the copy row automation would push it over as static data to a "trend" sheet.