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:AG80 and create a new table with 3 columns.
my initial approach was using this formula:
=TEXTJOIN("|"; TRUE;
INDIRECT("'" & TEXT(DAY(INDIRECT("$A$1")) + ROW(B1) - ROW($B$1); "00") & TEXT(MONTH(INDIRECT("$A$1")) + COLUMN(B1) - COLUMN($B$1); "00") & TEXT(RIGHT(YEAR(INDIRECT("$A$1")) + ROW(B1) - ROW($B$1); 2); "00") & "'!C" & 30 + ROW(B1) - ROW($B$1));
INDIRECT("'" & TEXT(DAY(INDIRECT("$A$1")) + ROW(B1) - ROW($B$1); "00") & TEXT(MONTH(INDIRECT("$A$1")) + COLUMN(B1) - COLUMN($B$1); "00") & TEXT(RIGHT(YEAR(INDIRECT("$A$1")) + ROW(B1) - ROW($B$1); 2); "00") & "'!AB" & 30 + ROW(B1) - ROW($B$1));
INDIRECT("'" & TEXT(DAY(INDIRECT("$A$1")) + ROW(B1) - ROW($B$1); "00") & TEXT(MONTH(INDIRECT("$A$1")) + COLUMN(B1) - COLUMN($B$1); "00") & TEXT(RIGHT(YEAR(INDIRECT("$A$1")) + ROW(B1) - ROW($B$1); 2); "00") & "'!AG" & 30 + ROW(B1) - ROW($B$1))
)
As you can see, on A1 i add a date with format dd/mm/aa and the formula gets on the tab with same date the data from each row and merge it using TEXTJOIN. this works great when I drag the formula horizontally... but if i have another date on A2 and drag the formula down, it does not work; if I remove $ it stops working horizontally.
I appreciate any input.
I'm not clear on exactly what you need but see many improvements to the formula are possible. First I recommend using LET instead of repeating and I think you can make use of SEQUENCE instead of dragging. For example:
=LET(
refa,"'"&TEXT(DAY($A$1)+SEQUENCE(5),"00")&TEXT(MONTH($A$1)+SEQUENCE(,5),"00")&TEXT(RIGHT(YEAR ($A$1)+SEQUENCE(5),2),"00")&"'!",
refb, 30+SEQUENCE(5),
refa&"C"&refb)produces an array of references that you want for column C. However to then add the INDIRECT you will probably need a LAMBDA function to query each cell so something like:
=LET(
refa,"'"&TEXT(DAY($A$1)+SEQUENCE(5),"00")&TEXT(MONTH($A$1)+SEQUENCE(,5),"00")&TEXT(RIGHT(YEAR ($A$1)+SEQUENCE(5),2),"00")&"'!",
refb, 30+SEQUENCE(5),
MAP( refa&"XX"&refb, LAMBDA( q,
TEXTJOIN("|", TRUE, INDIRECT(SUBSTITUTE(q,"X", "C")), INDIRECT(SUBSTITUTE(q,"AB", "C")), INDIRECT(SUBSTITUTE(q, "AG ", "C"))))))Unfortunately I couldn't test it and because I'm not a fan of using INDIRECT like this I honestly don't want to and would recommend you provide a sample sheet and more details to see if we might find a better way to accomplish this task.
It's difficult to draw up a formula without a sample workbook. If you're using Excel 365 then INDIRECT can be avoided in favor of a function like VSTACK that's capable of handling 3D references.
- =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)))
