This has happened to me multiple times (but not always). It is very frustrating and I have no idea why it does this. A.xls contains formula that links to B.xls Sheet1. I am working on A.xls. I decide to open B.xls.
Once I do - all references to Sheet1 turns into #REF. B.xls!Sheet1'!A1 turns into ='C:. B.xls!#REF'!A1 and naturally all values that were previously correct now returns #REF. This is absolutely frustrating as there is no way for me to undo it without re-opening my last saved version of A.xls. Find and replace is not ideal either because A.xls refers to several different worksheets of B.xls - and they're all now #REF. Any ideas why?:( Running Excel 2010.
![Excel For Mac Opening New Workbook Excel For Mac Opening New Workbook](/uploads/1/2/5/6/125602734/186791421.png)
![Excel for mac opens new workbook when opening file Excel for mac opens new workbook when opening file](http://farmakeio.club/wp-content/uploads/2018/04/excel-save-as-shortcut-shortcut-for-save-as-in-excel-excel-shortcut-for-opening-a-new-worksheet-in-mac-shortcut-excel-keyboard-shortcut-save-file.jpg)
Most formulas in excel do not update their formulas if they refer to closed workbooks. Now, if you open a workbook the formulas refer to, excel will try to update it immediately. Your problem I believe is that changes have been made to B.xls and the formulas in A.xls are trying to refresh its formulas when you opened B.xls. Because there has been changes in B.xls from since the formulas in A.xls were last calculated (such as sheets were renamed or plainly deleted), the formulas in A.xls cannot find the previous reference, and so return #REF!
After you open a shared workbook, you can enter new or change existing data in a regular way. 27 Responses to 'How to share Excel file for multiple users and co-author a shared workbook' Mike says: August 3, 2017 at 12:58 am. Apps for Excel for Mac. Merge two worksheets. Count & Sum colored cells.
I think you can have two options to avoid this:. Remove the formulas in A.xls by copy/pasting values. This will prevent any updates in A.xls from changes in B.xls and any other formulas in the sheet.
This is a downside in itself though. Make sure any changes to B.xls does not involve sheet deletion, renaming or row/column deletion in those that supply the formulas in A.xls. To help with Jerry's comment. When you close A.xls it will remember the values in it from the formula.
If you then alter B.xls in anyway such as workbook name or sheet name, then when A.xls tries to update itself with the new data it cannot be found because the referenced name has changed. Say your formula is something like ='B.xlsSheet3'!$B$1588 if you change the name of B.xls after closing A.xls to 'Data Saved ' then when you open A.xls it will have the values from B.xls. As soon as you open Data Saved then A.xls will realize that the data does not exist anymore and lose it. The work around for this is to make sure that you update the links in the formula. This should solve it all.
This is the area where you will enter data. Rows are cells aligned horizontally. Columns are cells aligned vertically. A worksheet is a single page within a workbook. Like the tabs in an internet browser, the tabs in an Excel workbook show different pages, or worksheets. A workbook may have many worksheets included in it.
In this screenshot, the workbook only has one worksheet and one tab, which is labeled Sheet1. The selected tab shows the selected worksheet. Clicking the + button will add another worksheet. When you save a workbook in Excel, all of the worksheets in that workbook are saved.