Broken hyperlinks going from Mac to PC in Excel

Discussion in 'Mac Apps and Mac App Store' started by annelidel, Dec 14, 2010.

  1. annelidel macrumors newbie

    Joined:
    Dec 14, 2010
    #1
    Hello,
    Most of my company is PC based, but we artists are Mac based. We all work on the same Excel file, updating, saving, replacing. When we open the PC excel file on our Macs, none of the hyperlinks work ... then if we save something to the file, when the PC guys open the file, none of the hyperlinks work for them anymore either. We really want to be able to use Excel and not have to use VMware in order to update our file on the Mac. Any thoughts??

    Thank you!!
    :eek:
     
  2. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #2
    Something's definitely wrong there. I routinely share Excel files containing hyperlinks with Windows users and never have a problem with hyperlinks not working. You might check firewall settings to see if Excel is blocked from internet access on either end. When you edit the hyperlinks, do they still show to be intact?
     
  3. annelidel thread starter macrumors newbie

    Joined:
    Dec 14, 2010
    #3
    @GGJstudios ... Thank you for your reply. It is encouraging to know its not impossible! You asked: "When you edit the hyperlinks, do they still show to be intact?" They do show to be intact, but the path is invalid. I should mention all the links are to pages (tabs) or cells within the document. I will have a look at the firewall(s). I work remotely though most of the other Mac users work on site ... same as the PC users ... and the company is good about keeping current with the software upgrades.
     
  4. Saturdays macrumors member

    Saturdays

    Joined:
    Nov 18, 2010
    Location:
    Somewhere in USA
    #4
    Are you guys running Office 2011? Also, I doubt its a firewall issue considering everything is within the excel document.
     
  5. annelidel, Dec 14, 2010
    Last edited: Dec 14, 2010

    annelidel thread starter macrumors newbie

    Joined:
    Dec 14, 2010
    #5
    Actually, I'm using Excel 2008 for Mac (12.2.6). On the PC they are running Office Excel 2007 but typically have to save things down to 97-2003 for some folks who haven't updated.
     
  6. roflwaffles macrumors newbie

    Joined:
    May 9, 2011
    #6
    Hi,

    It turns out that when opening or creating links in iWork on a Mac, the spaces in any hyperlink are converted to %20.

    I found that opening an excel document with an index sheet for all the other sheets that was created on a Windows platform would be broken when opened on a Mac as all the spaces in names in hyperlinks would be changed and therefor broken.

    I went through and changed the spaces in all sheet names and file names to underscores instead and now I can use hyperlinks in Windows and Macs with no problems.

    Hope this helps.
     
  7. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #7
    That's moving between Excel and iWork's Numbers. The discussion here is about moving between Excel for Windows and Excel for Mac, which doesn't do what you describe. Also, no web hyperlink can contain spaces.
     
  8. jlcoffin macrumors newbie

    Joined:
    Jun 2, 2011
    #8
    more broken hyperlinks

    Hi there,

    I'm having a similar problem, but not between computers. I successfully made hyperlinks between sheets within one excel document using CMD-K and then the Documents tab and naming the specific cell I want to hyperlink to. I tested them and they worked. After 10 hours or so, they all stop working. I click on them and they give me a "Reference not valid" error message.

    This has happened three or four times, and we've tapped out the technical expertise within our little company.

    Really could use a little help!!

    Thanks,
    jlcoffin
     
  9. wrldwzrd89 macrumors G5

    wrldwzrd89

    Joined:
    Jun 6, 2003
    Location:
    Solon, OH
    #9
    The solution to the OP's hyperlink problem is to use relative links, not absolute links. Absolute links WILL break going across platforms.

    The syntax for a relative link is:
    Code:
    ='Sheet Name'!A1
    
    ... where Sheet Name is the name of the worksheet you wish to reference, and A1 is a cell reference.
     
  10. rags27 macrumors newbie

    Joined:
    Jun 28, 2014
    #10
    I have the same problem

    I have the same problem. When Excel files with hyperlinks are created in Windows Excel, the hyperlink does not work in Excel Mac.

    A reliable solution will be much appreciated.

    Thanks!
     
  11. wrldwzrd89 macrumors G5

    wrldwzrd89

    Joined:
    Jun 6, 2003
    Location:
    Solon, OH
    #11
    Are you trying to link to another Excel spreadsheet? The only way this can have ANY chance of working reliably across platforms is if the two spreadsheets are in the same folder on both OSes.
     
  12. lam2020 macrumors newbie

    Joined:
    Dec 17, 2014
    #12
    How do I create a similar path, or folder on a Mac?

    For example, windows directory tree is: C:\inetpub\root\source
     
  13. wrldwzrd89, Dec 17, 2014
    Last edited: Dec 17, 2014

    wrldwzrd89 macrumors G5

    wrldwzrd89

    Joined:
    Jun 6, 2003
    Location:
    Solon, OH
    #13
    A relative path is needed here. For example: BookXD.xlsx!A3 (referencing a workbook in the same folder as the active one) instead of C:\Users\me\Documents\BookXD.xlsx!A3 (absolute Windows path) or /Users/me/Documents/BookXD.xlsx!A3 (equivalent absolute Mac path).

    EDIT: If you need to tell Excel to fetch data from a sub folder of the current one, or the parent folder, you will need to convert \ to / going from Windows to Mac, and vice versa the other way.
     
  14. lam2020, Dec 17, 2014
    Last edited: Dec 17, 2014

    lam2020 macrumors newbie

    Joined:
    Dec 17, 2014
    #14
    So let's say my 'original' files are located on a windows server in a directory path of:

    c:\inetpub\root\source\maindocument.xlsx

    it has references to another workbook located in:

    c:\inetpub\root\reference\referenceworkbook.xlsx

    in my maindocument.xlsx i would have to use:

    ~/inetpub/root/reference/referenceworkbook.xlsx ?

    and when i upload this file back onto the server it would know to change it to:

    c:\inetpub\root\reference\referenceworkbook.xlsx ?

    currently, when i open the file created in windows it appears the reference cell is converted to:

    c:inetpub:root:reference:referenceworkbook.xlsx
     
  15. wrldwzrd89 macrumors G5

    wrldwzrd89

    Joined:
    Jun 6, 2003
    Location:
    Solon, OH
    #15
    You're working outside "user space", presumably due to a server environment on the Windows side. This means there is no equivalent location for the Mac side. So your best path forward is VBA tied to a button, called "Fix Paths", which runs a macro that detects the OS and changes the external paths accordingly. The simplest way to detect the OS is to fetch the formula for affected cells and look for \ characters (Windows) and lack of : characters with the InStr function; the inverse applies for a Mac environment. As for the paths themselves, set the Formula property for each cell to the correct value based on the OS determined earlier. Most of the macro can be generated with the recording feature.
     

Share This Page