Moving data from monthly workbooks into summary workbook in same folder

[hyneso]

Hello Forum!

I hope I can find some help for this one! We manage 100's of lakes and ponds, which require aquatic herbicides be applied during the summer. We must record these for EPA reporting and also record them to bill the client $$ for the chemicals used.

We visit each property 10x per year, so the manual reporting is VERY tedious. We have developed a Excel workbook to record the chemicals in the field, filled in by the service tech. They return to the shop and download these onto a shared drive into a client specific folder.

Each workbook could be pre-named and filled in as that service visit is performed.

so how do we get the summary workbook to look into the client folder to see if the cells have been filled in, (which would result from a property visit) and add those values to the summary workbook? Each chemical would have its own, unchanging cell on the workbook as below.

Chemical Ounces Gallons Pounds
ABC 16
GHI 2
NMP 4

this would happen every month (except dead of winter), and we need to update the summary workbook automatically as the workbooks are saved into the clients folder.

thanks in advance!

Mike

[hyneso]

Well that table didn't format correctly.

ABC was 16 ounces

GHI was 2 gallons

NMP was 4 pounds

for example