using an IF command and OFFSET in same formula

Hello All
I have a spreadsheet that allows the booking of rooms (MDT tab); this is a dropdown list.
if room 4 is used then room 4 doesn't appear again until vacant. To make the room vacant I have to delete it manually from the list (room) Column I
This works by using the offset formula as seen in the Data tab.

What I want to do is have column S (MDT) set to complete. once I set to completed I would like this to auto-change the boom to blank (as in delete the room), thus allowing the room to appear again for use further down the spreadsheet

if anyone can provide me with this solution and how it works I would appreciate it.

How about
Clear col B on the Data sheet & put this in B1

Formula:

Please Login or Register  to view this content.

then for the data validation use

Formula:

Please Login or Register  to view this content.

Thank you both very much for these answers this has solved my problem

Thank you for this this is my preferred method. I thank you for the time you have saved me.

Glad to help & thanks for the feedback.