Rank using indirect

[Peter Davison]

Hi
I have a range where I want to use a simple rank for the products
Say this -
=RANK.EQ('Unit Sales'!AS6,'Unit Sales'!AS$6:AS$5000,0)
However, I want to use indirect to read The words "Unit Sales" from a cell AN1

When I use the following code it says circular reference when I allow the AS6 to be able to copy down and across
=RANK.EQ(INDIRECT("'"&$AN$1&"'!"&AS6),INDIRECT("'"&$AN$1&"'!"&AS$6:AS$5000,0)

If I put the AS6 and AS$6:AS$5000 in quotes "" it works but won't let me copy down and across

Can anyone advise where I am going wrong?
Any help will be appreciated

 

[maabadi]

Try:

=RANK.EQ(INDIRECT($AN$1&"!AS6"),INDIRECT($AN$1&"!AS$6:AS$5000"),0)

 

[Peter Davison]

That just comes up with a #REF! in cell AS6
Any other thoughts?

 

[maabadi]

What is value at AN1 and AS6?

 

[Peter Davison]

20 and the total of AS6:As5000 is 96202

 

[Toadstool]

Hi Peter,

You'll need to make that first AS6 relative and the range column relative if you want to copy down and across.

Does this work for you?
Note: The first AN3 formula is yours just to verify it worked).

PeterDavison2.xlsx
	AS	AT	AU
5	Data	More Data	Yet More Data
6	1	3	99
7	5	4	2
8	2	2	88
9	11	1	3
10	12	9	99
11	10	8	2
12		7
13
Unit Sales

PeterDavison2.xlsx
	AN	AO	AP
1	Unit Sales
2
3	6
4
5	6	5	1
6	4	4	5
7	5	6	3
8	2	7	4
9	1	1	1
10	3	2	5
11	#N/A	3	#N/A
Sheet1

Cell Formulas
Range	Formula
AN3	AN3	=RANK.EQ('Unit Sales'!AS6,'Unit Sales'!AS$6:AS$5000,0)
AN5:AP11	AN5	=RANK.EQ(INDIRECT("'"&$AN$1&"'!"&CELL("address",AS6)),INDIRECT("'"&$AN$1&"'!"&ADDRESS(6,COLUMN(AS5),2)&":"&ADDRESS(5000,COLUMN(AS5),2)),0)

 

[maabadi]

I think your don't have Data at AN1?

 

[Peter Davison]

This appears to work thank you very much.
I didn't need anything in AN3, wondered why you had the formula in AN3?

 

[Toadstool]

This appears to work thank you very much.
I didn't need anything in AN3, wondered why you had the formula in AN3?

Just because I wanted to check the INDIRECT formulae against your fixed formula.
Here it is without that row.

PeterDavison2.xlsx
	AN	AO	AP	AQ
1	Unit Sales	Ranking	Ranking	Ranking
2		6	5	1
3		4	4	5
4		5	6	3
5		2	7	4
6		1	1	1
7		3	2	5
8		#N/A	3	#N/A
Sheet1

Cell Formulas
Range	Formula
AO2:AQ8	AO2	=RANK.EQ(INDIRECT("'"&$AN$1&"'!"&CELL("address",AS6)),INDIRECT("'"&$AN$1&"'!"&ADDRESS(6,COLUMN(AS5),2)&":"&ADDRESS(5000,COLUMN(AS5),2)),0)

 

[Peter Davison]

You've been most helpful and thankyou for such a prompt response really appreciate your time