I have multiple sheets (S1, S2, S3...) which are all filled with identically formatted data (data tables). For each row in my main sheet, I identify what cell in these lookup tables represents that entry and store that reference using ADDRESS(MATCH(), MATCH()).

I wish to use that reference to then use HSTACK() and import all of the relevant values from the data tables as a horizontal array.

This works when I enter the cell address manually:

=HSTACK(S1:S2!$O$18)

But when I try to use an indirect reference excel crashes immediately after entering the formula, without any kind of error message:

=HSTACK(S1:S2! INDIRECT(H2)), where H2 is the cell storing $O$18.

This gives an invalid cell reference error:

=HSTACK(INDIRECT("A:B!"&H2)) 

I am open to alternative formulas, but ideally, I would like to store the cell reference in advance to reduce the number of lookups I am doing.

CodePudding user response：

INDIRECT doesn't work with 3D sheet references.

One option would be to list the relevant sheets within a vertical worksheet range somewhere, for example A1:A2, after which you could use:

=CELL("contents",INDIRECT("'"&TRANSPOSE(A1:A2)&"'!"&H2))