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...
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.