help with verify scheduling

Hello

I am in charge of creating the schedule for my department. I have attached a template here.

as you can see I use initials and quite often probably just because of my incompetency I tend to forget to put someone down on the schedule.

How can I build a check or macro to ensure that I have accounted for everyone at work

thank you in advance

for more clarification and as an example in the attached template I am missing "A" on Dec 1 and "M" on Dec 8th

Ah, so I intentionally left a few folks out as I was trying different ways to see how to create a formula or macro that would alert me to their absence.

It's pretty simple - if you are not off or not on call then you need to be covering one of the services. So A or M in this case needs to be assigned to a service on the respective days.

I am not looking for help with assignments. I am only looking to make sure that all my people are accounted for each day. All I need is a pass (all names accounted) or fail (someone missing- so I can take a second look and figure out who is missing). I seem to be terrible at making sure I have all initials covered for each day. I have even resorted to writing them down on a piece of paper and scratching off each name as I schedule them for each day.

Hope this explanation helps

.
I can provide a solution but it will require partially changing your roster layout.

First: need to have a list of ALL employee abbreviations. A, K, H, TA, VK, etc. etc.

Second: some modalities have the possibility of assigning more than one technologist to that area. Say for example FLUORO could possibly have 3 technologists assigned on a given day.
Rather than have one row for FLUORO, I need you to change that to three separate rows for FLUORO. Look at the other modalities and do the same for each. If MAMMO could possibly have two
technologsits assigned, give MAMMO two rows ... the same for the other modalities as well. The formula solution needs to have each technologist displayed on a separate row, rather than combining
several technologists as you presently have ( ECS/V ... P/ECS/JH ... M/JH ... etc.).


Here is an example of what I mean :

	A	B	C	D	E	F	G	H
4		MON	TUE	WED	THUR	FRI	SAT	SUN
5		27-Nov	28-Nov	29-Nov	30-Nov	1-Dec	2-Dec	3-Dec
6	OFF	-	NA	NA	NA	NA
7	OFF					VK
8	OFF
9	OFF
10	OFF
11
12	Body CT	TA	G	P	A	M
13	Neuro	K	K	K	K	K
14	Neuro Supp.	JH	./.	./.	JH	./.
15	Body MRI	VK	V	H	H	V
16	MSK MRI	H	VK	H	H	V
17	Ultrasound	G	JH	VK	P	JH
18	Fluoro	./.	ECS	VK	A	ECS
19	Fluoro		V
20	Fluoro		TA
21	PET/CT	A
22	IR
23	Swing Shift 10-18h
24	Swing Shift 4-8 PM
25	ON CALL RAD	M	M	M	M	P	P	P

Ok great that's fine... I can do that

1) all 12 employee initials: V, H, M, G, VK, ECS, K, JH, NA, A, P, TA

2) only need two rows for General Reading, I already have two for Neuro - rest I can manage with just one entry

thank you
neel

.
Thank you for your service to our Veterans !

that's great...I see how you used the countif function...one point in time I could do these simple tasks in excel but have since lost those skills.

Two questions

1) how does the cell highlight red if its zero?
2) Curious, how do you know I work for the veterans?

thank you for your help
neel

.
1 - Its done by Conditional Formatting. Just another formula that tells the cell to turn red when a Tech ID cannot be located in the column.

Click on Q6 (don't change anything in the cell, just click on it)
Then click on : Conditional Formatting (menu bar up top), then Manage Rules, then Edit Rule

You'll see the formula setup in the small window that appears. Again, don't change anything.
Once you tell Excel what the logic is in the formula, you can assign a color fill to display
if the logic is present. In this case, if the number Zero ( 0 ) appears in the cell, color it red.

2 - When I was working with your original file, there is a notation in the Save As window / Author that
indicates it belongs to the Dept of Veteran Affairs.

Side Note - Also, I recently retired from Radiology. 20 years. Started as tech and retired as Director of Imaging Services at a large
medical facility in South Carolina.

You are welcome. Glad to help. I would be interested in assisting you with future projects as well. Nice to dabble in xray again.



ps: From past experience I would recommend you maintain a copy of the file in a separate location just in case the original gets contaminated (somehow they all do).
It will save you alot of headaches cuz you won't have to re-create the wheel.

awesome and small world...I am a radiologist, prior military and now serving the veterans.

will surely reach out if I need help again.

thanks

.
You are welcome.

Cheers.

Hello,

hope you are doing well

I have been using the schedule with great success....Thanks for you help again.

I was wondering if you would be able to help make it even better.

If a rad is in the OFF column for the day, can we create an alert so that I do not put that radiologist on the schedule for that day?

thanks again

.
JeteMC : Nice job ! I tried to find your macro or formula that generates the alert ... but unable to . What have I missed ?

homer77 : My apologies for being late ... I just now received the notice of your request. Sometimes the FORUM is s...l...o...w sending out the emails.

.
Gotcha. Didn't think to look at Data Validation (obviously).

Good show.

ok great that works...:I just changed the message to state "This RAD is OFF on this day"

can I build a similar error msg for a situation in which where I have already made the schedule and someone calls off. I noticed that if I then fill the Off cell then it does not prompt me to remove that person from the daily schedule - so if I forget to manually remove them I could potentially still have them listed in OFF and on the schedule. I don't think this would be that big of an issue but think it should work the other way also, correct?

thanks a lot

nm, I just tried it and works....this tool is great!

Hello, just curious if the data validation tool can be used somehow to count the rads and make sure all are accounted for? Right now if someone is not on the schedule or off status then the corresponding box on the right shows a value of 0 and highlights red. Should I simply stick to this or is data validation an option?