Checking Worker Availability

Hi guys,

I'm in a bit of a pickle. I recently started a new job, but the previous worker here wasn't exactly... Apt... with Excel, so everything was very basic, manually input etc. I've managed to clean it up for the most part, and add in a few very useful macros and formulas. Now what I'm hoping to do is this.

We have shifts come in from residential homes that need covering (These vary all through the day, all different times). So what is in place at the moment is that when we get the shifts, we text them out to our worker pool and wait for them to get back to us.
What I wanted was for my excel sheet to automatically check the workers available days compared to the day the shift is needed and for it to produce a list with said workers. Therefore providing me with a list of workers who possibly could work that day, this will cut down the amount of texts I have to send out and therefore reduce cost.

I hope this has explained things OK, and I'm really sorry if I have posted in the wrong place!

Thanks in advance.
Solomon

You have a couple options, but I think they would all involve using an IF statement.

Explain how you have their availability schedule in Excel.

Correct me if I'm wrong, but you are attempting to consolidate your workers' availability for the week?

This may seem complicated, but it's not that bad. There may even be an easier way to do it. This is what comes to mind...

Let's say you note an employee's availability by Y or N under the day they are or are not available. You create two cells, off to the side somewhere. One is Y and one is N, or whatever you actually use to make distinguish the worker's availability.

If available on Monday, make a note in cell B2, if not make a different note in cell B2. If available on Tuesday, make a note in cell C2, if not make a different not in cell C2... This is why you created the two cells: Y and N.
So, if Bob is available on Monday, then we see that in cell B2, there's a Y. I placed my Y and N cells off at B 100 and B101. So, IF(B2=B100,'Worker Availability Sheet'! B2= Yes, No). Something like that.

You write the statement once, lock in your source data (using $ sign in front of the column or row, depending on what you need to lock), then drag it out for the week. Then copy it for each worker.

Good luck