Count a fixed number of rows in Excel formula

I'm trying to create a formula that I want to include only 12 rows down from a certain field.

The main issue that I'm having is that I have a formula in the first row and it calculates things on fields A2:A13. I constantly add new rows at A2, this is a requirement. I want to keep the old data for archiving, but want the formula to only consider A2:A13 no matter how many rows I insert on top.

For more clarification, here is the formula: =IF(COUNT(I11:I$26)=0,"N/A",INDEX(I11:I$26,MIN(IF(SUBTOTAL(3,OFFSET(I11,ROW(I11:I$26)-ROW(I11),0)),ROW(I11:I$26)-ROW(I11)+1))))

Basically, I'm looking to figure out how to do A2:(A2+12).

Any help would be appreciated!

Solution 1:

Use the INDIRECT function to refer to these cells inside the formula. This function returns a valid cell reference from a given text string.

You would refer to A2:(A2+11) by INDIRECT("$A$2") and INDIRECT("$A$13").