CODEWITHSUNDEEP
excelexcel-formula
Raikoug
Raikoug

Reputation: 357

Using a formula with a sheet name specified in another cell

I searched a lot, and now I will explain this as best I can:

I have a lot of sheets. In the first one (but only for example) I will have in A1 a list which contains the list of exact sheet names.

And in A2 I want to use the sheet named in A1 like this:

='A1'!C5

That for me would mean: Cell A2 is equal to the cell C5 contained in the sheet named in A1. Can this be done? Thanks so much for help!

(p.s. I would like not to use VBA)

Riccardo

Upvotes: 1

Views: 1707

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

As I mentioned in comments, you can use next formulas.

  1. "Static" version - after dragging formula down or accross, it still refers to C5

    =INDIRECT("'" & $A$1 & "'!C5")

  2. "Draggable" version (you can use it in A2 and then, say, drag to B2 and Excel automatically adjust C5 to D5):

    =INDIRECT("'" & $A$1 & "'!" & CELL("address",C5))

Upvotes: 5

Related Questions