Solved

Linking Cells with Hyperlinked Entries?

Posted on 2012-03-29
8
223 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 41

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 41

Assisted Solution

by:dlmille
dlmille earned 500 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

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 41

Accepted Solution

by:
dlmille earned 500 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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
TT Column Arrange 10 28
cannot get subtotal to work 8 18
VLOOKUP Function MS Excel 2010 2 22
TT Formula Check 9 11
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

746 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

13 Experts available now in Live!

Get 1:1 Help Now