Let me apologise in advance to those that want to read about the exploits of ‘the boy’ but I’ve been documenting more work-related items here recently, mainly so that I can find them again later.
I’m currently working on a large project and the stage we are at requires multiple people to fill in a lot of data on an Excel spreadsheet. One of my tasks is to combine everyone’s work back into a single file, which is no big deal. The problem is that someone used a formula to gather some of their data and I made the mistake of copying their tab directly into the master file, instead of copying and pasting values.
Now when the file is opened it warns about referencing external data and I’d prefer that it not, plus there’s always a chance that something changes in the linked file. Rather than step through the many tabs looking for the offending cell(s) I turned to Google. It turns out that there’s no way to actually do this in one step but with a little effort you can work your way through it.
The first step actually worked for me, as this was a simple VLOOKUP function, but I’ll link the Microsoft article here as there’s much more that can be done.
I opened the destination workbook, which is all I actually had, and did Ctrl-F to open the Find dialog box. Select Options then enter [ in the ‘Find what’ field. We do this because external references will always be enclosed in square brackets. Then set the ‘Within’ to Workbook, ‘Look in’ to Formulas, and then click Find All.
For me this returned over 100 cells with a Formula, one long column of VLOOKUPs. Eliminating them is as simple as inserting a column, copying the cells with the formula, and pasting values into the new column. Delete the old column, save the file, and re-open to check whether the warning goes away. Rinse and repeat if necessary.
Microsoft’s article has the following choices:
Find external references that are used in cells
Find external references that are used in names
Find external references that are used in objects, such as a text box or shape
Life in a Northern Town 