A common query we have relates to linking cells between WORKBOOKS and why links to other excel workbooks always looks at same cell.
The issue is with how Excel sets up the link.
Default Relative vs Absolute ($ signs in formula)
As shown below, when you link to another SHEET i.e. you:
- Click in a cell and type =
- Go to another SHEET and click on the cell
Excel creates the link and makes it relative (note no $ signs in the link) by default. In this case, if you now copy the formula across it will correctly change where it looks and look at the correct matching cell.
Linking to another WORKBOOK
However, if you do the exact same thing but link to a different WORKBOOK, Excel’s default action is different.
Below note that we are linking Workbook (1) to workbook (2). Notice that the formula now is using absolute referencing ($ signs in front of the letter and number).
If you leave it like this, when you copy it across, all the months will look at January instead of looking at the matching month.
This is just the way Excel does the links. If you link to a different WORKBOOK, make sure you remove the $ signs if that is what you want. There are some shortcuts to working with the dollar signs. You can see them on the free Excel Fundamentals course we offer which explains the $ signs and how to get them to do what you want.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.