excelexcel-indirect

EXCEL INDIRECT both sheet and cell dynamically

In excel 2016 on windows, i am trying to reference dynamically to a sheet and a cell.

For example:

=INDIRECT("'"&$L$2&"'!B33")

Where $L$2 has the sheet name and B33 has a numerical value.

However this does not allow me to have the cell B33 dynamic so i can propagate the formula.

I tried:

=INDIRECT("'"&$L$2&"'!"&B33)

But it throws a #REF!.

Any lead on how to build that formula so that both sheets and cells can be referenced dynamically?

Solution

  • Use INDEX and INDIRECT to refernce the full row, then when it drags to the right we change the column index:

    =INDEX(INDIRECT("'"&$L$2&"'!33:33"),1,COLUMN(B33))

    Now as is is drug across the column will choose which value to return.