Kutools for Excel’s Split Date utility can help you to quickly split data into multiple worksheets based on selected column as below screenshot shown in Excel. Click for 60 days free trial! Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.
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.
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! 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.