Forum Discussion

Simon Wilson's avatar
Simon Wilson
Copper Contributor
May 29, 2018

Alternative to offset indirect for worksheet references

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

Formulas & Functions
tips and tricks

3 Replies

    • Simon Wilson's avatar
      Simon Wilson
      Copper Contributor
      May 30, 2018

      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

       

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        May 30, 2018
        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?

Resources