Linking Cells with Hyperlinked Entries?

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.
LVL 1
-PolakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
Can you provide a non-sensitive version of your workbook to work from?

Thanks,

Dave
0
-PolakAuthor Commented:
Sure, here you go. All links have been set to google.com.
CurriculumEESafe.xlsx
0
dlmilleCommented:
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

-PolakAuthor Commented:
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
dlmilleCommented:
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
dlmilleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
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
-PolakAuthor Commented:
Excellent Expert. Provided two complete solutions to answer my question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.