Excel refrencing array with adress and indirect from another work sheet

Question

As part of a formula in Excel I am trying to create a reference to an array which is located on Another work sheet. In order to do so I am trying to combine INDIRECT and ADDRESS in this way:

INDIRECT(ADDRESS(MATCH('current sheet'!E87;'sheet 2'!$C$2:$C$47;0);1;1;1;"sheet 2"))&":"&INDIRECT(ADDRESS(47;1;1;1;"sheet 2"))

I receive the correct addresses when I paste the address formulas on their own, but once i combine them with indirect and try to put them in the formula down below it doesn't work. Does anyone understand what the problem might be?

=INDEX(INDIRECT(ADDRESS(MATCH('current sheet'!E87;'sheet 2'!$C$2:$C$47;0);1;1;1;"sheet 2"))&":"&INDIRECT(ADDRESS(47;1;1;1;"sheet 2"));MATCH('current sheet'!E87;'sheet 2'!$C$2:$C$47;0))

Answer 1

I think you don't need to append "sheet 2" on the second part of your array.
Just try this.
Go to your main sheet pick an epty cell, enter = then click on to your other sheet and draw the array you want to index.
It will look something like this: ='sheet 2'!E4:F12
At the moment your formula makes a reference that looks like this:
'sheet 21!E4:'sheet 2'!F12.
You don't need the second 'sheet 2'! in the array.
Hope this helps.