All Formulas return #VALUE error when spreadsheet is reopened

[Fourdaystil]

I am using Windows 7 Excel 2010.

I have a two-tab workbook, let's call them "A" and "B". A pulls all it's data from B through formulas. We realized B is no longer correct data, so we regenerated this report, which created a new worksheet called "C". We pulled in this worksheet to the workbook, deleted tab "B" and renamed C to "B".

This changed all the formulas in A to #VALUE error. I realize it is still looking for the deleted worksheet for data. When we type "Ctrl + Shift + Alt + F9," it will recalculate all formulas looking only at the dependent as stated, so it pulls in the correct data from our new worksheet B. But if we save this, and close, it will show #VALUE error upon reopening.

My only thought on how to fix this, would be to rename C to something simple like "Sheet1", then do a Find and Replace All function on worksheet A, looking for the old sheet name and replacing with 'Sheet1'. But there may be a better way to change all outside-sourced formulas within a worksheet to look at a different source (btw, some of the formulas are doing calculations within the sheet, so not pulling from another worksheet).

Thank you for your help!!

 

[RoryA]

If you deleted the sheet that built-in formulas were referring to, they should return #REF not #VALUE. Are you using UDFs?

 

[Fourdaystil]

Does UDFs mean "User-Defined Formulas"? I'm not sure what this would mean. A co-worker is the one who has the issue, so I'm not exactly sure of the sequence of events. It is possible he never deleted the old one, but moved the worksheet "A" to the workbook with the new worksheet, then imported into our software. I am sure the issue is that the formulas are still looking for the old worksheet that is on a different system (it's like the workbook is on a server now and the old worksheet is on a local drive).

 

[RoryA]

Well I suspect the best solution is going to depend on the exact nature of the problem, so you need to find out what exactly happened.

If the workbook is looking for the source in a different workbook, you may be able to simply use the Edit Links option on the Data tab to repoint the formulas to the current workbook.

 

[Fourdaystil]

I might want to mention also that the worksheet "B" in this scenario is pulling all data from our software through an Excel Add-in.

 

[Fourdaystil]

I totally agree about knowing exactly what happened; however, I think my coworker doesn't even remember how he did it all. So, I'm stuck with what I can assume. I didn't even think about the "Edit Links" option. I will try this and let you know if it works! Thank you so much for your help!

 

[Fourdaystil]

Unfortunately, the "Edit Links" option only shows the Add-in where worksheet B is pulling numbers from (which is correct and working properly). It does not show any link for the worksheet A to worksheet B. As long as we do Ctrl + Shift + Alt + F9 each time we open the workbook, we can see all the formula results properly. My method of Find and Replace does not work because it pulls up a file open dialog for each cell to select the appropriate source. I would be better off just rebuilding the entire spreadsheet. We did copy and paste the document in the software storage program just to have something to work on without ruining the original, and when we had both open at the same time, all the formulas magically worked. But when we closed and opened each one by itself, they required a full recalc function again. I don't know why this is since one was just an exact copy of the other. Maybe one is trying to locate the data from another workbook within the program, but only does this when the other workbook is open.

I really think we are at a point that the only option to get this error to go away is to start over and rebuild the entire workbook.