Daily Summary via Lookup to External File

[jws]

Hello,

I am trying to create a summary file that will be used daily for multiple plant summary files. I want to access the shared public drive, which stores the output files in CSV format.

1) Files are dated and stored in a CSV file, daily, for each plant
2) Date format would be plant-mmddyy.csv
3) Public folder would be C:\shared\daily\plant-mmddyy.csv
4) The data that needs to be summed will always be in column B, ranging anywhere from 100 rows through 2000 rows

Since there will be daily sums for each plant, I am trying to create an on-going sheet which will use the naming convention in a summary sheet with date and indirect functions.

Column A – Date
Column B – Plant
Column C - Summary

I was hoping to sum via SUMPRODUCT or SUMIFS, I am just not sure of the best course of action, to start to explore VBA options or can it be handled with a lookup?

If this is unclear, I apologize; I am going to be working on a rough format tonight to see if I can make progress.

Thanks in advance.

 

[jws]

As of now, I am going down the path trying to automate via Access, then link in via external data link; just hoping there is an easier way to maintain with less inputs.

 

[jws]

At this point I have made progress using MS Query - I have pulled the data I want, now the problem becomes making it dynamic.

I am hoping that a SQL Command Text can be based off my excel summary sheet, which would change by day, each day, in mmddyy format.

SELECT `071215`.DESCRIPTION, `071215`.PLANTSUMMARY
FROM `071215.CSV` `071215`
WHERE (`071215`.DESCRIPTION='TOTALS') AND (`071215`.PLANTSUMMARY>'0')

What I am now trying to figure out to meet my original request, would be to have this query run the above command, but use the summary sheet date criteria (which in this example is 071215). As of now, I just have it hard coded into the file. My SQL knowledge is very limited.

thanks for looking