JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,687
- Office Version
- 365
- Platform
- Windows
I suddenly started getting this error message on a workbook I have been actively using for more than week. I did some searching and came up with this useless page from M$FT.
Find links (external references) in a workbook
The first paragraph says:
Linking to other workbooks is a very common task in Excel, but sometimes you might find yourself with a workbook that has links you can’t find even though Excel tells you they exist. There is no automatic way to find all external references that are used in a workbook, however, there are several manual methods you can use to find them. You need to look in formulas, defined names, objects (like text boxes or shapes), chart titles, and chart data series.
The statement I underlined is pure M$FT arrogance or stupidity or something. Excel knows there is a problem, so they obviously know what it is and where it is. Then they have the gall to tell me that there is no automatic way to find the problem. It goes on to suggest a stupid manual way.
So I did a little more searching and came up with this page:
Control when external references (links) are updated
It's also a muddled mess, but it contains the basics of a solution. The trick is to open up the list of sources. You do this my clicking on Edit Links in the Queries & Connections section of the Data tab. There I found that the link was to a UDF in one of my add-ins. The add-ins are in a different folder on Win 10 than they were on XP. All I had to do was click on Change Source… and then navigate to the new location of the add-in. Now everything works.
But I have 2 questions:
Find links (external references) in a workbook
The first paragraph says:
Linking to other workbooks is a very common task in Excel, but sometimes you might find yourself with a workbook that has links you can’t find even though Excel tells you they exist. There is no automatic way to find all external references that are used in a workbook, however, there are several manual methods you can use to find them. You need to look in formulas, defined names, objects (like text boxes or shapes), chart titles, and chart data series.
The statement I underlined is pure M$FT arrogance or stupidity or something. Excel knows there is a problem, so they obviously know what it is and where it is. Then they have the gall to tell me that there is no automatic way to find the problem. It goes on to suggest a stupid manual way.
So I did a little more searching and came up with this page:
Control when external references (links) are updated
It's also a muddled mess, but it contains the basics of a solution. The trick is to open up the list of sources. You do this my clicking on Edit Links in the Queries & Connections section of the Data tab. There I found that the link was to a UDF in one of my add-ins. The add-ins are in a different folder on Win 10 than they were on XP. All I had to do was click on Change Source… and then navigate to the new location of the add-in. Now everything works.
But I have 2 questions:
- Why did I get the error message all of a sudden after working with that workbook for over a week? Why didn't it fail the first time I opened it on Win 10?
- Is there any way I can fix this on all of my other workbooks without opening each one and repeating the procedure above?
