Jan 03 2022 01:06 PM
Hi all. I'm about a 6 out of 10 on excel knowledge, but learning everyday.
Is there a way to extract specific cell data from multiple files and list that data into a new worksheet?
Example: If I had 10 applications (think of a loan application or applying for an apartment lease), and I wanted to extract specific data from various cells (City, Salary, loan amount, credit score, etc...), is that possible?
Jan 04 2022 06:11 AM
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,
Excel interprets that formula as if it's saying
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.
Jan 04 2022 06:24 AM
@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.
Jan 04 2022 08:03 AM
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?
Jan 04 2022 08:16 AM