Reputation: 4283
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
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