Keeping track of ambulance call rotation

[medic2medic]

I'm trying to create two buttons in an Excel document to change the position of cells in a list as a way to keep track of call rotation for an ambulance service. One button would be for emergency calls and a second for transfers. I would like to be able to put the list in the correct order when the shift starts and click either "E-CALL" or "TRANSFER" to change the order according to the call rotation. I'm just not sure how to set up a macro to move cells. Can anyone help with the setup? Here is how I have it set up so far:

Thanks!

[NoSparks]

You can't put a macro into that picture.
Care to post an Excel file ?

And out of curiosity.... what determines what?

[NoSparks]

Hello medic

Looking at your picture, is it reasonable to think it shows is a normal, no one out on a call situation?
Total of 7 crews, 2 not available due to being out of service, next E-Call goes to crew 01, next Transfer call goes to crew 03 ?

Question is, what do you expect to happen when E-Call or Transfer buttons are clicked ?
I would assume that particular crew, either 01 or 03 is removed from being in the line up for either Medical or Transfer until they return, is that right?
What's to happen if it's 20 E-Calls in a row?
Is your intent to store the crew out on call in the Out Of Rotation section until they are available again?
What do you have in mind for putting them back into each rotation? Another button?
What about a crew that takes a call and becomes available before a previous crew?
Where would they fit back into the Medical and Transfer lists?

I don't think what you're after is too difficult but it's impossible without knowing what the plan is.

[medic2medic]

NoSparks,
Yes, this is the normal setup for when we come on shift. 5 ambulances in this order for call and transfer. Our call rotation is set up so that when a truck is dispatched to a call, they are then dropped to "the bottom of the stack", if you will. Example would be Medic01 is dispatched and then becomes 5th for call, while Medic07 is now up for call, etc. If Medic07 is dispatched to a second (or the same) call and returns to service before Medic01, Medic07 is still 5th for call. We go off of order of dispatch instead of order of the unit coming back in service.

Say all trucks are on calls and Medic01 was the last dispatched, yet returns to service first, they are up for call until the actual "1st up" truck returns to service. In this instance, some manual tweaking to the rotation would probably need to be done and that's not a problem.

As for transfers, when a truck, Medic03 for example is dispatched for a transfer (which will generally last anywhere from 2-4 hours), they are out of the rotation. When that unit returns to town and is back in service, they become last for both call and transfer (provided a second truck has not taken a transfer after them). Essentially, every truck would be listed in the Medical and Transfer list, unless they are on a transfer. When they return, they would fall back into the bottom of the stack.

If there's 20 E-Calls in a row, it would be hectic lol. We generally run 15-20 calls and transfers total a day, so there isn't many times where there would be complete confusion to who is where in the rotation.

Basicall, I would like to be able to click "E-Call" and move the first unit in the list (in this case, Medic01) to the bottom of the "E-Call" list. As for transfers, I would like (if possible), to move the top unit in the "Transfer" list to "out of rotation" and possibly another button or way of returning them to the bottom of the "Transfer" and Medical" list when they return.

I don't know how much of this is possible and only have some experience with Macros, etc. in Excel. If you have any ideas or pointers, they would be greatly appreciated.

Thank you,
Kyle

[NoSparks]

Hi Kyle

I think this is what you're after.

Hopefully I've commented the code enough that you can follow and see what's happening.
I'm sure a real programmer would do it differently, but....

The first thing I did was remove all the merged cells.
While these can make things look good,
they are trouble waiting to happen and should be avoided like the plague.

Any questions or queries, let me know
NoSparks

[medic2medic]

That's PERFECT NoSparks! I knew it could be done, just didn't know how to go about it. Thank you so much!!!

-Kyle