A while ago I wrote a tip on how to consolidate two or more spreadsheets together. It occurred to me that maybe some of you were not aware of the simple linking that can be performed that you may find more to your requirements.
Let’s start with the simplest of formulae that links two cells.
- In an empty spreadsheet type your name in the cell A1.
- Select the cell A5.
- Type the following formula. “=A1”
When you press the enter key you will see the result is your name.
You don’t have to type the full text, you can start by typing in the equals sign and then use you mouse to select the cell with the data you want in the new cell. Then by pressing the enter key you have the result of your name. Why you might wish to do so will become clear shortly.
You have linked these two cells. If you ever change the data in the cell A1 it will automatically change in the cell A5 to replicate A1. If you want this same data to be in a different sheet you can have that too.
Having typed in your name in the first sheet in cell A1, select another sheet.
- In any cell type the “=”.
- Click on the first sheet to activate that one.
- Click on the cell A1 with your name in.
- do not return to the second sheet where you started this formula. Rather press the enter key. Your formula will be displayed in the formula bar as ‘=Sheet1!A1’
Once again, the result will be your name once you press the Enter key. This is what it will look like.
An extra Tip – colour coding
I always suggest to my students, on a course, that it would be good practice to have the first entry that will be linked to other cells to have a coloured background. A colour that you will always associate with a linked cell. So that when you see it in your spreadsheet in the future you instinctively know that if you change the data in that cell it will also change elsewhere in your workbook.
Linking sheets and workbooks
If you need to use this formula in your work read the tip at the bottom of this article which I posted on how to simply link and bring together several sheets onto one, with two different formulae. The next step is to link two or more workbooks together. Please be aware that if you link another workbook that is in a different directory to the one you are linking into the formula will read very long.
I have created four new files.
- Total for car Sales of All Branches.
- Car Sales for Windsor Branch.
- Car Sales for Luton Branch.
- Car Sales for Bristol Branch.
Start in the required cell in the Total File.
- Type the “=”.
- Select the first workbook. E.g. Windsor Branch and select the cell B2.
- Hold the Alt key on the left of your space bar. And press the Enter key. This will push you to a new line inside the formula bar and so make it easier to read the formula once completed.
- Type the “+”.
- Select the next file, Luton Branch and select the same cell B2.
- Hold the Alt key on the left of your space bar.
- Type the “+”.
- Select the final worksheet, Bristol Branch and select the cell B2.
- Press Enter on its own to finish the formula and view the result.
The final formula looks like this.
All four files are open.
Once you have created this link you can close the files you do not need to see and work on the total sales file. The formula will change. You can see that if the files are open on your desktop the address line is shorter but if they are closed the formula shows the full path to each file and cell.
If you have a team of people responsible for different areas it means you can design spreadsheets that will automatically combine the data together. One can see progress and the consolidated information is immediately available.