Automating the Documentation of Historical Values without Workflows
Problem: How to automatically document historical values without using the automation workflow.
I have a twoline graph of which one line (let's color it black) represents desired results within a date range. This date range ends in the future. The second line (let's color it red) represents actual results within the same date range. The graph shows how the actual results compared to the desired results for the date range. Until the last day of the date range arrives, the red line will only show values up to the current date while the black line shows values for the entire date range.
I have a sheet with three columns that is used to show the twoline graph.
 The first column labeled "Date" lists every day within the range. In this example, if the date starts August 1, 2024 and ends August 31, 2024, there would be 31 rows with the first row having August 1, 2024 and then the next row August 2, 2024 and so on until the last row would be August 31, 2024.
 The second column labeled "Desired Results" would have values entered from August 1, 20204 through August 31, 20204 (this represents the black line on the graph). These desired values are already identified and each row under this column has these values entered.
 The third column labeled "Actual Results" (this represents the red line on the graph) documents the actual value for that day.
While, I have a solution using automation workflow that copies a row to a sheet to keep track of the historical values, for a variety of reasons, I am looking for a solution that will automatically update the Actual Results column without having to manually enter the value on a daily basis and without using workflows.
Answers

There is a lot you can do here with formulas and an additional metric sheet. If the "Actual Results" can be determined somehow with math you're good to go. Where we see this a lot is when it comes to MTD goals.
The first entry of the month = Goal 1 (If it is the same as last month's goal it stays the same)
We take the goal and split it up throughout the working days. It then gives us our daily targets with a compounded value each day to represent it correctly on the chart. etc.Joe Goetschel  Smartsheet Director  SCS CLOUD  Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."

Hi Joel,
Thanks for taking the time to answer, but it's a bit different. Consider an example of a shoe store. They are predicting within a date range how many shoes they plan to sell on a daily basis. For each day in the date range the number of predicted sales is already entered. Then on a daily basis they track the actual sold, and that number needs to be stored in the "Actual Results" in the same row where the date in the Date column is today. My hope is to automatically copy the value at a specific time of day to the appropriate row. Perhaps the screenshot below may help. The problem I'm having is that any conditional formula I use returns one of two values, the expected value or some other value (such as a blank) which if this formula is used for the entire column, overwrites any historical information stored in the previous days. In conjunction with this issue, there appears to be no way to create a formula that states, don't do anything if there is a value already in the same cell the formula is in. Ideally, the problem would be solved with an "IF" statement that if true is able to write the value to another cell and not within the cell where the formula exists. While this problem has been solved using automation to copy the information to another sheet as a workaround for an "IF" statement not being able to write a value to another cell, I'm hoping there is solution that bypasses automation. Hope this helps.

I'm not sure of your use case, but you could have them enter today's actual in a separate sheet and then through a workflow put an entry date in a separate column each time the entry is made. You could also make it simpler for the entry person to enter the data through a form and populate the date each time when the form is submitted. No automation is needed if via a form.
Then on your daily summary sheet, summarize the Actual Results from the input sheet.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!