Travel Scheduler

I'm trying to create a travel schedule that checks against people's days that have been requested off. I have two sheets, one for scheduled travel and one for days requested off. The travel sheet has 5 travel spots in a row and a list of event dates in the leftmost column.
xxxxxx Spot 1 | Spot 2 | Spot 3 | Spot 4 | Spot 5
Event 1
Event 2
Event 3

The days off sheet is travel dates in the leftmost column and employee names in the top row. If an employee wants to mark a weekend as 'off', they place an X in the corresponding cell.
xxxxxxxxxx Abe | Bill | Cate | Dave | John |
08/12/2014
10/21/2014

Let's say John has the weekend of 10/25/2014 marked as off. I want to have some form of feedback (conditional formatting of a color maybe?) that lets you know they have marked the day off when you enter "John" into any spot (1-5) for the event taking place on 10/25/2014.

Sorry if this isn't very well explained or in the wrong forum, I'm new to all of this! Thanks in advance.

Hi sead16

Welcome to the forum.

I have a solution that is not quite what you asked.

My thinking is that it is better to know that John is not available before putting his name on the rota, rather than "I'll put John down - Oh no, he's not available."

Anyway have a look, and if you still want it as you described - I may be able to do something.

Regards
Alastair

Thanks for the response! I like that idea, but this list is going to have around 100 people on it, so that won't be as easy to look through.

I had another thought; what about a column that has all the names of people marked as off? How would I go about writing a formula to return names for any person marked with an X for that week?

Hi sead16

Yes, I agree that 100 names would not be a good list to have to look through.

I have written a worksheet change macro (right-click on the Events sheet tab > View code) which calls a macro (Alt+F8 . Step into)

If you have not used macros before, you may have to adjust your macro settings (File > Options > Trust Center > Trust Center Settings > Macro Settings)

I do not know the formula for returning names for any person marked with an X for that week. I could write a macro, but you may run in to the problem of too many name to cope with.

I note that you mention "Week". The macro has been written with days rather than weeks in mind. Doe this cause a problem?

Regards
Alastair

Thanks! This looks like it could be just what I'm looking for. Using days is fine instead of weeks, we usually just write the date of the Friday (since the events are over the weekend) to denote when you need to mark as "off".

I'm fairly inexperienced with macros though. I've gotten the code open and am looking at it, but when I mark Dave as off for 10/21/14 it doesn't change his name in the Events spot to red. I'm sure I'm missing a very basic step or something. Any tips on where/how to learn macros? I think they'd be very helpful to know!

Thanks again for your help so far!

Hi sead16

There are lots of books to assist with macros (or so they say - I've never read one! ) My own learning has been trial and error (and there have been plenty errors! ) and reference to this and other sites. A useful way of starting is to use the macro recording facility. Most of my problems have been with the syntax, rather than the concepts.

Speaking of which, the concept of the macro I wrote is that the macro is started when a name is put on the events sheet. Now you tell me you also want it to react to new entry on the Days Off sheet. This is not what was asked for in your original post but when I get a few moments, I will see what can be achieved.

Regards
Alastair

Haha, sorry for that! I think I might still be doing something wrong. I re-opened the document, and placed Abe and Bill in the first two slots for the 12/31/14 event and it didn't turn Red. Maybe I'm missing something.

Thanks!

Hi sead16

Missing something? No probably just doing it wrong (and I thought it was idiot proof )

No idea why it will not work for you - it works perfectly for me. Have you checked your macro settings?

Anyway - here is version 3. It will (sorry "should") now react to either a change in Event dates or a change in Days Off.

I have annotated the macro to try to explain what I am doing.

Regards
Alastair

I think the issue may be that I'm running Excel 2011 on a Mac. Could that be the issue?

Hi sead16

Sorry - I have no experience of Macs, but I do hear that they have some issues peculiar to them.

Regards
Alastair

Hi sead16

I am not sure whether there is anything on this site that may help.

http://www.rondebruin.nl/mac.htm

Not having a Mac, I cannot comment.

Regards
Alastair