How to implement a formula every three cells. without messing up its configuration.

Question

I am using a forecasting formula in excel withthin an IF function. here it is: =IF(AI28<>"",AI28,IFERROR(FORECAST.ETS(AI$2,INDEX($E$28:AH$28,1,1):INDEX($E$28:AH$28,ROW()-ROW($E$28),COLUMNS($E$28:AH$28)),INDEX($E$2:AH$2,1,1):INDEX($E$2:AH$2,1,COLUMNS($E$2:AH$2))),LOOKUP(2,1/(AI$27:AI28<>""),AI$27:AI28))). This formula will go in every three cells for example for this one ^, I place it on AI29, the next one will go on AI32, however I need these parts in the formula to also change in this manner: AI$27:AI28))) -> AI30:31. (AI28<>"",AI28 -> AI31,AI31. as well as all the index that state: E28 -> E31. It needs to be placed for a massive amounts of products so I am trying to streamline it as much as I can, any help?

Answer 1

Remove the $ from before any row number in the cell references where you want the new copy of the formula to refer to the "next" cell. For example, if you change the expression INDEX($E$28:AH$28,1,1) to INDEX($E28:AH28,1,1), when you copy the formula to cell A132, it will automatically change to INDEX($E31:AH31,1,1).

Leave the $ in the cell reference where the row number should not change, such as possibly AI$2.

On a different topic, please note that if you are copying the formula only to other cells in column A, the $ before the column letter serves no purpose. However, if that is not the case, then your use of this feature seems in inconsistent. There are numerous range references where the left column is being held constant and the right column is not. For example, the expression INDEX($E31:AH31,1,1) would change to INDEX($E31:AI$31,1,1) when copied to cell B129 but column AI is already in use in other expressions.