|29 Nov 2010||#1|
Excel experts, is this an impossible task?
Okay, this is a bit strange and I can't think of any way this could work, but maybe the internet can surprise me...
I have data (text, not numbers) in Sheet1. I concatenate it into Sheet2 as part of a hyperlink. How can I make it so that the whole workbook is only Sheet2 with working hyperlinks? (Yes, I have a practical application of this)
Sheet1!A1 says: page
Sheet2!A1 says: =HYPERLINK(CONCATENATE("http://www.com/",Sheet1!A1),Sheet1!A1)
Sheet2!A1 actually reads: "page", and it has a hyperlink to "http://www.com/page"
Now, how can I condense the workbook to ONLY Sheet2, maintaining not only the text referenced from Sheet1, but the working hyperlinks as well?
So the workbook will have ONE page with cell A1 reading "page" and hyperlinking to "http://www.com/page"
Does that make sense?
Is it even possible?
|My System Specs|
|30 Nov 2010||#2|
Solved on excelforum.com here.
Here's the solution:
Public Sub Example()
Dim rngC As Range
Dim lngEndPos As Long
Dim strURL As String, strFriendly As String
For Each rngC In Sheets("Sheet2").Cells.SpecialCells(xlCellTypeFormulas).Cells
If UCase(Left(.Formula, 11)) = "=HYPERLINK(" Then
lngEndPos = InStrRev(.Formula, ",")
strURL = .Parent.Evaluate(Mid(.Formula, 12, lngEndPos - 12))
strFriendly = .Text
.Hyperlinks.Add .Cells(1), strURL, , , strFriendly
Thanks to DonkeyOte over there!
|My System Specs|
|Similar help and support threads for2: Excel experts, is this an impossible task?|
|Need help creating a task scheduler task to move files over a network.||General Discussion|
|Task scheduler unable to load task status/active tasks at startup.||Performance & Maintenance|
|Mutliple Outlook 2003 instance in task tray and task manager||Microsoft Office|
|Task Scheduler failing to start task with merged network locations||General Discussion|
|Formatting Excel 2010, Excel 2003||Microsoft Office|
|Can't open 2003 Excel Files in Excel 2007?||Microsoft Office|
|Excel 2003 - Multiple Excel Open||Microsoft Office|
Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd
All times are GMT -5. The time now is 07:17 AM.