Create a dynamic named range or formula to change the range in the forecast formula

Hi Guys, just wondering if any of you excel gurus can help?

I have a worksheet that forecasts spend, as can be seen in the screenshot:
Column D populates Months from another worksheet "=EDATE(B6, SEQUENCE(D3,1,0))"

Column E populates Spend from another worksheet "=IF(D9="","",SUMIFS(TRACK_InvValue,TRACK_InvDate,">="&D9,TRACK_InvDate,"<="&EOMONTH(D9,0)))"

Column G generates a forecast for months that don't yet have an actual value "=FORECAST.ETS.CONFINT(D26,$E$9:E25,$D$9:D25,Lists!$V$3,1,1)"
If there is a value in the spend column, the the formula shouldn't calculate a forecast figure.
If there is a $0 in the spend column, the formula should calculate a forecast from the spend figures available.

What I want to do is create a dynamic named range or have a dynamic formula that will change the range in the forecast formula that will automatically update as new actual figures appear in the spend Column E

I do hope this makes sense? As it stands the forecasting does work, however the user must manually adjust the forecast.ets.confint range every time a new spend figure appears in the spend column. Users of this template will have little experience with excel formulas so I don't want them trying to extend ranges and messing up their figures (the more automation and less user input the better).

Thanks Guys

Shot1.png

Untested, but try

Formula:

Please Login or Register  to view this content.

Maybe:

Formula:

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As yo uare new here, I have done it for you this time.)

Again, untested

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.