CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
haemse
haemse

Reputation: 4283

Shortest way to get the A1 Notation (String) of a Range within a Google Sheet Cell Function

Let's say I got:

=CustomFunction(B5:B38, $A40)

as the cells formula value. However, the range parameters are handed as their values, not as range objects.

What is the shortest way to convert those ranges into their Address string, like "B5:B38", while keeping the range address within the formula as one piece B5:B38 (So it stays dynamic on sheet modifications)?

Upvotes: 1

Views: 1971

Answers (1)

player0
player0

Reputation: 1

B5:B38 would be: 

INDIRECT(ADDRESS(ROW(B5),COLUMN(B1))&":"&ADDRESS(ROW(B38),COLUMN(B1),4))

shorter but not dynamic: 

INDIRECT("B5:B38")

in some cases the first fx could be moded like:

INDIRECT("B5:"&ADDRESS(ROW(B38),COLUMN(B1)))

Upvotes: 1

Related Questions