Broken hyperlinks going from Mac to PC in Excel

[annelidel]

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!!

 

[GGJstudios]

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??

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?

 

[annelidel]

@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.

 

[Saturdays]

Are you guys running Office 2011? Also, I doubt its a firewall issue considering everything is within the excel document.

 

[annelidel]

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.

 

[roflwaffles]

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.

 

[GGJstudios]

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

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.

 

[jlcoffin]

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

 

[wrldwzrd89]

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:

='Sheet Name'!A1

... where Sheet Name is the name of the worksheet you wish to reference, and A1 is a cell reference.

 

[rags27]

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!

 

[wrldwzrd89]

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!

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.

 

[lam2020]

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.

How do I create a similar path, or folder on a Mac?

For example, windows directory tree is: C:\inetpub\root\source

 

[wrldwzrd89]

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.

 

[lam2020]

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.

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

 

[wrldwzrd89]

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.