Automating the Documentation of Historical Values without Workflows

mlinkous111786

Problem: How to automatically document historical values without using the automation workflow.

I have a two-line 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 two-line 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.

Joe Goetschel

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.

mlinkous111786

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.

dojones

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.

mlinkous111786

Thank you @dojones! I appreciate everyone's response in trying to figure out this dilemma. I'm hoping to find another way to do this without workflows as this is a template with a set of sheets in a folder and when someone is copying the template/folder, there is a high degree of probability that the name of the new sheet is not being changed in the automated workflow and it is instead populating the sheet referenced in the template.