Hi Sachin,
If you hyperlink to a cell within Excel you can use the Back button (alt+ArrowLT)
just like in your browser. In fact you can install Cyan (/aqua) colored back and
forward buttons on your toolbar (tools, customize, Web (left), Button (right side)
-- drag each button to the toolbar.
I think you are creating a can of worms for yourself, by asking a cell to
show a value with no visible connection.
When you do a web search and come with only 6 hits, you know it's
not a very popular thing to do. But it looks like Frank Kabel already
answered your question. Which means you've posted in more than
one place..
http://www.excelforum.com/t204672-s
Anyway here is my take on your question, install the
following two event macros. One is for the hyperlink that you
asked for and the other is a doubleclick event macro, which
I think would work better for you if A1 is the only place to be updated.
http://www.mvps.org/dmcritchie/excel/event.htm
The following will do what you ask, if and only if, you assign
each hyperlink (object hyperlink) individually -- without using
the fill handle.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
' MsgBox "destination: target.subaddress " & Target.SubAddress
' MsgBox "Source: Target.Range.Address " & Target.Range.Address
' MsgBox "Source: Target.Range.Value " & Target.Range(1, 1).Value
Range(Target.SubAddress) = Target.Range(1, 1).Value
End Sub
'the Double-click event macro below would update cell A1 when you
' double-click form anywhere on the sheet.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Range("A1").Value = Target.Text
On Error Resume Next
Range("A1").AddComment
On Error GoTo 0
Range("A1").Comment.Visible = False
Range("A1").Comment.Text Text:="Value from: " & Target.Address(0, 0) _
& Chr(10) & Format(Now, "ddmmmyyyy hh:mm:ss")
Range("a1").Select
End Sub
to install the two event macros: right click on the sheet tab,
then plop the above code in
Summary of original request:
A1 (wants the value of the cell that last hyperlinked to here from E1:E100)
E42 $1350 hyperliknk to A1
E1:E100 .... all hypelink to A1
The problem is if you hyperlink E1:E100 as a group to A1, or if you
use the fill-handle to to link to $A$1 you get a range of E1:E100
or whatever you did your hyperlink from
--
When you said you had not received
an answer that does not appear to be the case. Frank answered you
in another thread and you thanked him. The reply that I answered
appears to be a question so I answered it after all you did say that
you did not get an answer.. .Please stick to your original
newsgroup and thread when you ask a question. I don't like
replying to anonymous posters, ,you can get a hotmail address
and use the hotmail facilities to protect it from spam and not
compromise your current email address. If it weren't for all the
spam, I would consider anonymous postings as very rude in
this type of newsgroup because it tends to imply give me an
answer and I don't wish to participate.
If you use a valid email address (i.e. hotmail address) then you can
look in the archives a few years later and find your conversations.
http://www.mvps.org/dmcritchie/excel/xlnews.htm
Suggest you not chose a hotmail address similar to your main
email address because spammers take
an email address and try variations with every different domain name.
So if they found you as (e-mail address removed) they would
send also to (e-mail address removed) and @msn.com and @hotmail.com
then again by dropping the number.
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
Sachin said:
correction
If user clicks on E42 (Value of which is $1350), then user will be led to A1 & value of A1 shall be equal to that of E42
---
Had received the reply (lol), not the solution!
The problem though is that if SAY:
All cells in E1:E100 have hyperlink and the reference for all is A1, then in that case is it possible that A1 bears the value of the
cell from which the link originated
Ex:
If user clicks on E31 (Value of which is $120), then user will be led to A1 & value of A1 shall be equal to that of E31
If user clicks on E42 (Value of which is $1350), then user will be led to A1 & value of A1 shall be equal to that of E31
