It could be evidence of corruption. I recently had to remake an entire workbook from scratch in a new file. And linked workbooks seem to one of the most frequent causes of corruption in my experience. Probably the only thing I’ve seen cause more corruption is programmatically opening and modifying invisible instances of Excel. (No idea WHY that ruined so many workbooks but it did.)
If I made a list of top 10 things that I hate when people do in Excel, this would be on it, somewhere in the middle.
10,
9,
8,
7.
6.
5. Links to outside workbooks.
4.
3.
2. What I call “Pinball Logic” *
1, Nested IF statements
- Pinballing is what I call it when the logic (and cell referencing) flows left and right and up and down within a spreadsheet. Well designed spreadsheets work linearly left to right or top to bottom.
I just gotta work with what I have. And that is multiple outside linked workbooks.
To be fair, keeping it all in one workbook would be stretching the capabilities of Excel. This input data should all be stored in SQL somewhere but that’s for me to solve in a future quarter when I have time.
I definitely work with pinball data. “So this is calculated in a second workbook… then pulled into this one… but you’re then also calculating the data from the same inputs here…”
I’m doing a lot of refactoring.
I avoid linked workbooks because they scare me. Most of the heavy-lifting workbooks I create/use use connections to SQL. Starting to lean PowerBI.
- Merged cells
2 Likes
What, did you want to use VBA on your workbook EVER? Sorry, it has a merged cell.
1 Like
List addition APPROVED!
1 Like
A colleague referred to one process involving a bunch of linked workbooks as “a series of Excel spreadsheets duct taped together” which was apt.
I had been making the argument for years that we needed something better. At one point I demonstrated to auditors how easy it would be for me to defraud the company of millions of dollars due to these spreadsheets. They were unmoved.
I think about 15 years after my initial push they finally did something more secure and robust with them. I was no longer with the company.
Stop right there.
While it seems like an awesome tool in Excel, it is not. It’s a “sex machine” as the recent NSFW GIF stated.
1 Like
Hey, give me a week and I’ll rework this all into a better solution.
As it stands I just need to cobble together some shit that functions for this go-round.
I do wonder about your “about to go corrupt” guess. I had a workbook that was linked to a tabular SQL database that was doing squirrelly stuff. After fiddling with it to try to get what I needed, it finally acknowledged it was corrupt. If that’s the case, you might not be able to delay improving the process.
One other approach I inherited and works ok. Large segmented data (multiple copies of same data for x segments), lots of formulas to apply segment by segment. 1 Formula workbook, multiple data workbooks. Macro opens formula wkbk, 1 of the data wkbk, copies in data, computes formulas, save formula wkbk as unique to segment name, repeat. The formula wkbk has summary ranges, and summary ranges saved to aggregate workbook. Reviewers like the ability to examine any detail element.
Okay I don’t know if this is answerable
But WHYYYYY does Excel freeze at something basic like “Please close this workbook without saving”??
I have one workbook that needs another open for me to develop in it. But when I try to save the first workbook with the second still open, or close the second so I can save the first, they just freeze and spin…
After 4 crashes I’m just letting it run over the weekend to see if it saves.
I usually project a persona onto excel which helps me cope with its antics. Sometimes excel is having a bad day and/or mad at me for all the things I made it do.
1 Like
It finally saved after saving from about 2:30-6:30 and I called it good, I no longer have to interact with the Demon Workbook.
Maybe the workbook had just found out her husband was sleeping with his secretary!?
1 Like
RNN
Unless this is a deep cut to me posting about my neighbor who cheated with his secretary like 2 years ago, in which case, impressive memory
I’m definitely not that impressive… just trying overly hard to project an amusing persona onto your excel
Have you tried uninstalling and reinstalling the application?
Have you tried turning it off and turning it back on again?
2 Likes