Find maximum across multiple sheets

[Klips]

Hi all,

I'm having alot of problems trying to summerize some data that I have
on multiple sheets. I have set up a sheet called Cases that has an
index of all the data sheets (i.e. 1, 2, ...) and would like to
iterate through this list and find the maximum values and report them
on my summary sheet. I would also like to report the sheet that has
that maximum value. To complicate things a bit more, the ranges (i.e.
1!Data, 2!Data, 3!Data, ...) are defined as dynamic ranges.

The structure and behaviour would look like something like this:

SHEET: Summary
item id | max value | max sheet
1 | 20 | 1
2 | 14 | 2
3 | 33 | 1

SHEET: 1 RANGE: 1!Data
item id | value
1 | 20
2 | 5
3 | 33

SHEET: 2 RANGE: 2!Data
item id | value
1 | 12
2 | 14

SHEET: Cases RANGE: Cases!Data
sheet | description
1 | case 1
2 | case 2

I'm lost as how this could be accomplished. Any help is much
appreciated.

Thanks

 

[Lori]

With A1:C4 as the summary table, try in B2:C2 (ctrl+shift+entered):

=MAX(DMAX(INDIRECT("'"&Cases!A2:A3&"'!Data"),2,A1:A2))
=MATCH(B2,DMAX(INDIRECT("'"&Cases!A2:A3&"'!Data"),2,A1:A2),)

Then select A2:C4, Data > Table with Column Input: A2. Giving B3:C4

=TABLE(,A2)