# References In Ranges

#### 3LeggedDog

##### Board Regular
In the sheet below, I'm trying to figure out how to make the range dependent upon two values. I'm trying to get the range to be in column A, all of the time, but for the row values to be from the number shown in AI to the number shown in AH. In the case shown below, I'd want the range to be A2:A168, but those two numbers have different values in different points in the sheet. The idea is to then use that range to do the rank function. I know how to do ranking (thanks to Aladin), just giving the context.

Thanks
pitching 1871-2005.xls
AFAGAHAI
1IPouts
229 1682
3124 1682
4264 1682
5163 1682
6194 1682
7206 1682
8224 1682
9293DifferentTeam1682
10182 1682
11496DifferentTeam1682
12209 1682
1365 1682
14287DifferentTeam1682
Sheet1

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### acw

##### MrExcel MVP
Hi

How about creating a dynamic defined name to cover the range.

Name: MyName
Refers to: =OFFSET(\$A\$1,\$AI\$2-1,0,\$AH\$2-\$AI\$2+1,1)

Tony

##### MrExcel MVP
with

ah2 = 168
ai2 = 2

INDEX(AF:AF,AI2):INDEX(AF:AF,AH2)

returns the reference af2:af168, used, e.g:

=SUM(INDEX(AF:AF,AI2):INDEX(AF:AF,AH2))

Replies
9
Views
544
Replies
1
Views
276
Replies
4
Views
138
Replies
0
Views
320
Replies
1
Views
46

1,136,924
Messages
5,678,598
Members
419,774
Latest member
MooseWinooski

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