B3 
=INDIRECT("'"&B$2&"'!"&Char(63+ROW())&"2")
Indirect will take the cells:

B$2 will take the value in column B cell 2 (since there is no $ sign for the B, it will expand to C, D etc by dragging it to the right).

Char(66) is letter B.

To get that letter I took 63 + ROW().

In B3 the formula row will give the result 3.

So Char(63 + 3) = 66 => is letter B.

I take this approach so you could drag it to the right.

The & sign pulls the data together.

Did I explained well enough?