Formula required:

[JohnGreig]

Example: for two cells A2 and C2 I am looking for a formula in A2 that will copy the final entry in C2 when the information from other parts of the spreadsheet are moved more than once into C2.
Such as cell entries elsewhere "abc", "def", "xyz" that can be moved in and out of cell C2 until satisfied with the final entry. It is the final entry in C2 that I want to see in A2.

 

[mrshl9898]

=C2

 

[JohnGreig]

=C2 is fine if an entry is typed into that cell, but I want to actually MOVE a random cell into C2 and have that reflected in A2.
The sheet I am working on may have several random cells MOVED into C2 and I want the last entry to show up in A2.

 

[mrshl9898]

So you're dragging cells into it? Away from the pc atm but I assume =$C$2 won't work (worth a test).

Try =ADDRESS(2,3)

 

[JohnGreig]

=$C$2 does not work and trying your address suggestion just returns "$C$2"

 

[mrshl9898]

Oh ok. Apologies, I use it rarely. I would have tested myself if I could.

=INDIRECT(ADDRESS(2,3)) should get it done

Also perhaps (untested, maybe the same challenge)

=INDEX($C$2,1,1)

 

[mrshl9898]

Also, VBA sheet level macro

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$2" Then
        Range("A2") = Range("C2")
    End If
End Sub

 

[JohnGreig]

Brilliant thank you mrshl9898, your "INDIRECT" formula worked perfectly, much appreciated.
John

 

[Peter_SSs]

"INDIRECT" formula worked perfectly,

That may be fine for you but since INDIRECT is a volatile function I would consider this alternative

=INDEX(C:C,2)

 

[JohnGreig]

To take this one step further, I actually want to draw the information from a 2nd sheet in the workbook where "A2" on Sheet1 holds either the =INDIRECT or =INDEX formula and I want to reflect what has been moved into "C2" on Sheet2............the result I am getting on Sheet1 A2 is "#NAME? Any help on how to refine the formula would be appreciated.