I wasn’t sure where to put this, but I fugured I would get a better response in GQ than elsewhere.
Here’s the scenario: I have a massive Excel workbook filled with oodles of formulas and links that all feed the first sheet in the workbook, which is a sort of summary page. I update this workbook every morning with data that I download from our host system and send the info to a group of people in our office.
To save resources and bandwidth, I don’t send the whole workbook. Instead, I have a second workbook that contains only the summary sheet. I copy the cells from the big monster workbook’s summary page and paste the (values only) data into the small one-page workbook and then email this li’l guy out.
My problem (Excel vets can see this coming) is that I accidently forgot to paste values only one morning and instead just did a regular ol’ paste. I didn’t realize my mistake until it was too late to undo it, so I repasted the values only.
So now, every time I try to save this little workbook I get error messages saying “not enough memory” and “unable to save external link values.”
So my question is this: How in the heck do I remove the external link from this little workbook? I don’t see the option anywhere. I have the option to change my source data, but nowhere do I see an option to remove the link completely. Can anyone help?
To remove a link you’ll need to get rid of the formula that references the link.
Since you’re pasting values, that might not be your issue.
Here’s a theory I’d like to throw out. Range names.
I had a file that had multiple tabs (call it “Big Ass File”) with multiple range names. I took a tab in that worksheet, made a copy of it, and started a new file (call it “Simple sheet”). That tab had no formula that referenced anything off that tab. Yet whenever I opened “Simple sheet”, it asked if I wanted to update links. I couldn’t figure out how the link had been created. It was just a simple chart for data entry, no convoluted formulas, or anything. Yet it was linking to “Big Ass File”!!! Arghghgh!
Turns out that when I copied the tab to “Simple sheet”, it brought along range names with it. So I had a range “airfare” that was referencing “Big Ass File”. That was it. So I deleted the range name. Lo and behold the link was gone!
So, I have no idea if this is what your issue is, but it’s a super secret problem I’ve stumbled on in the past. YMMV.
D’oh!
I’m slapping my head right now. You are my new hero, scout.
I had a couple database ranges defined in the original workbook, which apparently carried over to the other workbook. I went into the Define Name dialogue box, deleted all my range names, and bingo! problem solved.
I can’t believe I didn’t think of that. Thanks!
Yay!
I saw you replied and was fully expecting to find that you tried it and it didn’t help.
What a pleasant surprise. Woo hoo!
:: struts away, feeling like an Excel wizard ::