You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I'm trying to tidy up some spreadsheets which have a "We couldn't get teh data from "Table 20" in the workbook xxxx. Open this workbook in Excel and try again. The problem is I can't find any workbook connections to this table within the current work book and the pop up occurs every time this doc is opened. Any help or pointers would be massivley appreciated! Thanks!
Can you put "Edit Links" into the search bar at the top of Excel? It should open a window which will then able you to see and break the links in the workbook.
Or select "View Formulas" and then search for Table 20 etc in the worksheets.
Regarding Church Group Scheduling, the most complex workbook I have ever seen was for managing a club snooker tournament. Did everything from arrange the knockout ties to work out who'd won. The VBA behind it was spectacular (and slightly broken following a Windows upgrade).
how about,
control F for Find and search for "TABLE 20"
then click on Replace header, and click on workbook drop down, then enter broken link into
from: XYZ
and add correction
to: ZZZ
then click on replace all, ok
It's a bit of a conundrum. Table20 doesn't appear anywhere in the workbook, there are no hidden worksheets, the edit links button doesn't exist, and the workbook links button is greyed out. When you select the change data source for the pivot tables (which I'm sure is where the problem comes from) this doesn't fix the issue. I'll probably just remove the pivot tables and start again. Thanks for the pointers, this has been a real headscratcher.
Try importing into Google sheets and see where the errors take you?
Save it as a CSV. 😁
External inks can appear in things like named ranges and conditional formatting expressions and the usual menu options won't show you them. In a similar situation I found some vba online(*) that went through everywhere that "departmental excel experts" could hide external links.
IMO they should be put into a deep dark hole and never allowed anywhere near a functional spreadsheet that anyone else might ever need to amend. Of course that's referring to external links, not "departmental excel experts". Honest.
(*) Edit - the vba in this thread from stackoverflow.com looks pretty thorough
https://stackoverflow.com/questions/48337861/removing-external-links
Have a look for “very hidden” tabs
Is it a multi sheet workbook? If so then save a copy and progressively remove sheets, save and re-open. That may narrow down where the link is.
Sorry- got interrupted- it’s possible to make a tab status “very hidden” in the view code section. When you click on tabs to unhide sheets, they don’t appear as an option- you have to go back into view code & change the status.
If you know vba sometimes you can use that to find objects you can't using the gui. This sort of code:
https://www.mrexcel.com/board/threads/list-all-tables-in-workbook-using-vba.1169797/
some excellent suggestions... <br /><br />very hidden tabs, that'll be fun
check any charts too. Sometimes a chart is looking for data on a foreign workbook, and that will fire up the 'edit links' error.
As above,
Unhide all tabs
Edit links and break if needed
Check source data for pivots and charts
Check named ranges, there may be old #refs linked to Table 20.
Also check if any macros reference it.
Failing that, kill it with fire 🔥