Forum Discussion
Consolidate data from multiple tabs using a dynamic formula
I have an online file (not using the desktop version) with multiple tabs named ddmmaa. future tabs will be added with random dates. on each tab i want to get the ranges C30:C80, AB30:AB80 and AG30:AG...
=TEXTJOIN("|", TRUE,
INDIRECT("'" & TEXT(DAY($A1) + ROW($B$1) - ROW($B$1), "00") & TEXT(MONTH($A1), "00") & TEXT(RIGHT(YEAR($A1) + ROW($B$1) - ROW($B$1), 2), "00") & "'!C" & 30 + COLUMN(A1) - ROW($B$1)),
INDIRECT("'" & TEXT(DAY($A1) + ROW($B$1) - ROW($B$1), "00") & TEXT(MONTH($A1), "00") & TEXT(RIGHT(YEAR($A1) + ROW($B$1) - ROW($B$1), 2), "00") & "'!AB" & 30 + COLUMN(A1) - ROW($B$1)),
INDIRECT("'" & TEXT(DAY($A1) + ROW($B$1) - ROW($B$1), "00") & TEXT(MONTH($A1), "00") & TEXT(RIGHT(YEAR($A1) + ROW($B$1) - ROW($B$1), 2), "00") & "'!AG" & 30 + COLUMN(A1) - ROW($B$1))
)
This formula works in my Excel for the web sheet.
If you enter the sheet name in cell A1 you can use this simpler formula. In A1 you only have to enter e.g. "140170" instead of "14/01/70".
=TEXTJOIN("|",TRUE,INDIRECT(ADDRESS(COLUMN(AD1),3,,,$A1)),INDIRECT(ADDRESS(COLUMN(AD1),28,,,$A1)),INDIRECT(ADDRESS(COLUMN(AD1),33,,,$A1)))