Hello all, it's my first time to post, so thanks in advance for your help and patience as this is a pretty hard request to describe.
I'm am building an Excel workbook that tracks changes to a retail chain's headcount. Headcout goes up and down via new hires and terminations (events) that happen in different locations, different dates, and with different rep types. I receive a transaction log weekly in Excel from our database that outlines each event, which includes the person's name, their ID number, rep type, and date it occurred (The Events Log tab in the attached workbook)
Each retail store has it's own tab in the same workbook (Friendswood, Houston, etc). Each store's tab shows the beginning headcount for each week, terminations, new hires, and an ending headcount for each week with those events figured in...all for each rep type. Terminations get negative numbers as they take away headcount, new hires get positives. I get the values for both of these events to populate in the site tabs by doing a bunch of countifs that pull from the Events Log.
My question here is twofold:
First, I'm stumped and need to have some help writing a process in VBA that would go to each store's tab, look and see if there are any values for Terminations and New Hires for all rep types, and if there is a value there other than zero, it needs to go back to the Events log and pull the name and effective date for the rep that was counted in the countif that populated the cell (ex. at Friendswood (cell B7), there was 1 termination the week of 6/30-7/6 for FT Regular Sales Specialist, I need to have a comment inserted in that cell that identifies the person's name and the effective date of the termination that caused that -1. If that number was -2 or -3, I would need each name and date listed in the comment as well. If the event has a value of zero, no comment needs to be inserted. I need it to do this for every week, every rep type, and for all events (terminations and new hires) I basically want to see who is terminating or brought on as a new hire each week with these comments at each location. Comments have to be used in this sheet to conform to our company's reporting structure (but if you have other suggestions to display this data, I'll certainly listen to it)
Second, I used some pretty primitive Countifs to populate the events in each location's tab, if there is a better/easier way to do it, I'd love your advice.
In reality, in my actual file I am using...I have 151 different location tabs, 5 different events that can happen, 4 different rep types, and 52 different weeks that it all can happen in. I really need something that can be easily replicated to each location's tab, and would have the ability to update itself when new events are added on a weekly basis.
I hope I have been clear on what I need, if not please let me know and I will clarify. Thanks all!!
Bookmarks