References In Ranges

3LeggedDog

Board Regular
Joined
Feb 11, 2006
Messages
149
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
Joined
Feb 13, 2004
Messages
4,814
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top