method for posting and tracking staffing changes?

[kinikisu]

The spreadsheet I'm working on must work in Excel 97. Taxpayer money doesn't grow on trees

I have a list of staff and their supervisors and my goal is 1) track changes so they can be counted and reported and 2) Show the results of most recent change (Ex: Johnny has had 3 supervisors since January and his current supervisor is Susan.)

If the formulas used for this are complicated (I'm thinking they would need to be) I need any easier way of inputing data so it doesn't take a lot of training, expertise or even time to enter new changes.

So far, I haven't been able to come up with a formula or method for this that is not extremely complicated but I'm thinking that's because I don't know what all's out there or how to use it.

Any suggestions and/or insight are welcome at this point. I'm really just looking for a place to start.

Thanks!

[6StringJazzer]

A lot also depends on how you have your data organized. For example, I would recommend having a column for staff, a column for current supervisor, and a column for the effective date. The effective date could be entered manually. Then you can probably do almost everything you want with a pivot table on another sheet.

Some other types of questions (who is Johnny's current supervisor) could then be answered with straightforward formulas that can be prepopulated if you know a priori what type questions you will need to answer.

If you want a greater degree of automation (i.e., update date is entered automatically) you need VBA code, but it is probably simple if you use the approach described above.

I'm not clear on your requirements. You said

1) track changes so they can be counted and reported

What are you counting? What data need to be reported? Changes for each staff, each supervisor, totals, since an arbitrary date, this month, all of the above?

2) Show the results of most recent change (Ex: Johnny has had 3 supervisors since January and his current supervisor is Susan.)

That is 3 changes, not the most recent change. What exactly do you need there?

[6StringJazzer]

Attached is a prototype of what I'm talking about. This uses a pivot table for reporting, but may not give you exactly the format you would like. But it's really easy to use. The data is on Sheet1 as I described above. Currently the table on Sheet2 shows dates that Addison was assigned to two different supervisors. You can filter on dates, staff, and supervisors.

[kinikisu]

thank you for your ideas.

I did some hashing out and ended up using a marriage of INDEX and MATCH to report the data I have set up in a log type of thing which was sorted with the most recent date first, which I am using track changes. I guess it will be important to only add changes to the top of the list from here on out. I'll share this here in case someone else has the same question.

If the person was not on the log, I assumed their supervisor had not changed so I asked Excel to report their original supervisor (B2), which was provided in my original dataset along with employee names.

Here is the Formula: =IF(ISNA(INDEX(Change Log!D$1:D$36,MATCH(A2,Change Log!A$1:A$36,FALSE))),B2,(INDEX(Change Log!D$1:D$36,MATCH(A2,Change Log!A$1:A$36,FALSE))))

I used COUNTIF in another column to tell me how many times the person appears on the log, thus reporting how many times the supervisor had changed.