Hi peeps...

It's my first serious foray into Excel tinkering (and this forum, so "Hi everyone!"), so please try to be patient...

Okay here's the challenge:

I work in design production where we have to track ad material statuses from the sales reps. The material comes in, we design the material, the work is placed into an 'Out For Proof' directory on the server and later moved to a 'Complete' directory once the ads have been returned with changes and approved by the client.
Mid-week I create a report that details any missing material for this week's publish so the sales manager can rattle the sales rep's collective chains and push them to get their work in on time to production. It also serves as an invaluable tool to track late ads, find 'repeat offenders' in the sales dept. and any national material that may have fallen through the cracks.

I would do most of this manually each week, referencing a rudimentary electronic publish from our ad order entry system and using the OS find function to search the ad storage server for a unique ad number identifier. If the ad number was not found, the ad didn't exist and was therefore not handed in for work.

The manual report would take me over an hour most weeks. And needed to be done again later once we got closer to our press deadline. Not ideal.

SO! I've created a custom report in an ad database system we have at work that exports the content of a weekly ad package report. The export is returned as an Excel spreadsheet.
I remove unwanted columns to reveal the bare necessities for the document, add a blank column for importing my data at A and a blank column in C for the result.
Column A will contain a current list of ads in a Mac server (our 'Out For Proof' directory content). Column B contains the ads that are supposed to be scheduled to run this week from the ad system. Column C contains the formula:


I open the 'Out for Proof' directory on the file server and copy the folder list (folders containing ad components, but titled with a unique ad number), then paste the content into column A.
The C column returns a match if there are duplicate ads in the list.
I then sort data by column C, colorize them in red for an alert color. Sort them again by the sales rep code column and email the report to the sales manager.

It works quite nicely but it would be AWESOME if there was a way where I didn't have to import the folder data manually. The idea here is to have Excel somehow monitor a server volume dynamically, so whenever I opened the spreadsheet, the data would update according to the content on the server.

One other thing to consider... The client software will be running on Macs, which means no Visual Basic! Ha! Gotcha!

I've searched this forum and the Interwebz and found nothing close to this.

Anyone up for the challenge?