Dynamic ranking

[cogswel__cogs]

I would like to offer on a summary page a rank column, but would like to allow user to decide what column ranking is performed on.
Do I want to write the rank using index/match or offset?

 

[sheetspread]

Here's 1 way:

Excel 2010
	A	B	C	D	E	F	G	H
1	Name	Series1	Series2	Series3	Series4	Series5	Series6	Series7
2	Q	4	11	90	37	93	66	76
3	W	41	39	33	67	59	78	22
4	E	82	48	21	26	78	82	79
5	R	94	18	76	19	29	58	95
6	T	69	23	64	87	4	98	83
7	Y	5	78	62	63	29	48	49
8	U	75	83	51	87	20	26	7
9	I	49	46	17	9	89	35	26
10	O	93	27	75	80	21	89	98
11	P	12	24	75	25	15	98	59
12
13
14
15	4
16	Q	6
17	W	4
18	E	7
19	R	9
20	T	1
21	Y	5
22	U	1
23	I	10
24	O	3
25	P	8
Sheet12

Cell Formulas
Range	Formula
B16	=RANK(VLOOKUP(A16,$A$2:$H$11,$A$15+1,0),INDEX($B$2:$H$11,,$A$15))

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H
1	Name	Series1	Series2	Series3	Series4	Series5	Series6	Series7
2	Q	4	11	90	37	93	66	76
3	W	41	39	33	67	59	78	22
4	E	82	48	21	26	78	82	79
5	R	94	18	76	19	29	58	95
6	T	69	23	64	87	4	98	83
7	Y	5	78	62	63	29	48	49
8	U	75	83	51	87	20	26	7
9	I	49	46	17	9	89	35	26
10	O	93	27	75	80	21	89	98
11	P	12	24	75	25	15	98	59
12
13
14
15	4
16	Q	6
17	W	4
18	E	7
19	R	9
20	T	1
21	Y	5
22	U	1
23	I	10
24	O	3
25	P	8
Sheet12

Cell Formulas
Range	Formula
B16:B25	{=RANK(INDEX($B$2:$H$11,,$A$15),INDEX($B$2:$H$11,,$A$15))}
Press CTRL+SHIFT+ENTER to enter array formulas.

works too

 

[cogswel__cogs]

This seems to be what I need but I am having difficulties rewriting it.
Not sure why?

 

[sheetspread]

How does your data look?

 

[cogswel__cogs]

Got it.
Many thanks, I had a spacer column in my data so I needed to mess with the column directives.