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

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!

Please post this as a new question. Thanks.

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.

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.

Perhaps the first workbook that was opened when Excel opened had calculation set to manual. When I make a link from one workbook to another the link is live and updates instantly.

Answer 4

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 way this works appears to be by design. If you want to suggest to Microsoft that CTRL+TAB be able to maintain the selection cursor as the focus changes from one workbook to another, please use Excel's Help menu and choose Send Feedback. Describe the step-by-step scenario as you have done here.