Is there a way to get what cell the text is in?

So say for instance if i put this into one cell

=Sheet1!

I want after =Sheet1! to be whatever cell that function is in...so say for
instance i put this in cell C5

It would determine that the function is in cell C5 and it will then make it

=Sheet1!C5

and so on for =Sheet1!C6 in cell C6. But i'm looking for a way i can do it
like this

=Sheet1!(TheCellThisTextIsIn)

So it basically mimics another sheet but this way i can put the same
function in all of the cells.

Thanks guys!


EDIT: I got this code from someone else one he

=INDIRECT("'Sheet1'!&"ADDRESS(ROW(),COLUMN(),4))

But how do i specify in that code that i want it to get what is in Column B
no matter what column it is put in. Thanks