Automatically changing range in formula

[packet]

Hi, i have a complex rank formula which i use below, however i need to use this same formula for different ranges and rather than keep changing the formula i need to be able to copy and paste it and it will automatically update the row range. I've put those in bold, so essentially i want a range 24 rows long, but then ability to copy that formula and use it elsewhere which is also 24 rows long, any ideas?

=RANK(EF421,$EF$421:$EF$440)+SUMPRODUCT(--($EF$421:$EF$440=EF421),--($EE$421:$EE$440>EE421))+SUMPRODUCT(--($EF$421:$EF$440=EF421),--($EE$421:$EE$440=EE421),--($EA$421:$EA$440>EA421))

 

[Domski]

Change the range refs from, for example, $EF$421:$EF$440 to $EF421:$EF440 and it will make them only column absolute.

Dom

 

[packet]

Thanks Dom, sorry what i mean is that the range for 24 rows needs to stay the same

BLOCK 1:
Rows 1-24 each have a Formula using Range A1 TO A:24
then
BLOCK 2:
Rows 100-124 each have a formula using Range A100 to A124

So i want to use the same base formula without changing the range each time. But the length of the range always stays the same (24 Rows) but the start and end of the range changes for each block.

It might not be possible but thought something using Index/Match may be possible

 

[Domski]

Ok, just to clarify that when you copy the formula down 1 row you want the rows that it references to increment by 100?

Dom

 

[packet]

Yep thats pretty much it,

Rows A1-A24 have Rank range of $A$1:$A$24, then i copy the formula and the rows for that range imcrement by 100 to $A$100:$A$24

 

[Domski]

Just got to pop off but here's an example of a sum formula that shows how can be achieved: =SUM(INDIRECT("A"&(1+(100*(ROW(A1)-1)))&":A"&(24+(100*(ROW(A1)-1)))))

It will increment the range by 100 rows each time it's copied down 1 row.

Dom