+ Reply to Thread
Results 1 to 2 of 2

Clicking Cell Link Changes Cell on Another Sheet

  1. #1
    Registered User
    Join Date
    09-18-2003
    Location
    None
    Posts
    1

    Clicking Cell Link Changes Cell on Another Sheet

    Okay this is a complicated excel question (i think)

    So I have a bunch of data appearing on a page called "Data" that contains data on different Companies in a certain market place. It has data like market size, earnings, growth, etc.

    So anyway, this data is listed vertically (that is to say, the company names appear across the top row and the fields (such as market size, earnings, growth) appear down each column)

    The very top row on "Data" contains the Company's ticker (stock symbol). Now what I would like to do is be able to click a ticker on that page (maybe through a hyperlink?) and have that ticker appear in cell "A1" of a sheet called "Home". Does that make sense? Basically suppose I clicked on cell F1 on the "Data" page, which contained the ticker IBM. I want the ticker "IBM" to then appear in Cell "A1" of a sheet called "Home." Now I could go back to the "Data" page and click cell F2 (which contains the ticker MSFT, for example) and then cell "A1" in sheet called "Data" would change its value to "MSFT".

    Is this possible to have an entire row (Call it row 1) in one sheet (call it Sheet A) linked to cell A1 of another sheet (call it Sheet B), whereby clicking on any cell in (Row 1) of (Sheet A) will make the value of cell A1 in Sheet B become the value you clicked on?

    I probably overexplained this, but I dont know how to easily explain this situation.

    I know i could use drop down boxes where you choose the Company name from and then use offset and match, but id like to make it clickable just because the drop down would be so large

  2. #2
    Dave Peterson
    Guest

    Re: Clicking Cell Link Changes Cell on Another Sheet

    There's nothing you can tie a click on a cell to.

    But you could do it by selecting the cell--either by clicking or using the
    arrows.

    rightclick on the data worksheet tab
    select view code
    paste this in:

    Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("f:f")) Is Nothing Then Exit Sub

    On Error GoTo errHandler:
    Application.EnableEvents = False
    Me.Parent.Worksheets("home").Range("a1").Value = Target.Value

    errHandler:
    Application.EnableEvents = True

    End Sub

    When you select a cell in column F, it copies the value to A1 of Home.

    ==========

    For the second question, the code looks remarkably similar--only we check just
    row #1 (instead of column F).

    Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("1:1")) Is Nothing Then Exit Sub

    On Error GoTo errHandler:
    Application.EnableEvents = False
    Me.Parent.Worksheets("sheet B").Range("a1").Value = Target.Value

    errHandler:
    Application.EnableEvents = True

    End Sub

    (This code would go behind "Sheet A")

    If you're new to macros, you may want to read David McRitchie's intro at:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    You can read more about these kinds of events at:
    Chip Pearson's site:
    http://www.cpearson.com/excel/events.htm

    David McRitchie's site:
    http://www.mvps.org/dmcritchie/excel/event.htm

    ============
    Personally, I think I'd find this a little difficult to navigate.

    Have you thought about using multiple dropdowns--the first would be used to
    indicate the first character, then the second would show all the symbols that
    start with that symbol.

    Choose M in dropdown #1
    and you'd see all the M's in the second dropdown.

    If you want to see how to try that, visit Debra Dalgleish's site:
    http://contextures.com/xlDataVal02.html

    nshah wrote:
    >
    > Okay this is a complicated excel question (i think)
    >
    > So I have a bunch of data appearing on a page called "Data" that
    > contains data on different Companies in a certain market place. It has
    > data like market size, earnings, growth, etc.
    >
    > So anyway, this data is listed vertically (that is to say, the company
    > names appear across the top row and the fields (such as market size,
    > earnings, growth) appear down each column)
    >
    > The very top row on "Data" contains the Company's ticker (stock
    > symbol). Now what I would like to do is be able to click a ticker on
    > that page (maybe through a hyperlink?) and have that ticker appear in
    > cell "A1" of a sheet called "Home". Does that make sense? Basically
    > suppose I clicked on cell F1 on the "Data" page, which contained the
    > ticker IBM. I want the ticker "IBM" to then appear in Cell "A1" of a
    > sheet called "Home." Now I could go back to the "Data" page and click
    > cell F2 (which contains the ticker MSFT, for example) and then cell
    > "A1" in sheet called "Data" would change its value to "MSFT".
    >
    > Is this possible to have an entire row (Call it row 1) in one sheet
    > (call it Sheet A) linked to cell A1 of another sheet (call it Sheet B),
    > whereby clicking on any cell in (Row 1) of (Sheet A) will make the value
    > of cell A1 in Sheet B become the value you clicked on?
    >
    > I probably overexplained this, but I dont know how to easily explain
    > this situation.
    >
    > I know i could use drop down boxes where you choose the Company name
    > from and then use offset and match, but id like to make it clickable
    > just because the drop down would be so large
    >
    > --
    > nshah
    > ------------------------------------------------------------------------
    > nshah's Profile: http://www.excelforum.com/member.php...nfo&userid=875
    > View this thread: http://www.excelforum.com/showthread...hreadid=400613


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1