• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell Link to a Hyperlink?

S

sbglobal2012

New Member
Greetings,


Apologies for what may be a simple question; my searches have been fruitless so far!


I've got an Excel Workbook containing a 'Master' sheet and several sub-sheets. (Running Excel for Mac, 2011). The sub-sheets contain only links to cells in the master sheet. The sub-sheets allow for easier searching through different categories of information in the master sheet, but because all sub-sheet data is linked to the master, I only need to edit one cell to have the changes reflected in all the sub-sheet categories.


A set of cells in the master contain hyperlinks to external web pages and documents. When I use the 'Paste Special' > 'Paste Link' command to populate the sub-sheet cells, I see the text of the hyperlink, but the hyperlink is not active. If I simply 'paste all', then the hyperlink is active, but it is not linked to the master sheet.


My question is: how do I (preferably in a single operation) create a cell link in my sub sheet that refers to the master sheet and remains an active hyperlink?


Thanks in advance for your help!
 
I don't think you can do that. The hyperlink to the external sites is only on the Master Sheet. On the sub-sheets, you can either place a hyperlinklink to the Hyperlink (sort of like a layover on a plane flight), a regular link showing just the text of the hyperlink, or you can copy the hyperlink.


It would take two clicks but I think the hyperlink to the main hyperlink would be your best bet.
 
Possible, but it starts getting tricky. =)

Depends a lot on how the hyperlinks are setup; whether it's a formula or just embedded into the cell.


Thanks for the confidence!
 
Hi, sbglobal2012!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Cell%20Link%20to%20a%20Hyperlink_%20%28for%20sbglobal2012%20at%20chandoo.org%29.xlsx


It's a just formula solution, no macros.


If you can afford to use a helper column check solution at cell C1 of sheets Slave1 and Slave2. You can easily then hide column B of slave sheets and these sheets will keep their previous aspect. You'd only need to unhide them when modifying original links to Master sheet.


Regards!
 
Not sure I totally understand the application but if you had a master sheet with a list of web addresses

eg: A2 had the text http://www.chandoo.org


Then you can use =HYPERLINK('Master Sheet'!A2,"Chandoo.org") anywhere

If you change the value in A2 it updates all the links automagically
 
You must log in or register to reply here.
Back
Top