Return cell reference of another cell thats highlighted

pleased2help

New member
Joined
Jun 27, 2013
Messages
28
Reaction score
0
Points
0
Is there a formula in Excel 2010 to return the cell ref of another highlighted cell ie:

If I move the cursor to cell "C1" and enter to select it

cell "B1" would show "C1" or better still the value in cell "C1"

If I then move the cursor to cell "C2" and enter to select it

cell "B1" would show "C2" or better still the value in cell "C2"

I would only need to do this for a range of cells "C1 to C14"

Many thanks Paul
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
There is no formula to do that... but you can use VBA.

right click on the sheet tab and select View Code.

paste the following code in the editor.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Target.Offset(0, -1) = Target.Value
End Sub

now select an item in C1:C14 range and B1 should change accordingly.
 

pleased2help

New member
Joined
Jun 27, 2013
Messages
28
Reaction score
0
Points
0
Further help on this please

There is no formula to do that... but you can use VBA.

right click on the sheet tab and select View Code.

paste the following code in the editor.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Target.Offset(0, -1) = Target.Value
End Sub

now select an item in C1:C14 range and B1 should change accordingly.

Help please:-
The cell next to the selected cell has the value in ie, if i select c1 then b1 has value, if i select c3 then b3 has the value, I just neet the value of the selected cells to appear in "B1" only . I would this to run all the time while the work book is open for any selection in the range C1:C14
Thanks
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Just simply change to:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Range("B1").Value = Target.Value
End Sub
 

pleased2help

New member
Joined
Jun 27, 2013
Messages
28
Reaction score
0
Points
0
Getting the result to another worksheet

Just simply change to:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Range("B1").Value = Target.Value
End Sub

Thank you for your help, much appreciated. Works great

Re the:-
Range("B1").Value = Target.Value

Can i get the result to another spreadsheet in the same workbook ie if it was just excel the reference for the result would be: " 'LOOKUP '!B1 "
"LOOKUP" BEING THE WORK SHEET B1 THE CELL REF.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Try:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Sheets("Lookup").Range("B1").Value = Target.Value
End Sub
 
Top