Search code examples
google-sheets

How to use Google Spreadsheet ADDRESS function to create a range

I'm trying yo create a formula in google spreadsheet that calculates the maximum value for all the cells above the current cell, in a way I can copy it to any cell and still workss. I tried using the ADDRESS function like this:

=MAX(ADDRESS(1;COLUMN()):ADDRESS(ROW()-1;COLUMN()))

But I get an parse error. I tried many variants of this code, but always get an error. Apparently, the ADDRESS function is not allowed as part of a range.

Is there any way to create a reference to a range based on the current cell position?

Solution

  • Was stuck on something very similar. Something like this should work.

    =MAX(INDIRECT(ADDRESS(1, COLUMN()) & ":" & ADDRESS(ROW()-1, COLUMN()))