Change Roster values

**Bloodywog** · 04-05-2012, 12:30 AM

Hi jaslake

OMG.. Can’t believe it!! That's fantasic jaslake!!! I would never have worked that out without your help. Thank you Thank you…

I attempted to use INDEX, MATCH and LOOKUP last night to work it out. I just have one more question. When I was trying to work it out last night, I thought it be easier if I put the roster for the year and I had 2 more scenarios popped up that I need to add if it can be done. Employees do shift swaps and I need to add Personal Leave.. Will this still work if I added the entire year?

I have attached an amendment worksheet with roughly how I want it to be. I was thinking I have different sheets for O/T, Sick, Personal Leave and Shift Swaps. I’m open to suggestions and anything to stop any troubles that will arise. The "Shift Swap" may be a little difficult but I am happy to enter this manually if the reest are done automatically.

Again.. I really appreciate your help. I can't thank you more.

**jaslake** · 04-05-2012, 01:48 PM

Hi Bloodywog
Try the code in the attached. Please note, there's no error checking.

**Bloodywog** · 04-05-2012, 03:39 PM

Hey.

At the moment everything working great. I can't thank you enough! You have saved me hours of head aches and save tones of time. I really appreciate it!

Thanks heaps mate.

**Bloodywog** · 04-06-2012, 12:09 AM

Just one more thing jastake. The worksheet works like a charm until I add more sheets. A Runtime error 13 - Type Mismatch shows. When I look at the roster, everything gets added then error occurs. I looked into the code and I think we need it to stop looking at all sheets defined in the start of code?

I'm new to excel and slowly learning.

A few things that will improve the sheet if it can be done:

1 - Having the ability to add more sheets without any errors. Maybe define only the sheets i require to update the roster? E.g. Overtime, Sick Leave, Personal Leave, Maternity Leave, Shift Swaps & Annual Leave sheets?
2 - Additional macro to Clear the "X" or to under changes if you make a mistake (if possible)

I have attached a copy of the worksheet. I plan to add additional 5-8 sheets in next few months to send email using macros, planners and create reports and pivot tables.

Thanks heaps for the help. Over the last couple of months it was taking me many hours to manually add data in. This is all done via a macro now and takes just a couple of minutes. Really appreciate it.

Bloodywog

**jaslake** · 04-06-2012, 11:51 AM

Hi Bloodywog
Something for you to ponder...how would you envision this working

Additional macro to Clear the "X" or to under changes if you make a mistake

Clearing ALL the X's would be quite simple; undoing changes to Roster2012 would be impossible unless O is the default since I have no idea what it was before the change. If you wish to change individual records let me know how you'd like it to work.

I'll need to look at it...my time will be limited this weekend due to the Holiday. I'll get back to you.