I take it you plan to have many worksheets, that is, the Front Sheet plus one for each patient. You envision each "room" cell in the Front Sheet searching through the patient worksheets looking for the corresponding value in B5, and (if it finds it) dynamically plugging in the patient name there. Is all that correct?

I've never done that with lookup functions, and I strongly suspect it isn't possible. If someone else here tells you "sure, just supply the range of worksheets in this syntax....", then by all means try it. But until that happens, I'm going to claim that the only way to do it is by writing your own worksheet function, or some other kind of VBA program within the workbook.

Now, I enjoy that kind of thing; but most people find the thought intimidating, and if you don't want to tackle it I won't sneer. But if you're already a programmer then this won't be hard for you; and if you're already thinking about being a programmer, or at least about trying it out to see how it goes, I encourage you to give it a whirl. Let me know, in that case, and I'll coach you.