Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-14
7
Medium Priority
?
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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
Independent Software Vendors: 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 1600 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

660 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