copy from one cell to another in one of three subsheets

First off...I am not a tech. Not even close so I need small itty bitty words. I am trying to consolidate my patient care worksheet load (I am a nurse), and it involves superhuman thinking. I heard there was a lot of that here. My main sheet is where the data is entered by a human and it will remain the refernce forever. Based on the contents of the cell the M column, I want the contents of the cell in the A column to populate in the next available cell on one of the 3 subsheets. If m = D then go to dawn teamsheet, if M = C then go to coast teamsheet, if M=S then go to Sequoia teamsheet. I also want that same data to delete itself (that sounds like dividng by zero to me...told you I was not a tech) when the data disappears from the main census sheet. Take a look at room 834...you will see superman is in there...I want his name to populate in the next available cell on the Dawn teamsheet and erase when it disappears on the census sheet. It seems like an easy task, but my mind is blown. Here is my workbook so far...no laughing, and yes I did it in crayon.

I press control shift enter and nothing happened...was there supposed to be a dialogue box pop up?

Now we are getting somewhere. I tried to copy that formula to the a column of the Coast teamsheet and change the D to a C, but excel would have none of it. All I got was #VALUE in the name box. I have tried 7 ways from sunday clicking cntrl, shft, and enter...nothing happens. I know I am frustrating to deal with, and I apologize for that, but I cannot emphasize enough the depth of my excel illiteracy. I would think that formula would work if I copied it over and changed the letter...also, is it possible to leave the name box blank if there is nothing to put in it?