Indirect function in Excel - dragging down the column

Question

I'm working on a workbook that will have a master list of tasks for a group, and then each person within that group would have a worksheet with their specific information. I'd like to make a few columns of information from those sub-worksheets populate back into the master list.

I think what I want to do is an indirect reference to the worksheet tab names. The following formula works for me:

(B2 in this is a column that references the worksheet name, and E2 is the cell from that worksheet that i want returned)

=INDIRECT(B2&"!E2")

so, for example, B2 might say "Sarah", in which case the formula returns the value from cell E2 on worksheet "Sarah", which is exactly what I'm wanting to happen.

The problem I'm running into is that when I drag this formula down to the next row, my cell reference to E2 stays the same rather than progressing to E3 like I would like it to.

I have 3000 rows that I'd like to do this for, so manually re-typing the row # isn't sounding like fun. Is there any way to make that automatically progress?

I'm pretty sure my issues are in those " marks, but I'm drawing a blank. Can anyone help?

Answer 1

I did some more digging, and came up with the following solution:

=INDIRECT($B2&"!"&CELL("address",E2))

it seems to work. It might not be the most elegant of solutions, but I'll take it!

Answer 2

Consider something like:

=INDIRECT($B$2 & "!E" & ROWS($1:2))

We want to "freeze" he B2 and propagate the cell reference.

Answer 3

Maybe it didn't work because the sheet name contains one or more spatials. This would then work with: =INDIRECT("'"&B2&"'!E2")