Formula to Return a Cell address

[Dazzybeeguy]

Hi, I need a formula in say Cell H2 (Not VBA)

I was hoping a formula could look at the Active cell I am in and then display the text in the Adjacent Cell

For Example

1. In Cell F:5 it has the text Apple
2. The user clicks in cell G5

3. I want cell H2 to display Apple.

Thanks

 

[Vogateer]

You can get close to that with H2:

=OFFSET(INDIRECT(CELL("address")),,-1)

But you need to actually make an edit or hit the F9 key in G5 (using your example) before H2 will update to display F5.

 

[Scott Huish]

That won't just affect based on the active cell, you would need event code in VBA for that.

I think you mean this though:

Book2
	F	G	H
1
2			Grape
3
4
5	Grape
Sheet1

Cell Formulas
Range	Formula
H2	H2	=OFFSET(INDIRECT(CELL("address")),3,-2)