Fetch Excel Values From Other Files

My mother Kate has written today's blog post. She had an Excel problem and could not find the solution online, so she asked me for a demo. And of course I obey when my mother requests something!

I know that my demo helped her, and I hope more people will find it useful. You can find it below and in the kalmstrom.com Tips section.

Linked Excel files
When I analyzed an Excel file with kalmstrom.com statistics, I understood that we would get better information if we fetched data from another file. But it was necessary that this data was updated in the statistics file each time it was updated in the source file, so I could not just copy and paste the value.

I tried to write a formula, but I could not get it right. Therefore I asked Peter for help, and I was surprised when he showed me that I did not have to write a formula at all! It was actually very easy to fetch the information I wanted from another file, and now I have seen that it gets updated too.

Side by side
To put two open files side by side in Windows 8, you just have to press the Windows key and place one file to the right with the right arrow key and the other to the left with the left arrow key. It is not necessary to have the files side by side when you to tell Excel to fetch data from one file to another, but it makes it easier to see what you are doing.

Let Excel do the work
Thanks to the capacity of Excel it is very easy to link data from a different file than the current one. Select the cell where you want to place the value and write the equal sign in the formula bar, to tell Excel to calculate. Then click on the cell in the other file that you want to fetch – and Excel creates the correct formula for you!

Excel tutorials
"If I cannot do it, there must be more people out there who need help." This was my thought when I asked Peter to make the video for me like a kalmstrom.com demo.

Now the demo on how to make Excel fetch data from another Excel file is published with a step by step instruction in the kalmstrom.com Tips section, as one of many tutorials on various aspects of Excel: formulas, charts, use with SharePoint and more.

Note: 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.
 Kate Kalmström
Documentation Manager
kalmstrom.com Business Solutions