# Automatically changing range in formula

#### packet

##### New Member
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))

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Domski

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

Dom

#### packet

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

Replies
1
Views
159
Replies
1
Views
293
Replies
1
Views
455
Replies
1
Views
416
Replies
1
Views
230

1,191,691
Messages
5,988,111
Members
440,126
Latest member
duque00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back