+ Reply to Thread
Results 1 to 15 of 15

Hyperlink Help

  1. #1
    Registered User
    Join Date
    03-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Hyperlink Help

    Hi

    Im trying to get a hyperlink to show up on a different worksheet in the same workbook.

    in the orginal cell (in this example L11) i have placed a hyperlink to an external file.

    in the cell on the other sheet i have placed

    =Sheet1!L11

    this shows the text up (in this example : test ) but the link has disappeared.

    any ideas how i can get the link to show up aswel as the text?
    Last edited by atonyg; 03-25-2009 at 05:47 AM.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Hyperlink Help

    use
    =HYPERLINK(Sheet1!L11)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    03-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Hyperlink Help

    thanks for the reply

    this works if i just have the one cell being called (as in my example)

    however i am also using them in nested IF statements

    =IF(Sheet1!$B$2 = 6, Sheet1!K59, Sheet1!L7)

    the 2 cell references in bold are the ones that contain hyperlinks. as before only the text shows up, not the link.

    i tried placing the HYPERLINK function in that statement, but i dont know if im placing it in the right place.


    thanks again

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Hyperlink Help

    try
    =HYPERLINK(IF(B2=6,Sheet1!K59, Sheet1!L7))

  5. #5
    Registered User
    Join Date
    03-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Hyperlink Help

    that doesn't work

    it just places:

    =HYPERLINK(IF(B2=6,Sheet1!K59, Sheet1!L7))

    into the cell.

    i also tried:

    =IF(Sheet1!$B$2 = 6, (hyperlink(Sheet1!K59)), (hyperlink(Sheet1!L7)))

    but that doesn't work either.


  6. #6
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Hyperlink Help

    I don't have a real answer, but a workaround could be to copy / paste all your links to Sheet1.
    The Hyperlink(If.......) formula does work if the references are on the same sheet.
    See attached example.

    Other way could be to copy the cells in formulas, like you were doing before. That will bring over the text values and then write a macro to convert them to hyperlinks.
    If you want help with this, let me know.

    Hope that helps a little.

    There are lot of experts on this forum, so someone might have a better solution.

    Good Luck,
    modytrane
    Attached Files Attached Files

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Hyperlink Help

    =HYPERLINK(IF(B2=6,Sheet1!K59, Sheet1!L7))
    works for me
    are you saying it just shows the formula? try formatting general before pasting in(nb paste special text)
    see attached also works from sheet 2 to sheet 1(see sheet 2)
    Attached Files Attached Files
    Last edited by martindwilson; 03-24-2009 at 03:35 PM.

  8. #8
    Registered User
    Join Date
    03-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Talking Re: Hyperlink Help

    that method works, and now the hyperlink is clickable.....

    however it doesn't point to the external file that i want it to open.

    any ideas how to get this to happen?

    (thanks for all your help so far )
    Last edited by atonyg; 03-25-2009 at 05:49 AM.

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Hyperlink Help

    well it can only point to what you type in!
    how is the cell your referinging to hyperlinked is it by insert/hyperlink or by =hyperlink(-----)

  10. #10
    Registered User
    Join Date
    03-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Hyperlink Help

    just thought i'd post up my spreadsheet

    in the saved file. i want the user to click on the link in the 'sample file' column and it play back the related file.

    i've just put in a couple of blank links to check that the correct data will show up in the front page.

    thanks again
    Attached Files Attached Files

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Hyperlink Help

    i cant see how this is supposed to work.(besides the fact sheet 1 has #value!,which i presume were links to some audio file)
    i presume you want to chose from those 3 drop downs ,which will in turn select a hyperlink based on the three conditions that refers to one of those audio files?

  12. #12
    Registered User
    Join Date
    03-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Hyperlink Help

    yeah.

    the user selects the microphone, source and position (of the mic) and the appropriate links to the right files show up.

    each microphone sheet contains all the sample links for that microphone.

    these are then called to 'sheet1!', which are then called up to the table on search page, using the criteria set by the if statements.

  13. #13
    Registered User
    Join Date
    03-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Hyperlink Help

    and i was using the 'insert---hyperlink) method.

    your worksheet points to webpages, prefixed with 'http://' is there something similar i need to prefix my file links with?

  14. #14
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Hyperlink Help

    i presume the hyperlinks work ok on their own
    with out reworking the entire workbook. i suggest using a vlookup against the three criteria concatenated together. i used trim to get rid of odd spaces which stopped matches see sheet1 col m and n
    vlookup is in searchpage col f
    note this is saved as '97 xls so some of your original excel 2007 formulas may be broken
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    03-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Hyperlink Help

    cheers martin

    i'll give it a go and see how i get on.

    thanks alot for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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