Forum Discussion
Links breaking when tabs moved
Hi,
I hope someone might be able to shine some light on a very disabling issue I'm having with my workbook.
I have about 200 worksheets and most of them reference values from either within the same worksheet, or from cell references in other worksheets within the same workbook.
Very simple =worksheet name!B4 (or whatever the specific cell reference)
So this all works fine, except I started noticing that sometimes the destination values weren't updating, in fact all the links throughout the workbook had stopped linking!
First check, to ensure formulas hadn't switched to 'manual' in the settings, but they were still set to automatic.
Seems there were two ways to fix this. Either retype the link formula into any linked cell (the exact copy of what was there before) - a magically all the links would come back to life - but this doesn't always work. Sometimes this only fixed the specific link repaired.
The other option was a restart of Excel, and sometimes even a restart of the PC.
After some messing about, it appears that the trigger for this glitch is when I change the position of the worksheets, either by dragging the tabs to a new position of using the right click move or copy function.
I contacted Microsoft support and they 'repaired' my 365 Excel, but it made no difference.
I've also tried removing macros from the workbook (there was only one) and saving it as a regular xlsx file, but it makes no difference.
Any help would be hugely appreciated.
Purple_Octopus This is not easy to trouble-shoot. My hunch is that either your workbook is reaching the complexity limits Excel can handle, or contains some sort of corruption. Or both. I'd love to have a look at that file, are you able (and willing) to share it (privately) by any chance?
Hi Jan, thanks for your reply.
I'll confess that I've never used forums like this before but would feel a bit vulnerable sharing my sensitive business data with a complete stranger.
I think the MS support officer suspected corruption which is why he did an Excel 'repair' but it didn't make any difference.
Perhaps you are correct that I'm simply asking too much of Excel. I had it in my head that a small-time user like me would never stretch it to its limits, but perhaps I'm just creating unnecessary complexities through my limited knowledge on how to use it efficiently.
I was hoping someone else my have experienced the same issue and there might be a straightforward fix. Wishful thinking perhaps!
Purple_Octopus I totally understand your reservations, I would have the same 🙂
It was a matter of professional interest: I was curious which issues your file might contain.
I deal with companies with Excel sheets with problems on a regular basis.
