Thread: Track People in multiple workbooks

Our IT department puts out a weekly Active Position Report, which shows all of our agency's positions (there are about 15K of these). So, for each PERSON, there's a Position Number and an Employee Number and a Name. Employee number doesn't change, but position number may (sometimes someone will leave a position in, say, Dallas, to move to a position in Austin). Names also change due to marriage, divorce, or deciding to go by James T. Kirk instead of Jim Kirk, for instance.

I'm frequently presented with lists of people (usually more than 100 at a time, and often only thier name, which may or may not match what's in the current Active Position Report) and asked "What positions did they hold in the last 6 months?" or two years, or whatever. Looking this up takes forever, because I have to go through every workbook and search it for every person. It's driving me crazy.

I was wondering if there would be a way to use a list of Employee Numbers and do a search through multiple (in fact hundreds) of workbooks to harvest the Name, Position Number, and any other information I want from the old Active Position Reports, so that I could create a list which I could then keep updated.

Any help on this would be a lifesaver to me.

JBeaucaire,

Thanks for offering to help.

The Path is:

Path
\\12AUST1001FS01\SHARE10011\Budget\SOBUDGET\Reporting_QA\FTE Reports\All Active Position Report for Budget Analysts

Filenames follow this pattern:

Active Positions 01_06_12.xls

The dates are all Fridays.

I've attached a source file (all names are bogus). One of the issues is that the source files aren't gathered by me, but by another team, so the source data in the source workbook is named either "Data" or "Sheet1", and there may or may not be an autofilter applied.

I'd like to grab a copy (ideally) of the entire line whenever there's a change in any of these fields.

Posn	EmplID	Name	PAC	Unit	Job Cd	Posn Func

I'd like to capture the "As Of" date along with as much of the other information as I can, or at least the fields above.

I'm being pulled away, so may not be able to respond for an hour or so.

Sorry so slow on replying; I'm really getting pulled in a lot of directions today!

The EID is the most consistent piece of information, so the best way to tie it all together.

Ideally what I'd like to do is do a one-time harvest of all the existing workbooks, and gather together all of the lines of an employee when one of the afore-mentioned fields change. I figured the easiest way would be to gather the entire line, but if it works easier to gather only those fields that will be plenty for me. If the whole line, then formatted pretty much the way it is in the sample book.

This would give me a masterlist I could keep updated. Thereafter, every time the Active Position Report comes out, I'd like to run a macro against it that would compare with my masterlist and update it with any changes. Then, whenever I get a request, the data is all accessible in one workbook.

The requests I receive are usually either a list of names, a list of EmployeeIDs, or a list of Position numbers, no particular format.