Results 1 to 6 of 6

Thread: Return cell reference of another cell thats highlighted

  1. #1

    Return cell reference of another cell thats highlighted



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    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.


  3. #3

    Further help on this please

    Quote Originally Posted by NBVC View Post
    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

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    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


  5. #5

    Getting the result to another worksheet

    Quote Originally Posted by NBVC View Post
    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.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •