Lookup cell for documents in a folder

[Marc Trumpi]

I am trying to create an overview of the time spent on projects. People hand
in weekly time-charts in excel format where the projects are referenced by a
standard number. So in effect I have a lot of separate excel files from which
I would like to create a summary, which also updates when a new file is added
to the folder.
I have no idea if this is possible and if it is, how one should accomplish
it. Can anyone help?

 

[JLatham]

There's a lot of information that would be needed for a complete solution,
but I'll outline generally what would be needed.

This would have to be done with VBA code (a macro). At some point the code
would read the contents of the folder where all the weekly files are and
determine if any new file had been added. This would require that the
summary file keep a record of files previously seen, probably on a sheet
dedicated just for that purpose. If any new file(s) were found, the code
would continue on to either build formulas to link to data in them needed for
the summary or work through the files in the folder, opening and reading the
needed data from them and placing it into the summary book.

I said "At some point..." and you have choices on how that would be done.
You could actually set the code up to run automatically when the workbook
opens, or you could set up a 'button' on a sheet that you could click to
update from the individual files. Since this could be a lengthy process,
that may be the way you want to go.

Like I said, there's a lot of info needed to create the macro, just a little
of which would be: the naming convention for the weekly files, the sheet
names involved in both the weekly files and the summary workbook, cells
involved in all of the sheets in both workbooks, etc.