CODEWITHSUNDEEP
excelexcel-indirect
rainrunner
rainrunner

Reputation: 118

Excel, indirect reference to range of sheets

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.

Upvotes: 3

Views: 781

Answers (3)

cobo714
cobo714

Reputation: 1

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!

Upvotes: 0

nwhaught
nwhaught

Reputation: 1592

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

Upvotes: 0

nwhaught
nwhaught

Reputation: 1592

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")))

Upvotes: 0

Related Questions