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!


EDIT: I got this code from someone else one here:

=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

 

[DILipandey]

Hi Tripflex,

suppose you have typed some text in the sheet "a" of workbook 4, then use
following formula:-

=CELL("address",a!A1)
which will shows result as following
[Book4]a!$A$1

I believe this is what you require, it is showing up, the workbook name,
then the tab (sheet) name and then the cell in which you have typed in or
given the reference.

Please click on "Yes", if it helps. Thank.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India