Getting what cell the text is in

[tripflex]

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!

 

[Luke M]

This will return address of whatever cell you put it in. You could combine it
with the rest of your formula to make:
=INDIRECT("'Sheet1'!&"ADDRESS(ROW(),COLUMN(),4))

Of course, if you simply put
=Sheet1!A1
into a cell, and copy it to another cell, the reference will change using
the same shift in row/column.

 

[tripflex]

What about if i want to specify what Column it is in? Like say i want it to
get information out of B instead of C...can i specify that? Thanks!