Store cell data on specific date

questioningly

Hello,

I'm needing some help in figuring out if it's possible for smartsheet to pull data to a cell on a specified date. For example I need to hire 5 people for a building opening on 12/1/2018, but they need to be hired 8 weeks prior.

What I want to do is have one cell that takes the hired count cell and pull the info on 10/6/2018 (8 weeks prior). This way I can go back later and easily see how many of the 5 people were hired on time.

 

Thank you

 

Mike Wilday

Can you explain what you mean by "Pull the info?"

questioningly

Yes, I attached an example here of what my smartsheet is doing. So column 4 is filled weekly by another person based on what they've managed to hire. I'm looking for a way to automatically fill column 5 with the data in column 4 on the date listed in column 6.

Thank you

Mike Wilday

=IF(today() >= [column6]6, [Column4]6)

That should do the trick for you. You'll need to put that in column 5. ON the date of 8/11/18 you will get the number that is in Column 4 You can drag the formula down into subsequent rows.  You can test it by changing the Fill positions by date to something in the past. 

 

questioningly

If I understand correctly then every time I open the sheet past the date in column 6 it will continue to populate column 5? The problem is the other editors of the page will continue to update column 4 over time every week, but I want the value in column 5 to stay locked to what it was on the date in column 6.

Thanks

Mike Wilday

You can't hardcode the data from a formula. As far as integrated options, there aren't many. You could set up a Notification or Reminder to be sent to you based on the date in column 6 and then you could manually record the data on that date. 

The only way to automate it would be to use a third party tool like Zapier. I don't have much experience with it but you can contact J. Craig Williams who has a ton of experience. Zapier does cost extra depending on how its used and how much. 

Mike Wilday

Here is a link to learn more about Reminders and Alerts in Smartsheets.

https://help.smartsheet.com/articles/2476191-alerts-stay-on-track-with-automatic-notifications-and-reminders