I work for a physician office and we keep track of which patients are ready in a room and which doctor goes in to see that patient. We use a google spreadsheet to track this information. I want to have a cell at the top of the spreadsheet labeled for each room 1-10 highlight(green) when a patient is in a room, and again highlight a different color(purple) if doc is in a room.
We record a patient in a room next to the pt name on spreadsheet with the letter "r" (for ready) with the room number next to it. For example "r1" designates that a patient is ready in room 1.
We designate that a doc is in the room by changing the "r" to "i" (for in) with the doc's initials. For example "i1jd" would mean Dr. John Doe is in room 1 with the patient.
We also use "d" next to a room number to designate that the patient and doctor are done and no longer in the room, for empty rooms the room number at top of page may remain white.
We have up to 5 doctors per day with a column for each doctor's scheduled patients. This would be helpful to keep the room numbers locked at the top of the screen so that at a glance we can see which rooms are occupied and which are empty without walking down the hall to see each room.
I have tried this formula:
but I can only get it to look for the text in C1 and can't get it to look for a partial match such as i1jd.
I found that formula on a different forum.
Here is a link to an example spreadsheet: https://docs.google.com/spreadsheets/d/1NS6iJGvLsxPS9G0oHzHed55uLXt2YClgpTWIJWVqDaE/edit?usp=sharing