Hi Steve
See attached solution.
Basically I have used VLOOKUP functions to check what name is in Column C of the "Front" sheet and put that name in the room box on the next sheet.
In addition I have put a small macro that recalculates the workbook when anything changes on the "Front Sheet". The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculation = xlManual
For Each sh In ActiveWorkbook.Sheets
sh.Calculate
Next sh
End Sub
Instead of changing the room numbers, leave them static and just type the name of the person using each room against the relevant room number.
You can view this code by right clicking on the Front sheet tab and selecting view code.
Hope this helps.
If you need anything else then come back to the forum.
Good luck.
Tony

Attached Files

Test.xlsm (16.8 KB, 18 views)