how to remove external links from excel

I sent a private email to Jan Karel, but thought others might benefit to see what I discovered.

-------

Before sending the excel to you, I decided to remove a lot of stuff that I was sure would not be the issue, since they were recently added.  But, as I did so, I realized that I could do so incrementally, and perhaps narrow the problem down.   I continued until I only had one cell that caused the problem.  It was a Data Validation cell. The FindLink.xla program could not find it.

 

Perhaps the creator of FindLink.xla could modify it to see these types of links.  But if not, I wonder if a program could be created that would systematically delete portions of a spreadsheet.

 

1) Save as Test.xls

2) Delete half the tabs , save as Test1

3) Open Test1, and see if the error occurs.

   a) if no error, close Test1, then re-open Test and delete the other half of the tabs. 

   b) If error, delete half of the remaining tabs.

4) Repeat the above cycle, until only 1 tab  remains.

5) Do a similar process for the rows of the spreadsheet, then the columns, until only one cell remains, which is the problem cell.

 

There would need to be some method to keep track of the cells, so that at the end, it could say what tab and cell reference was a problem.  I also imagine there may be multiple cell reference problems.  So, I guess my steps a and b, both Test.xls and Test1.xls files should be checked for the external reference error.

 

 

-Joe Dunfee