I work with workbooks having multiple tabs and about 200,000 cells, 90% of which have formulas. I use the Solver addin for these workbooks
I've run into two bugs in Excel 365, 64 bit which is preventing me from using Solver, and preventing me from saving a worksheet which took hundreds of hours to create and refine.
If I have a small table containing the list of variables for the entire worksheet, on worksheet "A" and cut/paste that table to an empty space in worksheet "B", open solver, hit the "reset all" button and then enter new data, constraints etc., Solver will rename some of the constraints to some kind of code, rather than the expected formulation (e.g. "B5>=1"). Solver will instead save that as something like solver_lhs5 >=1
See this screenshot, and notice last two constraints.
https://www.dropbox.com/s/ns4bd6w2n6nmy7n/Screenshot%202017-08-09%2014.01.16.png?dl=0
If I try the "reset all" button again, the problem is replicated.
Worse, if I hit "reset all" button and try to save the workbook I get this message: "Errors were detected while saving ___ . Microsoft Excel may be able to save the file by removing or repairing some features. To make the repairs in a new file, click Continue..."
So I click "Continue" and get another error message saying "document not saved."
If this were a simple worksheet I'd just copy it over to a new one. But this is a complex workbook, and in the past when I've tried to copy/paste pages from it, the new workbook will contain references in its formulas back to the orignial workbook. That means I have to discover, find/replace those references. A tedious task!
PS, when I first contacted Microsoft about this I was first connecteed to a tech who could not solve the problem. As a last resort she suggested doing an "online repair" which I did. After the online repair, the suite notifed me that my software wasn't activated. But when trying to activate it, I was presented with a window saying that the page could not be found. I contacted Microsoft again. A new tech suggested remote access. He managed to screw up the VPS on which I was running office, leaving me unable to connect to it. I got that resolved on my own. In any case, this issue is not confined to this one computer or workbook. It occurs on another computer with another version of my workbook.
It might be more efficient to resolve this issue if someone could tell me how to copy/paste a complex workbook to a new one, in a way which does not refer formulas back to the original one. Then in the future, I just won't try to move my "solver" inputs to another worksheet!
Thanks.
