Join Dennis Taylor for an in-depth discussion in this video Locating and maintaining links, part of Excel 2016 Essential Training.
- In this movie we need to have two files open. 09-05 EmployeeTable, and as I press ctrl + tab, 09-05 RegionalSales. When you write formulas that gather data from different workbooks, you can go to the Data tab and see if those exist by choosing an option called "Edit Links". But if it's greyed out, there are no links. However, this can be a little confusing. Links are actually two-way. They've got a source file, they've got a destination file, so to speak.
I'm going to go to the other workbook with ctrl + tab, EmployeesTable. We're going to write a formula here that gets data from the other workbook. Equal, and how do we get to the other workbook? By way of ctrl + tab, or the View tab in the ribbon menu system, switch windows, we'll go over to the RegionalSales file. And click on this cell right here, that's the couch's total, and enter. We've got a linkage formula here that's getting data from the other workbook. On the Data tab here, I'm gonna press ctrl + z to undo that last action.
Keep an eye on Edit Links. It's greyed out. In other words, the formula is not here. In other words, there is no formula linking this worksheet to the other one. I'll press ctrl + y, bring that back, and now Edit Links is selectable. Now it's important to note that when you've got formulas that link two workbooks, the source workbook, if you're looking at it, and I'll press ctrl + tab, nowhere here do we see Edit Links, so what we're saying is that in this workbook, there's no information that tells us that there's a formula elsewhere that's gathering data from here.
Now, possibly, you could stumble across this if you were not the one to have written the formula, didn't know anything about it. If you were to go to this cell and go to the Formulas tab, you can prepare to use Trace Precedents, but how do you know which cell to choose? If I choose Trace Dependence here, it tells me by way of this icon, and I can double-click it, that another workbook has a formula, and there is that workbook, it's currently open, and it's gotta be open for this to happen, another workbook that's telling us that it's getting data from here.
But that's pretty long odds that you would ever stumble across that. Let's remove these arrows here. So there's no link in the source of the formula here. Let's go back to the other workbook, ctrl + tab. We want to follow this a bit to see what happens when we open and close files. So there's a linkage set up here, and Edit Links reminds us of that. When we click Edit Links, we see the other file name. Now, what would happen if this file is closed, we don't see it for a while, we open it a few weeks later, maybe we forgot about that, or maybe we're a totally different person looking at this.
Another person might look at this and say, "I wonder if there are any links to other workbooks here?" I'll go to the Data tab. Edit Links is not greyed out. It's selectable, let's click it. What are we learning here? We know that somewhere within this workbook, there's a link, there's a formula somewhere that's getting data from this other file called 09-05 RegionalSales, but this doesn't tell us where it is. But we can track it down in an unusual way. Let's go back to the formula, now you'd have to know this first of all. Linkage formulas that involve linkage to other workbooks include brackets, and also .xlsx.
How often do you see brackets in Excel? Rarely. Now, that's not to say you couldn't see them, but let's say if we can find the cells with brackets, we're gonna track down those linkage formulas. But imagine if you didn't know where it was. Select all the worksheets here. Right-click any sheet tab, select all sheets, then go to the Home tab in the ribbon, far right button, Find and Select. Choose Find, there's a bracket. Now, I've done this a while ago, so you might see it there, you might not see it there.
Left bracket or right bracket, makes no difference. Then, Find All. It will take us to the first one and potentially show us even more in this list here. In this case, only one, so we've tracked down that formula, and there could be others, although in this case, there are not. So that's how we find them, and that's kind of a runaround to do that. Now, going back to Edit Links on the Data tab. Here's an option: Startup Prompt. These choices are worded a little bit oddly. The last one, "Don't display the alert," and "Update Links," well that could be acceptable in some environments, but don't accept the alert and don't update? I think that's unacceptable, most of the time.
The top choice, even though oddly worded, is the one we want. "Let users choose to display the alert or not." It's almost as if it's asking us every time we open this, it's going to question us again and again, "Do you want to see this alert?" What it really means is it's going to ask us if it wants to update, so that's one we want to choose, let's click OK, and close. So, let's close this file. We can do this by way of ctrl + w, and I will save the changes.
Save, right here. Now, on the other file, meanwhile, and it could be days later, whenever, I'm going to make a change here to cause this grand total to go over 12,000. So instead of 4,870, I'll just make it 5,870. So it's well over 12,000. And I will save this workbook and close it. I can do this also by way of ctrl + w, it prompts me to save, and I'll save it. So it's a later time, I'll go to File open, here's that file that I want to get to, the EmployeesTable, there it is, and here's a prompt, what does it say? "This workbook contains links to one or more "external sources that could be unsafe.
"If you trust the links, update them." I do. Now, as I click Update, watch this number change. It's going to go above 12,000. And there it is. So that helps us keep track of these. I would suggest, in general, shy away from writing links between different workbooks, and yet I do recognize, from time to time, it's important. Remember, if a workbook has links to other workbooks, in other words, if it has formulas that get data from other workbooks, on the Data tab, Edit Links will be selectable.
Remember, if we're looking at the other file, we could go back and open it, we won't see Edit Links at all because it's the source of the formula, it's not the actual formula itself. So a little tricky, maybe, at times, and it's good to practice with this a little bit. And again, what it said earlier still is true, though. Be a little bit wary of these, be a little bit hesitant about writing formulas that link different workbooks, even though, from time to time, they are necessary.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros