Links are basically formulas that get data from another worksheet and therefore link them.
To create a link:
Once you have your two or more worksheets ready with you, say, for instance, Revenue sheet, Sales sheet, and Price Sheet, follow the steps:
- Select a cell in Revenue Sheet where you want the actual sales figure from the Sales sheet and type '=' and then go to the Sales Sheet and select the cell where the figure is and press Enter.
- The sales figure will appear in the associated cell in Revenue sheet and the formula bar will show the path to the cell in the Sales sheet where the figure is taken from. For Example ,
This formula is a link and the Revenue sheet's cell is a linked to the cell of Sales sheet.
Building a Formula by typing
You can also create links by manually typing the formula.
In Step 1 above, after typing '=' also type the formula which has the following format:
Using link tabs on a trust center
In newer versions of Excel, it might happen that due to a trust center setting, the automatic update of links is disabled and when you open your Revenue worksheet it will show a security warning. To use links over such trust center settings and for the links to bypass them, the following steps need to be followed:
- Click on the "Options" button besides the security warning. Microsoft Office Security Options dialogue will open.
- Click on "Open the Trust Center" and then go to "External Content".
- There, select the option to enable automatic update for workbook links.
Opening Workbooks with Links to Closed Workbooks
We have so far learnt how to create links to other worksheets using various methods. In excel, it is also possible to link two different workbooks using formulas and references.
As shown in the above image, you can simply type the above formula, or click a cell in another workbook to insert a cell reference. Now if you happen to open the workbook that has the formula, after updating the worksheet that has the data, Excel will prompt you if you wish to update the current worksheets with the new data. Click on ok to update the workbooks.
Dealing With Missed Linked Workbooks
Sometimes it happens that two workbooks are linked together, and you happen to delete the workbook with all the data. In this case, if you open the Excel file, excel prompts you that the links could not be updated. You have two choices, either you can click continue or you can click on the "Edit Links" button. If you have access to the missing file, you can point Excel to that file after clicking on Edit Links button. In the dialogue box that open, click the Change Source button to the edit the source path of the linked workbook.