Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.

Windows 7: Excel experts, is this an impossible task?

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("",Sheet1!A1),Sheet1!A1)
Sheet2!A1 actually reads: "page", and it has a hyperlink to ""

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 ""

Does that make sense?
Is it even possible?

My System SpecsSystem Spec
30 Nov 2010   #2


Solved on 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
With rngC
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
End If
End With
Next rngC
End Sub

Thanks to DonkeyOte over there!
My System SpecsSystem Spec

 Excel experts, is this an impossible task?

Thread Tools

Similar help and support threads
Thread Forum
Task scheduler unable to load task status/active tasks at startup.
My task manager is unable to load task status/active tasks at startup. I get the message: "The selected task "{0}" no longer exists. To see the current tasks, click Refresh." When I refresh I get the same message. I checked and found that under Microsoft the problem was in the defrag...
Performance & Maintenance
Mutliple Outlook 2003 instance in task tray and task manager
Every now and then I look down at the task tray ...and whoa!!!! about a dozen instances of Outlook. See attached What is causing this? And how do I prevent it??
Microsoft Office
Task Scheduler failing to start task with merged network locations
I have two routers I usually connect to (work and home) and each have wireless access. That means there are essentially 4 network locations I have listed. I have merged each respective network location (wired and wireless) into their own singular network location - "Work" and "Home". I also...
General Discussion
Formatting Excel 2010, Excel 2003
Hello, I want to create a table as in the drawing below, where the first cell is table-wide (as it contains the heading common (pertaining?) to all columns and rows under it) but, and this is where I cannot get it together, where those cells are divided into several columns. It's a cinch when...
Microsoft Office
Can't open 2003 Excel Files in Excel 2007?
I'm not sure if it because the files are 2003 or not? When I try to open them an error message appears stating, "...the file you are trying to open is in a different format than specified by the file extension. Verify file is not corrupted and is from a trusted source..." These are my files. The...
Microsoft Office
Excel 2003 - Multiple Excel Open
Hi All, I don't know if I'm posting in the right area but I really need help. Before (In XP and Vista), when I have one excel file open and I go to desktop to open a different excel, it would open in the same excel program (2 files in 1 program) With Windows 7, when I open a different file, it...
Microsoft Office

Our Sites

Site Links

About Us

Find Us

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 21:30.
Twitter Facebook Google+ Seven Forums iOS App Seven Forums Android App