Solved

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

Posted on 2011-02-14
7
271 Views
Last Modified: 2012-05-11
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
Comment
Question by:Steve_Brady
  • 3
  • 2
  • 2
7 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34890518
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
 
LVL 42

Expert Comment

by:dlmille
ID: 34890533
Here it is attached.

PS - did you award points on that prior question?

Dave
ShowAinConBselect.xlsm
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 100 total points
ID: 34890601
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Accepted Solution

by:
dlmille earned 400 total points
ID: 34890680
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
 

Author Closing Comment

by:Steve_Brady
ID: 35073071
Just what I needed!  Thanks
0
 

Author Comment

by:Steve_Brady
ID: 35073097
Tried to give rspahitz a few points for the heads up but it won't let me.  ??
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35073174
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question