Forum Discussion
How can I prevent an external reference from being created?
Adsf-32 Hey, I can try to help you out with this issue if you can possibly share your file with me and explain in detail.
Thank you for the offer. Though, because of the spreadsheet's proprietary nature I can't share it. And actually, the file has gone through many itinerations over the past few days, so I can't really give you a good clear example.
In the spreadsheet are named ranges, including reference in formulas, and in cell data validation rules. Those are a real challenge to get across.
Then in regards to external links, there are buttons from the form controls, and I found that the buttons cause cross-spreadsheet data links. So, they were not copied, but instead re-created.
I just wish Microsoft would publish an extensive list of how you can unintentionally create these links, and how to locate where they are. Excel is able to be notified of the external link, because it asks if you want to update the information in the link... but it refuses to tell you where that link is hiding. There are few ways to search, that will find SOME of them, but not all. And of course, it would be nice if there were a "save special" that would forbid the creation of external links.
I ended up taking several steps to avoid the link.
1st, I did a save-as from the spreadsheet that I was wanting to copy the data and formulas from. Then, I erased all the stuff I didn't want. That brought a lot of the things I wanted in. But, I still had an earlier spreadshee that I had started from scratch, and I was hoping to get that work into the save-as version.
2nd, from the spreadsheet that I had earlier created from scratch, I was able to copy the formulas, by setting Excel to show the formulas, and then copy/past those cells into Notepad. This would strip all the information about the origin of the data. I the copied that text, and pasted it back into Excel (in the one I just crated with the save-as). This successfully pasted the formulas without creating external links.
3rd. For the buttons that triggered macros, I manually copy/pasted the code for the macros in VBA. Then, manually recreated the buttons.
Finally, I also had to do a lot of re-work, because pasting those formulas, also lost all the formatting info, and the data validation was not possible to copy. But, after a few days of work, I was able to get my new spreadsheet working as I expected.
-Joe
Adsf-32 Just to clarify is your problem solved or Not.
If no you can also share your screen and then I can look into it.
DevendraJain, yes the problem for this spreadsheet is solved.
Though, it took a lot of work ...more than 3 days to accomplish what should not have taken more than perhaps 10 minutes.
In the future, I will just start with a blank spreadsheet. And limit my copy/paste operations to pasting as values only. The trick of showing formulas, and pasting that into Notepad to prevent Excel from creating external references was a very valuable way to reuse formulas. But pasting raw data, or the formulas as text is apparently the limit of what you can do between spreadsheets.
-Joe
