Search multiple files and display results

[DAULT]

Ok, so this is what I am facing.

Our office has a job cost ledger workbook for each individual project we are working on. At any given time, there are 300-400 active projects. I would like to create a summary sheet that searches the same cell within all of the files in a folder for the project number i have typed in to column 1 in the summary sheet and display the appropriate information I am looking for. I am using a similar formula elsewhere, however it is based on searching a single specific file and not a range of files. That formula looks like this:

=IFERROR(VLOOKUP($B6,'[Job List.xlsx]Sheet1'!$A$2:$AB$9014,13,FALSE),"")

I would like to be able to use a similar formula, however instead of having to change it for each entry, I would like it to be more automatic.

Thank you!

[TFiske]

You may want to look around in the VBA forum to find a solution for this problem. I've built some sheets using the built in Workbook Connections utility in the Data Tab of the toolbar but nothing as extensive as what it sounds like you're trying to do.

You might be able to play around with it, though: Data>Connections>Add. Maybe you could attach your summary sheet and one or two of the files you are attempting to reference. That way we could at least have an idea of what kind of structure you're working in.

Sorry I couldn't be of more immediate help. Maybe that gives you some ideas to start with.

[MarvinP]

Hi Dault and welcome to the forum,

Excel 2016 has a newer tool called Get & Transform (old name of Power Query) that could pull in all the files in a folder and extract what I think you want. It is much easier than using VBA and you may already have it on your version. Here are a few articles explaining it.

http://excelunplugged.com/2015/02/10...n-power-query/
http://www.dutchdatadude.com/combini...ery-for-excel/

[DAULT]

Thank you for your response. The version of Excel I have does not have the menu indicated in the link provided. I have taken another look at what I wrote and I thought I would provide a bit of clarification.

I have a job list that I use to draw information about the project into a project specific sheet. This is done using the formula I posted, which searches a specific cell in a specific file for the specific information to be displayed in the destination file in a specific cell. We will have hundreds of project specific files that draw from the job list. What I am looking to do now is take some of the information from each of the job files and link it into a summary sheet, however I do not want to have to link each specific file. I was hoping I could use the same formula but replace the specific file reference with a folder reference so that it will search each file in the folder and display the information based on the project number in the summary sheet.

Unfortunately I am not permitted to share the files as they are proprietary.

[MarvinP]

In 2016 Excel the tool has moved to the Data Tab. Power Query was an Add-In tool from Microsoft in 2010 and 2013 Excel and finally built into 2016. They changed the name to "Get & Transform" instead of "Power Query". All the functions are the same. I believe you need/want to learn and use Get & Transform to do your problem.

Watch some YouTube showing this tool.

https://www.youtube.com/watch?v=lDk5dOFe-YE