Thank you for the response! I decided to try coping one element at a time in order to help locate what the issue may be. I have written out a log of the actions I took to resolve this issue. Should you want to just see the results, feel free to skip past the "Trial and Error" section to the "Results" section at the bottom of the post.
Please note that copying and pasting from inside the original document does not result in the document crashing upon reopening, only when it is copied from the original to a different workbook.
Trial And Error
Here is a log of what happened to each thing I copied. The Widget in question is Widget B) Circular Dial Widget Type #1 (Light Version). Each trial was performed by having the original widget workbook open, opening a blank, unchanged workbook, copying and pasting from the original workbook to the new workbook using Ctrl+C and Ctrl+V, saving on the desktop and closing the new workbook, then reopening the new workbook.
- b) Light Circular Dial Widget Type #1 (Crash)
- Circular Dial1 Title (Crash)
- Circular Dial1 % Value (Crash)
- Circular Dial1 Total Value (Crash)
- Circular Dial1 Main Value (Crash)
- Circular Dial1 LightBlue Circle (Works)
- Circular Dial1 Donut Chart (Crash)
- Circular Dial1 Units (Crash)
- Light Circular Dial1 Background Rectangle (Works)
Upon closer inspection, each of these failed elements are linked to another place inside the workbook, so this time I repeated what I did before, but after pasting the elements with formulas into the new workbook, I deleted the formula before saving and closing. Here are the results:
- Circular Dial1 Title (Crash)
- Circular Dial1 % Value (Crash)
- Circular Dial1 Total Value (Crash)
- Circular Dial1 Main Value (Crash)
- Circular Dial1 Donut Chart (Crash)
- Circular Dial1 Units (Crash)
Again, they all crash. I try this once more, except I delete the formulas in the original workbook first before copying the element over to the new workbook. Here are the results:
- Circular Dial1 Title (Works)
- Circular Dial1 % Value (Works)
- Circular Dial1 Total Value (Works)
- Circular Dial1 Main Value (Works)
- Circular Dial1 Donut Chart (Works)
- Circular Dial1 Units (Works)
This is good news! By deleting the formula before copying it, I was able to move each individual element. After all the formulas had been deleted,
I copied and pasted the entire widget as a whole, and
I was able to open the workbook. The results of these trials have shown that the issue lies within the formula itself and not the actual widget or elements.
I questioned that perhaps the real problem inside the formula is where the information is being linked from. Since I use OneDrive at my company, I had the original workbook saved on there. With this in mind, I performed another set of trials where I had the original workbook either on the local desktop or on the shared OneDrive folder. The new workbook will follow the same rules. I copied the entire Widget B) Circular Dial Widget Type #1 (Light Version) from the Original to the New without modifying any of its elements. Here are the results (From=Original. To=New):
- From OneDrive - To OneDrive (Crash)
- From OneDrive - To Desktop (Crash)
- From Desktop - To Desktop (Works)
- From Desktop - To OneDrive (Works)
It appears that the formula does work when it is copied from the Desktop instead of OneDrive, which leads me to think that perhaps it is either the link to OneDrive or that the original information was not stored locally that was causing the spreadsheet to crash. I tested this theory by opening the "From Desktop - To OneDrive" workbook from another computer that did not have the link's source saved locally, and
the results were successful!
I downloaded the original workbook onto a different computer and copied and pasted a widget from that computer onto OneDrive, and I was still able to view the workbook, which is excellent!
Results
- The widget is not at fault.
- The formula causes the issue when copying and pasting.
- This issue is only caused when the widget is copied from OneDrive.
- You can still copy from OneDrive, but in order to do so, you will have to delete all the formulas in the original workbook, copy to the new workbook, then rebuild the formulas.
I hope this information helps you and your company in the future should this problem ever arise again!
