CODEWITHSUNDEEP
excelexcel-indirect
Cy Bu
Cy Bu

Reputation: 1437

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?

Upvotes: 0

Views: 1243

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

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.

Upvotes: 3

Related Questions