Excel - Getting values from other file

December 2016




Issue


I have 2 xls files (book1.xls and book2.xls). In book1.xls the first column (A1) has certain names in first 10 rows (say x1, x2, x3, x4, ...... x10). The second xls book2.xls has sheets with same name as the names in the column A1 of the first xls (book1), that is, the names of the sheets on book2.xls are x1, x2, x3, x4, ...... x10.

Now what i have to do is to get certain values from book2.xls from the corresponding sheet (in book2) as in the column A1 (in book1), for example: in book1 corresponding to x1 i have to get the column D1 value from sheet x1 from the book2.xls, and in book1 corresponding to x2 i have to get the column D1 value from sheet x2 from the book2.xls and so on .... for each value until x10.
Can someone please help/guide me on this ?

Solution


Consider the following:

In book1.xls in sheet1 from A1 to a10 is like this
x1  
x2  
x3  
x4  
x5  
x6  
x7  
x8  
x9  
x10 

book2.xls has got sheets named x1,x2,x3 etc.
in each of these sheets there is some entry in D1
  • in book1.xls in sheet1 in B1 copy this formula

=INDIRECT("[book2.xls]"&A1&"!d1") 
  • copy B1 down.

Note that


Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « Excel - Getting values from other file » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.