Is it possible to dynamically change the range when overwriting?

Question

So for example lets say I am reporting weekly forecasts on a 3 month cycle ( right now its Sept, Oct, Nov). I export and write  to an excel sheet. Now when a new month starts (October), I want to report on the previous month(Sept) one last time and append a new month (December) to keep the 3 month cycle. I want to be able to Overwrite what is in the table (excel) for September (as now these turn from forecasts to actuals),October, and November and then append the forecasted numbers for new month of December.

Currently I just copy and paste the output from Alteryx over to the excel but I would like to automate this, but I am unsure about how to deal with this scenario. The problem I am facing is that when overwriting you have to specify a range you want to overwrite but in my case that range I want to overwrite changes for every new month (i.e a new month is introduced thus making the excel longer changing the range).

So this is what is looks like for the last week of September.  Although there will be more rows for different stores etc.

MonthYearSales September2021240October2021250November2021300

Now when I run it again first week of October I would like it to overwrite September, October, November and Append December Numbers.

MonthYearSales September2021280October2021300November2021350December 2021400

For the first week of every new month I would like to overwrite the previous month and the 2 other reported months then also append the new month. Then continue overwriting all 3 months until the first week of the next month in which October will be overwritten one last time and then January 2022 will be added so then I will be reporting on (Nov, Dec, Jan)

Maskell_Rascal · Answer

@Aviator0807 - can you share part of your workflow with some sample data and the calculations in question?

Aviator0807 · Answer

@Maskell_Rascal Yeah that's one solution I was thinking of, only problem with that is I would have to re-calculate all months every week indefinitely. Like Sept. should be done next week and never touched again. But if I do it the way you are suggesting I will have to run it again in October and November and December..etc or else it will get lost. Same with Oct once we hit November and so on.

Maskell_Rascal · Answer

Hi @Aviator0807

In the Output tool you can select a range to write to, which I'm assuming you already knew. What's more interesting is that you can select a much longer range and the tool will overwrite all those cells even if there is no data from Alteryx.

In this example, I selected range of A1:C1000, even though my data will only be a max of 3-4 rows.

The file I'm outputting to already has 4 rows of data, but my incoming data will only have 3 rows.

Current file:

Incoming data:

Output after running workflow:

Let me know if that works for you.

Cheers!

Phil