+ Reply to Thread
Results 1 to 10 of 10

Internal Links Using Microsoft Excel Online

  1. #1
    Registered User
    Join Date
    02-24-2018
    Location
    Palmerton, PA
    MS-Off Ver
    2016
    Posts
    39

    Internal Links Using Microsoft Excel Online

    Hello,

    I have created a workbook that uses formula to create both internal links to different tabs and external links for things like shipment tracking. However, oddly enough, when I open the workbook using Office online through a service like dropbox the internal links do not work. I have no problem using links to things like FedEx and UPS. Does anyone else have this issue or know how I might correct it?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Internal Links Using Microsoft Excel Online

    Do you mean that hyperlink to different sheets in same workbook doesn't work on Excel Online?

    How was your link created? I routinely use following 2 methods and have no issues when uploaded to OneDrive.

    1. =HYPERLINK("#SheetName!A1","SomeText")

    2. Right click -> Link -> Place in this document.

    3rd party file storage service, I don't use so am not sure about that one.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    02-24-2018
    Location
    Palmerton, PA
    MS-Off Ver
    2016
    Posts
    39

    Re: Internal Links Using Microsoft Excel Online

    Hello CK76, thank you for the reply! Here s an example of one of my formulas. This works great on my local machine but does not work through Microsoft online, at least with DropBox.

    =IF(OR(C12="Customer Service",C12="Delay - See CS",C12="Partial B/O - See CS"),HYPERLINK("#"&CELL("address",INDEX(CS!A$1:A$989,MATCH(A12,CS!A$1:A$989,0),1)),"CS Notes"),"")

    In this next example the list refers to where I have a link to the tracking site for FedEx and I can combine it with the tracking number. It works great! However, you will see further in where, if I refer to another sheet because I may have several tracking numbers, the link does not work.

    =IFS(M12="","",L12="FedEx",HYPERLINK(List!$D$4&M12,"TRACK"),L12="See Tracking Page",HYPERLINK("#"&CELL("address",INDEX(Track!A$1:A$1001,MATCH(A12,Track!A$1:A$1001,0),1)),"Track"))

    Whenever I attempt to access an internal link, I get the following error

    "Sorry, we couldn't open this link. The link address may be invalid, or you may not have permission to open it."

    HTH,
    John

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Internal Links Using Microsoft Excel Online

    I thought it was due to Cell function.
    So tested following construct. But worked fine in Excel Online, file stored in OneBox.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I went and got trial subscription for Dropbox. Uploaded the file and used menu to open file using Excel Online.
    It also worked.

    Can you upload sample workbook with file demonstrating your issue? (i.e. same as your original, but with desensitized data).

    To upload, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

  5. #5
    Registered User
    Join Date
    02-24-2018
    Location
    Palmerton, PA
    MS-Off Ver
    2016
    Posts
    39

    Re: Internal Links Using Microsoft Excel Online

    Hello, please find attached the sample file you requested.
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Internal Links Using Microsoft Excel Online

    Ok, I think I figured it out.
    When workbook name along with sheet is supplied to hyperlink function, and it is prefixed with internal reference (#), it causes issue in Excel Online.
    CELL function returns [Tracking 2019_Test.xlsx]Track!$A$8 to hyperlink.

    Since, Track sheet and range is static (based on your index formula)....

    Change your internal hyperlink formula to...
    Please Login or Register  to view this content.
    And it will work.

  7. #7
    Registered User
    Join Date
    02-24-2018
    Location
    Palmerton, PA
    MS-Off Ver
    2016
    Posts
    39

    Re: Internal Links Using Microsoft Excel Online

    Please forgive me but my formula does not refer to the workbook anywhere that I can see. I am attaching a notepad with just the formula. Also, I tried to change the formula as you suggested and, assuming I did it correctly, it did not work. It seemed to corrupt the formula. Do I need to change the location in the formula to look at the track sheet first?

    Mod Edit (to avoid use of unnecessary attachment):


    =IFS(M2="","",L2="FedEx",HYPERLINK(List!$D$4&M2,"TRACK"),L2="UPS",HYPERLINK(List!$D$5&M2,"TRACK"),L2="AIT",HYPERLINK(List!$D$3&M2,"TRACK"),L2="XPO",HYPERLINK(List!$D$6&M2,"TRACK"),L2="A DuiePyle",HYPERLINK(List!$D$7&M2,"TRACK"),L2="SAIA",HYPERLINK(List!$D$8&M2,"TRACK"),L2="SEFL",HYPERLINK(List!$D$9&M2,"TRACK"),L2="Holland",HYPERLINK(List!$D$10&M2,"TRACK"),L2="Daylight",HYPERLINK(List!$D$11&M2,"TRACK"),L2="Ceva",HYPERLINK(List!$D$12&M2,"TRACK"),L2="Echo",HYPERLINK(List!$D$13&M2,"TRACK"),L2="Old Dominion",HYPERLINK(List!$D$15,"TRACK"),L2="Estes",HYPERLINK(List!$D$16,"TRACK"),L2="Pilot",HYPERLINK(List!$D$17,"TRACK"),L2="Estes",HYPERLINK(List!$D$16,"TRACK"),L2="See Tracking Page",HYPERLINK("#"&CELL("address",INDEX(Track!A$1:A$1001,MATCH(A2,Track!A$1:A$1001,0),1)),"Track"))
    Attached Files Attached Files

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Internal Links Using Microsoft Excel Online

    As stated my previous post, Cell() function, returns [WorkbookName.xlsx]SheetName!Cell when other sheet is referenced.

    So below portion of your formula,
    CELL("address",INDEX(Track!A$1:A$1001,MATCH(A2,Track!A$1:A$1001,0),1))

    Evaluates to...
    [Tracking 2019_Test.xlsx]Track!$A$1

    This is causing issue here. So your last hyperlink formula should be replaced with my suggestion.
    Ex:
    =IFS(M2="","",L2="FedEx",HYPERLINK(List!$D$4&M2,"TRACK"),L2="UPS",HYPERLINK(List!$D$5&M2,"TRACK"),L2="AIT",HYPERLINK(List!$D$3&M2,"TRACK"),L2="XPO",HYPERLINK(List!$D$6&M2,"TRACK"),L2="A DuiePyle",HYPERLINK(List!$D$7&M2,"TRACK"),L2="SAIA",HYPERLINK(List!$D$8&M2,"TRACK"),L2="SEFL",HYPERLINK(List!$D$9&M2,"TRACK"),L2="Holland",HYPERLINK(List!$D$10&M2,"TRACK"),L2="Daylight",HYPERLINK(List!$D$11&M2,"TRACK"),L2="Ceva",HYPERLINK(List!$D$12&M2,"TRACK"),L2="Echo",HYPERLINK(List!$D$13&M2,"TRACK"),L2="Old Dominion",HYPERLINK(List!$D$15,"TRACK"),L2="Estes",HYPERLINK(List!$D$16,"TRACK"),L2="Pilot",HYPERLINK(List!$D$17,"TRACK"),L2="Estes",HYPERLINK(List!$D$16,"TRACK"),L2="See Tracking Page",HYPERLINK("#Track!A"&MATCH(A2,Track!$A$1:$A$1001,0),"Track"))

    However, upon further inspection, exact cause of hyperlink issue, is that there is space in your workbook name.

    In URL encoding, space is represented as %20, but formula still returns single space. Hence the error.

    There are number of ways this issue can be avoided.

    1. Remove space from your file name (ex: replace single space with "_" underscore).
    2. Use Hyperlink formula construct in my previous post to return sheet name and cell reference without workbook name.
    3. Use SUBSTITUTE function to replace single space with %20.
    Last edited by CK76; 08-23-2019 at 10:36 AM. Reason: A4 change to A2

  9. #9
    Registered User
    Join Date
    02-24-2018
    Location
    Palmerton, PA
    MS-Off Ver
    2016
    Posts
    39

    Re: Internal Links Using Microsoft Excel Online

    Thank you CK76! I must have entered something incorrectly the first time. I have it working now!

  10. #10
    Registered User
    Join Date
    10-14-2020
    Location
    Dublin
    MS-Off Ver
    Professional plus
    Posts
    44

    Re: Internal Links Using Microsoft Excel Online

    Disregard post (Please delete)
    Last edited by Nicolantonio; 11-25-2020 at 06:00 AM. Reason: I had found a soliution to my question.

+ 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. Excel Online - External Links Disabled - OneDrive
    By cassca in forum Excel General
    Replies: 4
    Last Post: 11-30-2021, 05:28 AM
  2. Break Internal Worksheet links in a file
    By lalaarif1 in forum Excel General
    Replies: 1
    Last Post: 10-11-2015, 03:16 PM
  3. [SOLVED] List of Internal links
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-06-2015, 01:18 PM
  4. Replies: 3
    Last Post: 04-14-2014, 09:01 AM
  5. Re: Which are online microsoft excel online course ?
    By sun0flower in forum Excel General
    Replies: 0
    Last Post: 09-18-2012, 05:15 AM
  6. [SOLVED] Convert external links to internal
    By Marie J-son in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2006, 11:55 PM
  7. Internal links
    By Job in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-26-2005, 03:05 PM

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