Solved

Excel: How to Copy Hyperlink Cell To Another Cell

Posted on 2011-09-13
6
678 Views
Last Modified: 2012-08-14
Hello,
I have a simple equation that returns a value in a cell so that when you click on it, it is a hyperlink. This works. = IF(AC7>0, (HYPERLINK((VLOOKUP(AC7, $AM$7:$AN$3000, 2, FALSE)),AC7)), ""). It returns a "friendly location" which is a 4 digit number and a link location (on the web) when you click on it.

How do I copy and paste just the VALUE to another cell so that again, when you click on that copied value, the hyperlink works. Right now, the value is copied but it is no longer a hyperlink. I can't have the equation in the new cell because of other reasons.

Thanks,
Dennis
0
Comment
Question by:u002dag
  • 2
  • 2
  • 2
6 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36531260
Your formula is trying to find a cell in the range specified for the value (same as in AC7). Note that the lookup range is fixed but AC7 is not an absolute reference, so when you copy/paste the formula into another cell the reference going to chnage(somethin else in place of AC7). Please make sure that the value of the new reference does exist in the range. Then the hyperlink should work. You can attach a sample sheet, so that I can get a better idea of your problem.
0
 
LVL 5

Expert Comment

by:slycoder
ID: 36531360
I would insert a column next to your current formula that does the VLOOKUP of the hyperlink

=VLOOKUP(AC7, $AM$7:$AN$3000,2,FALSE)

that would be your list.

0
 
LVL 5

Expert Comment

by:slycoder
ID: 36531377
To make it "hot", naturally it would be

=HYPERLINK(VLOOKUP(AC7, $AM$7:$AN$3000,2,FALSE),VLOOKUP(AC7, $AM$7:$AN$3000,2,FALSE))


Hope this helps.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:u002dag
ID: 36531397
Hi SantaBaby,
Thanks for the quick response. I attached a sample sheet as you requested.

Goal:      
Need 4 Digit Value in Column B, copied to column A with the hyperlink of column B      
So it's no longer an equation that's being copied over.      

I found this on the web, is it of any help?

But this code fails if source cell doesn't contains hyperlinks

Sub copyHyperlink()
r = Selection.Rows.Count
For i = 1 To r
ActiveSheet.Hyperlinks.Add Anchor:=Selection.Cells(i, 2), _
Address:=Selection.Cells(i, 1).Hyperlinks(1).Address
Next
End Sub


EOS-Assistant-experts-exchange-v.xls
0
 
LVL 10

Accepted Solution

by:
SANTABABY earned 500 total points
ID: 36531526
Could you please try to change your formula?
Add a $ sign befor ecah reference C column in your formula.
Example: Change the formula in B8 from:

= IF(C8>0, (HYPERLINK((VLOOKUP(C8, $D$7:$E$3000, 2, FALSE)),C8)), "")

to

= IF($C8>0, (HYPERLINK((VLOOKUP($C8, $D$7:$E$3000, 2, FALSE)),$C8)), "")

Once you complete the change, copy B8 and apply it to other cells in column B to change all formulae in column B.

The the formua will refer to C coulmn for lookup, no matter where you copy it to (as long the destination is in the same row).

You can try your copy at this point.

Please let me know if this helps.

0
 

Author Closing Comment

by:u002dag
ID: 36531780
Thanks.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now