Excel, indirect reference to range of sheets

Question

Trying to use INDIRECT to reference a range of sheets, and a range of cells in those sheets, looking for the MAX. Neither of these work:

=MAX(INDIRECT("1:"&last_sheet&"!G"&ROW()&":K"&ROW()))
=MAX(INDIRECT("1:6!G"&ROW()&":K"&ROW()))

However, this does (but only looks at one sheet: 1):

=MAX(INDIRECT("1!G"&ROW()&":K"&ROW()))

And so does this (but doesn't use INDIRECT):

=MAX('1:6'!G6:K6)

It seems to me that INDIRECT simply cannot be used with a range of sheets. Please tell me I'm wrong and why.

score 0 · Answer 1 · answered Jul 16 '15 at 17:41

0

It looks like you're probably correct. The following workaround is ugly, but it works. =MAX(MAX(INDIRECT("'1'!B1:C2")),MAX(INDIRECT("'2'!B1:C2")),MAX(INDIRECT("'3'!B1:C2")))

answered Jul 16 '15 at 17:41

nwhaught

1,562
1
15
36

Thank you. However, the whole reason for using INDIRECT is that the number of sheets to look at will grow, and I would like to look at a range whose end is specified by "last_sheet" and will change daily. – rainrunner Jul 16 '15 at 22:26
Would a custom built function be of any use? Doing this in VBA would not be difficult. – nwhaught Jul 17 '15 at 13:06

score 0 · Answer 2 · answered Jul 17 '15 at 16:13

You can paste the below function into the VBA editor, and it will produce the results you're looking for. It returns the max of whatever range you specify, across all of the sheets in the workbook. Use it just like a regular function, ie =MultiMax(A1). It also accepts an INDIRECT as a parameter.

Function MultiMax(r As Range) As Long
    Dim s As Worksheet
    Dim a() As Long
    Dim m As Long
    ReDim a(0 To 0)
    For Each s In ThisWorkbook.Sheets
        m = Application.WorksheetFunction.Max(s.Range(r.Address).Value)
        ReDim Preserve a(0 To UBound(a) + 1)
        a(UBound(a)) = m
    Next

    Dim y As Integer
    Dim m1 As Long
    For y = 0 To UBound(a)
    If a(y) > m1 Then
        m1 = a(y)
    End If
Next
    MultiMax = m1
End Function

score 0 · Answer 3 · edited Aug 11 '16 at 15:51

Similar to the above solution, you could also try an array formula. However, this will require you to do a MAX function on each sheet (preferably in the same cell on each sheet). For example, on sheet '1', you have MAX(B2:C2) in cell D1, and then the same on sheet '2', sheet '3', etc. Then on your summary sheet, use this array formula:

=MAX(N(INDIRECT(ADDRESS(1,4,,,ROW(INDIRECT("A1:A"&last_sheet))))))

Then be sure to hit Ctrl+Shift+Enter to enter it as an array formula.

This assumes "last_sheet" is some integer value like 6 for example, then makes a range string of it ("A1:A6"), passes this to INDIRECT which passes it to ROW() giving you an array from 1:6. This array is used as the list of sheet names for ADDRESS which creates an array of references at cell D1 on each of the six sheets. The array is passed to INDIRECT which returns #VALUE! errors until you pass the array of errors to N(). Finally, max returns the largest value in the array. You can use "Evaluate Formula" to see how it breaks down step by step, but hopefully this is a good starting point for you!

Excel, indirect reference to range of sheets

3 Answers3