How to link to values in another Excel 2010 worksheet or workbook

In Excel 2010 or 2007, if you want data from one worksheet to be linked to, or shown in another worksheet then follow these steps:

1. In the sheet you want to link to (source) select the cell where the value is stored.

2. Right click and select Copy.

XL10Link1

3. In your main sheet (target), select the cell where the value is to be displayed and right click on it.

4. In the displayed context menu, under Paste Options, click on Paste Link (the last icon to the right).

XL10Link2

5. The value in the source sheet will now be reflected in the target sheet. Notice the link to sheet1 in the formula bar.

XL10Link3

6. Every time the source sheet is updated, the target sheet will also be updated with the new values.

7. The same procedure can be applied to link to data in another workbook.

 

Create a hyperlink to an existing file or web page

To create a hyperlink to an existing file or web page, follow these steps:

  1. In the Insert menu click Hyperlink.
  2. The Insert Hyperlink window will open.
  3. hyperlink1

  4. Under Link to to the left side click Existing File or Web page.
  • To link to a file, Under Look in select Current Folder, then select your folder or file from the list to right.
  • To link to a file that you have recently used, click Recent Files and then click the file you want to link to, from the list to right.
  • To link to a web page, click Browsed Pages and then click the Web page you want to link to, from the list to right.

Applies to: Excel 2003

How to find links in a workbook

There is no automatic way to find links used in a workbook. However links use brackets [ ] to enclose the source workbook name, and it also uses the exclamation mark at the end of the sheet name in the link. So you can search for one of these two symbols depending on what type of links you are looking for.

If you are looking for external links only, use the ‘[‘ . If you are looking for all links, external links and links within the same workbook, then use ‘!’.

Examples of links:

1. =Sheet2!$C$1
2. =[Book2]Sheet1!$C$1

Link 1 above is a link to a worksheet within the same workbook, while link 2 is a link to an external workbook.

Steps to find a link:

1. On the Edit menu, click Find. the Find and replace window will open.
2. Click Options to expand the window.
3. In the Find what box, enter “!”.
4. In the Within box, select Workbook.
5. In the Look In box, select Formulas.
6. Click Find All.
7. Your links will be listed in the box at the bottom of the Find and replace window.
8. To select the cell with a link, select the line that contains the link.

findlink1

Applies to Excel 2003

How to link to values in another worksheet / workbook

    If you want data from one worksheet to be linked to, or shown in another worksheet then follow these steps:

    1. In the sheet you want to link to (source) select the cell where the value is.
    2. Right click and select Copy.
    3. In your main sheet (target), select the cell where the value is to be displayed.
    4. Right click and select Paste Special.
    5. When the dialog box appear click on the Paste Link button.
    6. The value in the source sheet will now be reflected in the target sheet.
    7. Every time the source sheet is updated, the target sheet will also be updated with the new values.
    8. The same procedure can be applied to link to data in another workbook.

    Applies to : Excel 2003