Forum Discussion

JParker54's avatar
JParker54
Copper Contributor
Jan 03, 2022

Extract specific cell data from multiple files into a new file

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?

Formulas and Functions

4 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jan 04, 2022

    JParker54 

     

    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.

    • JParker54's avatar
      JParker54
      Copper Contributor
      Jan 04, 2022

      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.

      • mathetes's avatar
        mathetes
        Silver Contributor
        Jan 04, 2022

        JParker54 

         

        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?

Resources