Problems linking data between workbooks....

Question

In Excel for Windows, you can create a link to data in another workbook, simply by entering '=' and then physically clicking the cell you want to create the link with in another workbook.

I'm having problems establishing how you can do this in Excel 2011 Mac? When entering the '=' and clicking in another workbook, nothing happens. I am aware that you can paste a link, but this isn't very practical when using a spreadsheet which accumulates an awful lot of linked data.

Secondly, when linking the data, (I'm currently using the paste method), the cell references don't update correctly. For example, I create a link in cell A1 to another workbook. I then delete this second workbook but the cell reference remains until I manually calculate that cell again. Shouldn't the calculation be instant?

Finally, and most importantly, I'm having problems with existing documents that have been created on Excel 2010, that are stored on a Windows network share. I open up a document in Excel 2011 from this share, but the links to other documents, (sometimes in the same network folder) can't be resolved, even when using the update links dialogue box. In fact, even if I try to change the source, the document I want to link to is greyed out and I can't be selected. This is a MAJOR cause for concern as I have a number of finance users who wish to migrate to Office Mac, but so far cannot due to this fundamental issue.

Any help will be greatly appreciated!

Answer 1

Update: Seeing as these problems have rendered Office Mac 2011 useless for our production environment, we've had to revert to having Windows installed on a VM and using Office 2010. Not ideal, but at least it works. Thanks for the suggestion anyway people.

Answer 2

No one?

Answer 3

Hi Bob.

My apologies for the apparent misuse of my post, in my mind they were all the same sort of issue, which is why they were put under the same post.

For the first part of my problem, it appears that it doesn't work quite the same way as it does on Windows. When testing the functionality with my finance manager, she explained that when she creates links from one workbook to another, she types in the equals sign, then uses ALT+TAB to jump to the other open workbook, clicks the cell and hits enter which does what you would expect.

However, on OS X, the keyboard combination to jump between workbooks, CTRL+TAB, (to move between other instances within a given program, not to be confused with CMD+TAB which switches between programs), removes the "selection" process after entering the equals sign - we've found that it does indeed work if you don't use the keyboard command to jump between workbooks, but then this isn't entirely useful, as generally speaking, the user will have spreadsheets maximised - having to manually move things around isn't really ideal.

Admittedly, the process still works if you use mission control to change from one workbook to another, but again, this isn't exactly the fastest approach as the process seems a little bloated. It's almost as though pressing the CTRL+TAB combination ends the editing session of the last active cell... Any ideas as to how we get around this, is there something we're missing?

The second issue; well calculation mode was the issue we thought it was having. Though it would appear that it is not the case as calculation is set to auto.

As for the final issue, the files can all be accessed individually, though they are not stored in the same specific folders but sit on the same area of a network share. Is this a compatibility issue with SMB/AFP perhaps?

Answer 4

First, it would be easier for you, those of us who answer posts, and other users who are trying to solve their own problems if each post was restricted to a single issue. You have posed three in single post.

Nevertheless, for the first, you create a link from one workbook to another EXACTLY the same way in windows as is done in a Macintosh. you type an equal sign in the destination cell, and then switch to the source worksheet and click on a cell. then press enter. If this is not happening for you, please explain in more detail what you have done and what is or is not happening.

Secondly, the cell references are probaly not updating because you have calculation set to manual. Calculation is a "global" setting, but its state is saved with the workbook. So if you open a workbook that has been saved with manual calculation, the calculation mode will be changed. make sure calculation is set to automatic before you paste.

For the final issue, are the linked to documents in the same folder as the source document? If not, can you access them individually?