the demo below Peter Kalmstrom, Microsoft certified Excel
expert and trainer, shows how to tell Excel to fetch a value
from another Excel file to the current sheet.
This is the process:
Open the two Excel sheets and put them side by side
on the screen. This is easily done if you use the Windows
button + the left and right arrow keys.
Select the cell where you want to place the value
that should be fetched from another file.
Write an equals sign in the formula bar.
Click on the cell that has the value you want to
Now Excel will create a formula that fetches the
value in the cell you clicked on to the selected cell
in the other Excel file.
Press Enter to see the fetched value in the selected
cell, instead of the formula.
Each time the value in the fetched cell is changed, the
value in the cell you selected will be changed too.
Peter uses Excel 2013 for his demo, but you can do the same
with earlier versions of Excel.
when you add a new line in the source file, the row number
of the cell you fetch a value from will be changed. Normally
Excel handles this and updates the formula automatically,
but this is NOT done when you refer between files.
This problem is avoided if you give the source cell a name
instead of using the cell coordinates. Right click on the
cell that has the value you want to fetch and select "Define
name". Give a unique name to the cell, and then follow the
instructions in the demo.
Now you can modify the other cells as you wish. The source
cell will always have the same name, and the value of that
cell will be fetched to the new file.