Forum Discussion
Extract specific cell data from multiple files into a new file
Yes, it's possible. I do something very much like that in a workbook I've created to analyze various investment opportunities.
It helps a lot, of course, if those "source files" are all laid out in exactly the same way. So, for example,
City is always in cell B10
State is always in cell C10
Zip is always in cell D10
and so forth
The trickier thing is incorporating in the summarizing sheet the file names given to those source files from which you're wanting to draw the specific cells.
For that you could use the INDIRECT function. Here's an example:
=INDIRECT("["&F2&"]Worksheet1!$A$2")
In that example,
- F2 of the current (summarizing) sheet contains the file name of the source file ("sourcefilename.xlsx")
- A2 of the source file contains the desired info, "New York City"
Excel interprets that formula as if it's saying
- =[sourcefilename.xlsx]Worksheet1!$A$2
- and delivers the result "New York City"
There may be other ways, and other contributors here may suggest more efficient methods. Mine works for me because those source files don't change their names, even though the contents do change. Your situation may require new source file names each time (each new loan applicant, or whatever)...
Let us know if INDIRECT does work, or if it doesn't.
mathetes I had heard of this possible scenario and as you noted, also realized the hurdle of the source files having different names. Hmmm..... I'll try with at least one file to see if I can get that to work and then move on to the next hurdle. Thanx again.
Part of it will depend on what you want that summary file to do, how dynamic the whole system is--how frequently the source files change or whole new sets are created. In my case, I just download fresh (investment related) data but continue to use the same set of file names for a very finite set of source data.
Is it possible for you to post some sample files, both of your intended summary sheet and one or two source files?
- Unfortunately I'm still in the "brainstorming" phase, but the source files/worksheets will be static with fixed cell inputs, its just that the names will change for each file. The source files will contain dropdown options (3-5 to choose), some of which would be choices like "good, marginal, bad, etc..." and from there I'd like to extract key data points for each worksheet and then be able to sort them by the "good, marginal, bad, etc..." ratings.
