Consolidate data from multiple tabs using a dynamic formula

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.