I'm looking for a single arrayformula to convert a specified range, such as B2:D4
, into a list of addresses (not values!) contained in that range, in this case B2,B3,B4,C2,C3,C4,D2,D3,D4
.
Attempts so far
Google didn't come up with useful results.
I have tried to count through the array, and are able to extract two list of numbers corresponding to the row and column numbers, but have failed to come up with a combined formula.
Column calc:
=ArrayFormula(
ROW(INDIRECT(SUBSTITUTE(ADDRESS(ROW(),ROW())&":"&ADDRESS(ROW()+
COLUMNS(B2:D4)
-1,ROW()),"$","")))-ROW()+
CELL("col", B2:D4)
)
Row calc:
=ArrayFormula(
ROW(INDIRECT(SUBSTITUTE(ADDRESS(ROW(),ROW())&":"&ADDRESS(ROW()+
ROWS(B2:D4)
-1,ROW()),"$","")))-ROW()+
CELL("row", B2:D4)
)
Note on the above formulas, the ROWS()/COLUMNS() function is used to get the range size, and the CELL() is used to get the range start.