link cells from different worksheets in order to populate indexed values

What I would like to accomplish pertains to the last 2 pages – “Night Charge RN” and “Night Second RN”. The top page “Census Sheet” is the data entry page and preferred reference page. When this book is done every other page will populate either directly or indirectly off of the top page –“Census Sheet” (Sheet 3103). You will see on both of the last worksheets column D and E reference the cart name and room number respectively. Because the last names of the patients on those sheets is truncated to only show the last initial, I cannot link the name with the respective columns on the “Census Sheet”. I would like to find a way to populate the room number and the cart initial into these columns from “Census Sheet” or sheet 3103. On the “Census Sheet” sheet the cart name is column N or 14 and the room number is column A or 1.

in column E of "Night Charge" and column C of "Census Sheet" you will see the solution I came up with. Now I have the issue of the error trap not working. Can anyone figure out a way to remove the "Bed #" error? Is there another error trap I could use?

I always feel humbled when someone presents such an eloquent solution...and shamed...why did I not think of that?