Macro to search file location for presence of a specific file

Hello,

I want to design a worksheet, sort of a “dashboard”, that can summarize reporting for multiple employees. I work for a small accounting firm where 3 / 4 different employees are responsible for compiling approximately a dozen reports on a daily basis. Each report is saved in a unique folder with the same file name, except the date is changed/rolled to reflect reporting for the current day.

I’d like to be able to open this dashboard, and for a given report, see whether or not something has been saved down. For example, in the attached sample file, the first report in row 7 is the “Trial Balance”. Column B expresses how the report is saved each day, as “TrialBalance_MMDDYYYY”. Based upon the date in row 5, is it possible for a macro to search for a file with the name TrialBalance_MMDDYYYY in a given directory, and return TRUE or FALSE? E.g. Given a specific location, if the macro identifies a file with the name “ExpReport_12242013”, it will return TRUE in cell C9. If that file name does not exist in the given directory location, then FALSE.

If this is something that could be done, then the macro could look for a fixed file name, with a variable date suffix, in a given directory. The report would then return TRUE if that specific file exists and FALSE if it does not. This would create a useful and quick summary for me that would enable me to see which reports have been completed and which have not.

Thank you for taking the time to read!

-Student1990