?
Solved

Linking Cells with Hyperlinked Entries?

Posted on 2012-03-29
8
Medium Priority
?
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 
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

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.

Question has a verified solution.

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

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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