I have a similar situation, however the tab names are not sequential or numerical. In fact i extracted them in array format using "=(GET.WORKBOOK(1))&T(NOW())", and similarly to the above query I'd like to use the indirect formula to get cell C2 of each sheet.

I'm using this formula "=LET(A, "'" &A14#& "'!"&"C2", B,BYROW(A,LAMBDA(X,INDIRECT(X))), B)" However I'm getting a value! error. Strangely enough when I workout "A" named range in a separate cell and then refer to that cell using "#" since it would be a dynamic array, it works. However it is not very neat. Any help on this?