Excel ranking across multiple sheets using INDIRECT function

[MaxLagace]

Hello, I am having a bit of difficulty and need some help with the following
I have 3 sheets called
Data-2013
Data-2014
Data-2015
These sheets contain weekly performance data for the designated year and the each row holds the same data, i
i.e. row 2 = 'widget sales'
row 5 = 'sproket sales'
etc...

I am creating a dashboard which ranks weekly performance across ‘All Time’ i.e. 3 years
Normally I could type the formula below and get the results

=RANK($H4,’DATA-2013:DATA-2015’!H73:BG73)
​

But I need to use the indirect function because depending on the filters used the reference cells will change (H73:BG73 will become H122:BG122)

However when I use the INDIRECT function to create the reference, it returns an error.
I used the following formula to create the needed value.

="'DATA-2013:DATA-2015'!$H$"&$AO5&":$BG$"&$AO5
​

But when I then try to rank using =RANK(H4,INDIRECT(AW4)) I get a #ref error.


Where have I gone wrong? Is it possible to use INDIRECT to reference data across multiple sheets?
As well as rank I am also trying to get the 'max' value across the 3 years but I am having the same problem.

 

[pgc01]

Hi Max
Welcome to the board

Is it possible to use INDIRECT to reference data across multiple sheets?

Indirect() does not support 3D references.

You can use Indirect() to reference data across sheets if you use a list with the sheet names and generate each range in each sheet.

This is an example with the data you posted.

I'd write in K1:K3

DATA-2013
DATA-2014
DATA-2015

and I'd use for the Rank()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&K1:K3&"'!H"&AO5&":BG"&AO5),$H4))=0,NA(),1+SUMPRODUCT(COUNTIF(INDIRECT("'"&K1:K3&"'!H"&AO5&":BG"&AO5),">"&$H4)))

Please try.

 

[MaxLagace]

Hi Pgc01, this seems to have doe the trick for Rank, thank you very much.

Can you suggest how to modify the formula to dertermine the maximum value in the selection?
unfortunately =max(INDIRECT("'"&K1:K3&"'!H"&AO5&":BG"&AO5) does not seem to work

Cheers

Max

 

[pgc01]

Hi Max

Assuming that there is at least 1 numerical value in each of the sheets, try:

=MAX(INDEX(SUBTOTAL(4,INDIRECT("'"&K1:K3&"'!H"&AO5&":BG"&AO5)),0))

If there may be sheets with no values and you have only negative values then you have to check and exclude those sheets.