I'm new to this company and currently there are two functions to one specific
operations area - one is to do the work and the second is to QC the work and
the QCs are pretty extensive. Currently the work queue is listed by job name
in salesforce so that's where the new jobs are entered, a pool of people work
on the jobs and then report the work is done. Once a job is done another
entire group of
people QC the work the first group did. Basically the queue of job is ever
changing so the first group just keeps looking for new jobs to work on and
the QC team keeps looking for jobs to QC. The QCs are recorded 100% in Excel
- one job per workbook. Each workbook has at least 5 worksheets but all the
information I need is exactly the same place on a couple tabs, always the
named the same thing. Then my boss has been manually consolidating the QC
results by looking in salesforce to see what jobs were done (the job names
are unique so no two will ever be repeated), opens every associated workbook,
basically copies and pastes the QC results in a CSV file which he then
uploads to salesforce. Once all the QCs are done for the week, he then
exports the data out again so he can chart it. It's clear in the long term
they're trying to do more and more in salesforce but for the time being it's
a completely time intensive process so what I'm proposing is to have Excel do
more of the heavy lifting. I can export the list of jobs for the previous
week from salesforce and I want to drop that data into Excel then have it
programmatically go pull the data from the fields I need in the associated
workbooks so I can simply create a macro to create the CSV file which can
then be uploaded.

If I lost you the important thing is I have a list that looks
something like

QC Record : Job Name : Listing Count : Date
201002-18485-QC : HD-11311-TA031110 : 263 : 3/15/10
201003-15737-QC : SHT-100312 : 103 : 3/14/10
201002-18327-QC : 032110Mag : 246 : 3/17/10
201001-18510-QC: TSA-100321 : 411 : 3/15/10
....

Since I know the job name I know there's a file that exists in a folder
called \\de-mt1\clients\QC Reports\Proofs Adjusted\. The file will be called,
for example, \\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110
proof adjusted.xls where HD-100311-TA031110 is the only piece of information
that will ever change and it will always be the job name. From the "Upload"
tab I need to start grabbing the data from cell A2, B2, C2, D2, and so on.

List of job names is 100% dynamic and the list varies in length from
probably 50 to hundreds in their peak season.

--
Jen