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

 

[Toadstool]

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

You're welcome.
Thanks for the feedback.