?
Solved

Linking Cells with Hyperlinked Entries?

Posted on 2012-03-29
8
Medium Priority
?
240 Views
Last Modified: 2012-04-02
I have a workbook with multiple worksheets. I just realized that the task of maintaining what I'm building would be a lot easier if I just linked all of the data to a MasterWorksheet and conducted maintenance/editing on that sheet.

However,two columns in the MasterWorksheet contain hyperlinked fields and that hyperlink does not copy over when I link that cell on another sheet.... is there a workaround for this?

If a Marco is the solution, all the other sheets have duplicate data of the MasterWorksheet; the only difference is all the ChildWorksheets have filters applied to the data in a variety of ways.
0
Comment
Question by:-Polak
  • 4
  • 4
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37785309
Can you provide a non-sensitive version of your workbook to work from?

Thanks,

Dave
0
 
LVL 1

Author Comment

by:-Polak
ID: 37787697
Sure, here you go. All links have been set to google.com.
CurriculumEESafe.xlsx
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 2000 total points
ID: 37789381
You don't need a VBA macro, necessarily, unless what we come up with on a formula basis drains computer speed, then we can use a macro to generate the embedded links as you have.

I believe your first step should be to add HYPERLINK formulas to your first sheet, then on the copy over, you'll have to use a hyperlink formula there, as well.

I've done this on the first 5 rows for you to see the example.

The HYPERLINK function is entered as follows, for internet links:

=HYPERLINK("http://www.google.com","Friendly Name - E.g., Business Writing for Results")

So, when you link over on other sheets you'll need to just copy/paste the HYPERLINK formula over, otherwise, you can add another column to then reference the base link with the hyperlink formula.  In either case, linking to a  hyperlink doesn't make a new hyperlink.  You either have to copy/paste the link from the initial page (embedded) or the formula should be copied over or built linking to a REFERENCE cell - E.g., Hyperlink(reference!A2,"Friendly Name") or Hyperlink(Reference!A2,Reference!A3).

I've created those helper columns in the main sheet and report sheet, for the first 5 rows and used the HYPERLINK formula to generate the results you desire.

See attached,

Dave
CurriculumEESafe.xlsx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:-Polak
ID: 37796527
I don't hate this solution; but, I'm a little confused by what you meant by:
"unless what we come up with on a formula basis drains computer speed, then we can use a macro to generate the embedded links as you have".

In my mind, since all of the cells are in the same position on each worksheet (just filtered differently) couldn't a macro just run to say if not blank copy cells in columns E and F to columns E and F on all subsequent worksheets?  

In my opinion that's a more elegant solution then the helper columns and having to Hide/Unhide them for publication/subseqent maintenance.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37798250
Please advise if you want to use the HYPERLINK formula on the master sheet, or you want embedded hyperlinks.  Its easy enough to copy/paste the result on the successive sheets.

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37798296
I do want to note that if all the following sheets are exactly the same as the first, then just making a copy of the first sheet and renaming, then adding filters is the way to go - no code at all.  However, with the links in place this allows you to make a change on the master sheet.  Given that changes could be made, I'll replicate the hyperlinks in columns E and F to the slave sheet.  That way, if you make a change, the hyperlinks are replicated.  It doesn't matter what type you use, I'll just copy them over.

This code copies columns E:F from the master to all worksheets that are not named "Master".  

Here's the code:

Option Explicit

Sub propagateHyperlinks()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksMaster As Worksheet

    Set wkb = ThisWorkbook
    Set wksMaster = wkb.Worksheets("Master")
    
    For Each wks In wkb.Worksheets
        If wks.Name <> "Master" Then
            wksMaster.Range("E:F").Copy
            wks.Range("E1").PasteSpecial
            Application.CutCopyMode = False
        End If
    Next wks
    
End Sub

Open in new window


See attached.  I've put a button to run this from the master sheet.

PS - If you want to replicate the entire sheet from column A:F (since they all have the same data) and not worry about links at all then you can just change lines 13:14 to:

            wksMaster.Range("A:F").Copy
            wks.Range("A1").PasteSpecial

Open in new window




Cheers,

Dave
CurriculumEESafe-r1.xlsm
0
 
LVL 1

Author Comment

by:-Polak
ID: 37798348
Thanks. Works great even if run once the other sheets are already filtered.

Thank you for showing me the non-VBA way of doing it; I'll let you know which I decide on, but I think the Macro is the cleaner solution.
0
 
LVL 1

Author Closing Comment

by:-Polak
ID: 37798360
Excellent Expert. Provided two complete solutions to answer my question.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

862 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