I am trying to write a formula that would move the Unit sold from the previous years to the year selected in column G. Example if I select year 2025 in column G then product should show the number that is in 2022 example 100, 2026 101,2027 102. Similarly if I select 2026 in column G then 2026 should have 100, 2027 should have 101.

I wrote the code =IF($G$6>I2,0,OFFSET(I6,5,-2,1,1)) but I couldnt get the refence to be dynamic by year. What am I doing wrong?

CodePudding user response：

The argument "Cols" in the offset should be a formula that includes G6.

The solution is: =IF($G$6>I2,0,OFFSET(I11,0,$I$2-$G$6))

Add the formula to I6 and "fill" the cells to the right.

If you have any question, please let me know in the comments. There are several other possible solutions.

CodePudding user response：

To avoid OFFSET which is volatile and recalculates at any change in the file, you could accomplish the same with INDEX.

Use this in I6 and drag to the right: =IF(I$2<$G$6,0,INDEX($I$11:$P$11,1 I$2-$G$6))