Forum Discussion
"mother" cells linking with "source" files in excel
Hi, will try to describe the problem that i met in excel
There is a "Mother" file which collects data from several source files. Source files has the same structure and data always filled in the same way, same cells (for example if we have File1.xlsx and File2.xlsx and ... then cell C1 in all files has the same meaning but not the same value)
Now in Mother file in A1 i enter name "File1.xlsx" and then in B1 should apear value from File1.xls tab "sheet1" cell C1. If it would be done manualy formula would look like:
=+'[File1.xlsx]sheet1'!$C$1
In Mother file in A2 i enter name "File2.xlsx" and then in B2 should appear value from File2.xls tab "sheet1" cell C1. If it would be done manualy formula would look like:
=+'[File2.xlsx]sheet1'!$C$1
Question: is it possible to write simple formula that "[File1.xlsx]" part would be taken from Mother file A1 and then linked to correct cell in source file.
BR
DJ
6 Replies
- HI Dalius,
it may be worth you looking into Power Query to do this sort of data consolidation.
What version of Excel are you using?
How many files and how many rows / columns are you referencing?- Dalius J.Copper Contributor
Hi, Wyn,
Can You tell me more about Power Query?
I use Excel 2013.
At the monet i think there could be 12 columns. Rows number is equal to source files number, and it is hard to say but do not think that there wil be more then twenty (20).
Hi Dalius
You can download Power Query here
https://www.microsoft.com/en-au/download/details.aspx?id=39379
Here's a demo of how it can be used
http://myexcelonline.com/blog/consolidate-multiple-excel-workbooks-using-power-query/
- Detlef_LewinSilver Contributor
Hello Dalius
It is possible. You need INDIRECT(). INDIRECT() requires the source files to be open.
- Dalius J.Copper Contributor
Hi, Detlef,
I would prefer that data would be taken when source file is also closed :)
I tried INDIRECT() function but i did not like how it worked.
- Detlef_LewinSilver Contributor
Hi Dalius
Then don't bother with INDIRECT().