Alternative to offset indirect for worksheet references

[Simon Wilson]

Hi,

I can't seem to find something in the help which matches what I'm looking for.  I have a workbook with a worksheet of input parameters, one of which I want to use to select a worksheet with a range of numbers which is then used as an input range in another.  I have a matrix 365*48 which is in say 'Data1' worksheet.  I also have 'Data2' and 'DataX'.  User inputs worksheet name e.g. 'Data2'.  A sheet 'Calculations' has a matrix of formulae which is also 365*48. I have accomplished this with offset indirect like OFFSET(INDIRECT("'"&$A$3&"'!A1"),ROW(B4)-1,COLUMN(B4)-1) where B4 is the B4 cell in Calculations and A3 stores the text name Data2.

Offset indirect is too slow as I have Data Tables in the workbook. I can't seem to think of a way to use index match without also an indirect for the worksheet name 'Data2'.  What's the best way of allowing the source to be pointed to different worksheets, without VBA or slow functions please?

Thanks,

Simon

[#2]

Hi Simon 

You could try INDEX (see attached) with a formula like this

=INDEX(   INDIRECT($A$3&"!$A$1:$AV$365"),  ROW(A1), COLUMN(A1)  )

[#3]

Hi Wyn,

Thanks, wouldn't that still be slow to calculate on large spreadsheet with data tables because of the use of Indirect, Rows and Columns which I understand are volatiles?

Thanks,

Simon

[#4]

Hi Simon,

I would think it would be quicker. I don't believe ROW and COLUMN are volatile plus using INDEX instead of OFFSET should speed things up

Do the different Matrices have to be on different sheets?

Is consolidating them into a single data set using Power Query an option?