Get worksheet name from a specific cell, reference a cell from that worksheet and then drag it down to get references from other cells

[frugihoyi]

Let's say I'm in worksheet "unicorn." Inside this worksheet I want to write a function to get the value of a cell from a different worksheet, named "sasquatch." The special thing here is that the worksheet name (sasquatch) should be copied from another cell in this "unicorn" sheet we are in. The solution I've found is =INDIRECT(F$3&"!E11"), which takes the word "sasquatch" from cell F3 and then copies the value from the E11 cell in the sasquatch worksheet.

The problem is that I want to select this cell and drag it down so that !E11 increases on each row, but since it has quotes around it, it doesn't increase. What to do?

 

[crazydragon84]

CC?

Anyways, =Indirect(F$3&"!E" & row()). does that work?

 

[frugihoyi]

CC?

Anyways, =Indirect(F$3&"!E" & row()). does that work?

Good idea! Thank you!