Max function by using row number and column number of cell reference

The following formula is static.

=MAX(A5:C10)

I need dynamic formula because data range is variable.

I want to use row number and column number of cell reference.

I have tried the following formula but the following formula doesnt work.

=MAX(ADDRESS(5,1):ADDRESS(10,3))

Any solution?

Formula like =ADDRESS(5,1) will return a cell address as text, not as an actual cell reference.

To turn it into an actual cell reference you need to wrap the formula with indirect - =INDIRECT(ADDRESS(5,1)).

As you no doubt know you reference a range of cells using : between the start and end cell reference, but =ADDRESS(5,1):ADDRESS(10,3) won't work as the ADDRESS function returns text, while the : part expects a cell reference.

So we can either use INDIRECT on both ADDRESS functions: =INDIRECT(ADDRESS(5,1)):INDIRECT(ADDRESS(10,3))

or create a text string representing the full range and use INDIRECT on that =INDIRECT(ADDRESS(5,1) & ":" & ADDRESS(10,3)) - here : is being treated as text so needs to be joined with the rest using & ":" &

Then just wrap the whole lot with MAX to find the maximum value: =MAX(INDIRECT(ADDRESS(5,1) & ":" & ADDRESS(10,3)))

The address row and column numbers can be swapped out for cell references if you're calculating those elsewhere - something like =MAX(INDIRECT(ADDRESS(F1,G1) & ":" & ADDRESS(F2,G2)))