• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Display values in Excel based on the location of the current active cell

Hello,

In Excel (2007), what formula or code would enable a given cell to display values from a range based on the location of the current active cell?

For example, suppose column A contains arbitrary values in all cells from row 1 to row 9999 (A1:A9999) and you want to be able to activate or select (by clicking or using arrow keys, etc.) any corresponding cell in column B (in range B1:B9999) and have cell C1 display the value from the adjacent cell in column A.

In other words:

    if cell B1 is active, cell C1 will display the value in A1.
    if cell B2 is active, cell C1 will display the value in A2.
    if cell B3 is active, cell C1 will display the value in A3.
    if cell B4 is active, cell C1 will display the value in A4.
    and so on...

In effect, it would be very similar to how the Name Box, in the upper left corner of Excel, displays the coordinates for the currently active cell, except here, C1 would be displaying the values from the same row in column A.

By the way, if more than one cell in column B is selected, C1 should display the value from the first corresponding column A cell -- again similar to how the Name Box works.

Thanks
0
Steve_Brady
Asked:
Steve_Brady
  • 3
  • 2
  • 2
2 Solutions
 
dlmilleCommented:
Put the following in ThisWorkbook codepage.

Dave
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        rowsel = Target.Row
        Range("C" & rowsel).Value = Range("A" & rowsel).Value
    End If
End Sub

Open in new window

0
 
dlmilleCommented:
Here it is attached.

PS - did you award points on that prior question?

Dave
ShowAinConBselect.xlsm
0
 
rspahitzCommented:
I think this will work for you


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 2 Then
        Range("C2").Value = Cells(Target.Row, 1).Value
    End If
End Sub


(dl, I think that the it's supposed to always be C1 and you put C + row#)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dlmilleCommented:
Good catch - here it is corrected with file...

Dave
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        rowsel = Target.Row
        Range("C1").Value = Range("A" & rowsel).Value
    End If
End Sub

Open in new window

ShowAinConBselect.xlsm
0
 
Steve_BradyAuthor Commented:
Just what I needed!  Thanks
0
 
Steve_BradyAuthor Commented:
Tried to give rspahitz a few points for the heads up but it won't let me.  ??
0
 
rspahitzCommented:
looks like it worked...thanks Steve.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now