Need help on using the offset function for monthly data changing yearly

[DaySouth]

Good day,I have a 10 year monthly model with a number of worksheets. I would like a variable on my offset function to change after every year,to be specific, the syntax is: Offset(Input!$b$23,0,MONTH(Output!c2))*Input$h$24...I would like h24 to change to I24 after one year i.e 12 months can anyone help?

[Bernie Deitrick]

You could change Input!$H$24 to be something like

INDEX(Input!$H$24:$Z$24,Year(some cell) - 2015)

But without the specifics it is hard to say exactly how to fix it....

[DaySouth]

EXCEL.PNGExcel2.PNG
Ok thank you,I have attached screen shots of the work, the yearly assumptions are in blue and on the second shot you have the monthly data that needs to match that particular year's yearly planting area.I hope its clear...

[Bernie Deitrick]

What values would you want in those cells?

[DaySouth]

I would want the different proportions of area planted (planting) given in row 23 on the Assumptions tab(not shown) multiplied by the hectares planted given in row 26 of the Assumptions tab as shown on the snippet. Now the offset function works fine for this but what I want is for the figure of the planted area to change(given in row 26) when I get to the month of July in each new year to assume the value of the area planted in that year maintaining the proportions adopted from row 23 in the Assumptions tab as I drag the formula without manually copying and editing it,something like a when loop (if there's something like that)

[Bernie Deitrick]

We can use the month and year from the date cell, but I really need numeric examples: this * that * this other value, but when this cell changes to this date, then I want this * that * this completely other cell. I see values labelled 2016, 2017, etc, but nothing labelled 2015.....