+ Reply to Thread
Results 1 to 16 of 16

Hyperlinks from referenced cells

  1. #1
    Registered User
    Join Date
    10-14-2019
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    18

    Question Hyperlinks from referenced cells

    Hi Forum,

    I am building a spreadsheet, to track work, and the URL may change depending on which system we are using to track the work - we currently have 4 likely locations, and the work may move from one to another.

    In column A - I select the location of the column - so B populates the cell with the correct URL from (in the attached example) columns C,D or E using an if statement.

    The problem I have, is that all the links work in columns C,D and E but when displayed in B - they don't work.

    Perhaps the "if" statement isn't the way to do this - I'd prefer not to use macros if possible.

    I like to hide the URL with a friendly name - so I can't use the text URL and hyperlink it - this works but is ugly.

    Can anyone help?
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-11-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    144

    Re: Hyperlinks from referenced cells

    Hi,

    Please find the solution in attached excel
    Attached Files Attached Files
    Ravikumar,

    * Please Add Reputation if solved.

  3. #3
    Registered User
    Join Date
    10-14-2019
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    18

    Re: Hyperlinks from referenced cells

    Thanks fshbet.

    It works (of course) but doesn't solve the problem - I need to use the friendly name from the original link - since the entries in Column A are actually static - wheras the links in column B (and in my example spreadsheet, columns C,D and E)will be unique. I could add a further few columns to add the friendly name, but I'm trying to streamline the process...

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hyperlinks from referenced cells

    I wasn't very clear what you wanted to see.... there was a lot of stuff, but I couldn't understand what you expected answer was. But.... try this, with a slight change of layout:

    =HYPERLINK(VLOOKUP(A2,$F$2:$G$4,2,FALSE),A2)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Registered User
    Join Date
    10-14-2019
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    18

    Re: Hyperlinks from referenced cells

    Hi Glenn,

    If you look at my original example - there are links in column C,D and E. These have the friendly name, and a URL - which in the real spreadsheet, will be an Incident Number as the friendly name, and the URL to the system where the case is recorded. These will arrive via copy & paste, so I don't want to have to change that to two columns - one for the friendly name, and one for the URL - since in my system, the URL itself does not contain the actual Incident number.

    So - as I have several systems and each generate different numbers and letter combinations - and I need to record them all, I can't use a single column and "overwrite" it with the appropriate reference, I want to select the "current" tracking system in Column A - and have it populate Column B with the link (including friendly name) from either column C,D or E (actually in the real spreadsheet, there are more of them, and they are much further (V to Z) away with lots of other data between them)
    Regards,


    BatterBits

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hyperlinks from referenced cells

    Nope.

    What do you expect to see? Where do you expect to see it?

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Hyperlinks from referenced cells

    Quote Originally Posted by fshbet View Post
    Hi,

    Please find the solution in attached excel
    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    10-14-2019
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    18

    Re: Hyperlinks from referenced cells

    Quote Originally Posted by Glenn Kennedy View Post
    What do you expect to see? Where do you expect to see it?
    In B4, I want to see a working hyperlink, which is taken (copied?) from C4, D4 or E4 depending on the setting in A4

    At the moment, I have a working Hyperlink in those 3 (C,D & E) columns, but when the IF statement references the cell containing the correct hyperlink, although it displays correctly, it shows an error message when the link is clicked.

    so - if A4 references D4, although the actual link in D4 works if you click it, the link presented in B4 does not work

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hyperlinks from referenced cells

    RU OK with a UDF solution, that will require saving as .xlsm??

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hyperlinks from referenced cells

    Please Login or Register  to view this content.
    How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    Then use:

    =HYPERLINK(getHYPERLINK(INDEX(C2:E2,,MATCH(A2,$C$1:$E$1,0))),A2)
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-14-2019
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    18

    Re: Hyperlinks from referenced cells

    Hi Glenn,

    I really appreciate your help here, it's almost right, but not quite - my apologies for not being clearer in the first place - it made perfect sense to me when I wrote it, but then I know what I'm trying to achieve..

    In this test sheet,
    A4=GOOGLE - from a lookup list - text "label" - this is correct.
    C4= google (the friendly name - from Column A) - a hyperlink to google with a search term of test. My lazy input, it would have been clearer if I had made the friendly name of this Google-Test1
    B4. Currently displays google - from column A - it needs to display the friendly name from C4 - which should (as above) therefore be Google-Test1, with the URL from C4

    so it should read from column A
    GOOGLE|Google-Test1|Google-Test1|Yahoo-Test1|Bing-Test1


    In essence, it must take the friendly name from the source URL hyperlink, not from another column/cell

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hyperlinks from referenced cells

    Can you add a new smple sheet, showing what you want? I am almost terminally confused here!!!

  13. #13
    Registered User
    Join Date
    10-14-2019
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    18

    Re: Hyperlinks from referenced cells

    Hi Glenn,

    So this is how it should look - but the links in Column B should work (they don't).

    And note what happens if you change a Column A entry from the validation list...
    Attached Files Attached Files

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hyperlinks from referenced cells

    Try this (fingers crossed...):

    =HYPERLINK(gethyperlink(OFFSET(C2,,LOOKUP(A2,$A$19:$A$21,$B$19:$B$21),,)),OFFSET(C2,,LOOKUP(A2,$A$19:$A$21,$B$19:$B$21),,))
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-14-2019
    Location
    West Midlands
    MS-Off Ver
    2016
    Posts
    18

    Re: Hyperlinks from referenced cells

    Hi Glenn

    Thanks - that works exactly as required. I confess I'm not sure exactly how it works, but looks as if I need to add a column populated with the offset value (as you have in B19:B21)?

    I assume that the C2 reference is the first (0)column - so the columns need to be either next to each other or manipulate the offset number accordingly.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hyperlinks from referenced cells

    Correct about offset. On phone, not PC, so can't provide explanation right now. But, hey...we got there!!

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dragging cells to other referenced cells gives REF ERROR.
    By iturriko in forum Excel General
    Replies: 2
    Last Post: 11-29-2018, 10:00 AM
  2. Pulling through hyperlinks from referenced cells
    By Ben01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2016, 01:02 PM
  3. Find Hyperlinks, Copy Hyperlinks to alternative sheet, print all hyperlinks
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2013, 05:13 PM
  4. Offseting referenced Cells
    By kickme in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-27-2012, 07:33 AM
  5. Hyperlinks - Locking hyperlinks to cells
    By snoopy1461 in forum Excel General
    Replies: 4
    Last Post: 12-14-2011, 10:57 AM
  6. Display referenced cell and related hyperlinks
    By pepe.r.taylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2011, 08:10 AM
  7. Sorting Referenced Cells in VBA and Keeping Them Referenced
    By BrownTeddyBear in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2009, 06:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1