Value Lookup in Folder of Excel Files

[ecshvac]

Hello,

I am trying to create a spreadsheet that would look up values in multiple spreadsheets nested in multiple folders. We are trying to calculate the total value of projects we bid on. Each project we bid on has an Excel spreadsheet in the project's folder. What formula can I use to look up and return these values on a single spreadsheet instead of opening each project's Excel file and referencing the cell?

The same proposal template is used so the column letter and row number would be same except for the spreadsheet's title name.

 

[Joe4]

Welcome to the Board!

I see that type of solution as being very cumbersome and hard to maintain (especially if dealing with nested folders), and probably will require VBA, unless you want to try to maintain it manually.
If it were me, I would probably look at moving everything over to a database program, like Microsoft Access, and storing all the data there.

Or alternatively, maybe include some VBA code that when you get these proposals back, it writes the data directly to your central tracking file as your add it your file directories.

 

[ecshvac]

I didn't think the solution would be as simple as I hoped!