Formula required:

JohnGreig

New Member
Joined
Feb 21, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

JohnGreig

New Member
Joined
Feb 21, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
=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

Well-known Member
Joined
Feb 6, 2012
Messages
1,723
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

New Member
Joined
Feb 21, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,723
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)
 
Last edited:
Solution

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,723

ADVERTISEMENT

Also, VBA sheet level macro

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

JohnGreig

New Member
Joined
Feb 21, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Brilliant thank you mrshl9898, your "INDIRECT" formula worked perfectly, much appreciated.
John
 

JohnGreig

New Member
Joined
Feb 21, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,069
Messages
5,628,468
Members
416,319
Latest member
k8o2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top