Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 30th 05, 10:31 PM
nshah
 
Posts: n/a
Default 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


--
nshah
------------------------------------------------------------------------
nshah's Profile: http://www.excelforum.com/member.php...nfo&userid=875
View this thread: http://www.excelforum.com/showthread...hreadid=400613


  #2   Report Post  
Old August 31st 05, 01:50 AM
Dave Peterson
 
Posts: n/a
Default

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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I link cells, sheet to sheet, to recognize row deletions? LeeC Excel Discussion (Misc queries) 30 November 6th 09 11:26 PM
indirect function to reference cell on different sheet Dolemite Excel Worksheet Functions 2 August 19th 05 05:25 PM
Identify repeated cell entries in multiple sheet workbook as you . Trigger Excel Discussion (Misc queries) 0 August 17th 05 01:57 AM
Excell:Move from any Cell Sheet 1 to any cell Sheet 2 etc. eldo Excel Worksheet Functions 1 August 16th 05 09:17 AM
Linking a dinamic cell value from one sheet to another KT Excel Discussion (Misc queries) 1 July 17th 05 12:53 AM


All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017