References In Ranges

[3LeggedDog]

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
	AF	AG	AH	AI
1	IPouts
2	29		168	2
3	124		168	2
4	264		168	2
5	163		168	2
6	194		168	2
7	206		168	2
8	224		168	2
9	293	DifferentTeam	168	2
10	182		168	2
11	496	DifferentTeam	168	2
12	209		168	2
13	65		168	2
14	287	DifferentTeam	168	2
Sheet1

 

[acw]

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

 

[PaddyD]

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