Using data from multiple sheets

[pvwebster]

Hi all, I am attempting to help my wife develop a spreadsheet for use in her Hospital Ward.

1. She will input data on Sheet 1 (checked in cases) which will be updated on she's 2 (WARDS) - that's easy enough and I can do that myself.
2. Data on sheet 2 then need to populate different cells i.e. if Q4= CAT1 then B3 colour is red. I can do this using conditional formatting but to do each one will be extremely tedious given there are about 12 wards and 15 beds in each ward.
3. If a patient checks out (on sheet 1) I would like the data on sheet 2 to be deleted - this isn't essential but would make it a bit easier to use.

II have attached a sample spreadsheet as I may not have explained it properly

Any assistance would be appreciated

Cheers

Paul

[ThxAlot]

Hi, there

Out of respect for medical staff, especially those who fight against coronavirus for the moment, I spent nearly an hour on formulae that fully automate the allocation

All you need to do is to amend or change entries on "Checked In Cases"; then all details will be simultaneously updated on "WARDS". Requirements 1 and 2 are done. Nothing requires changing manually on "WARDS".
As for requirement 3, as long as the "Out Date" of a patient is not later than (<=) TODAY, his/her name vanish from "WARDS" automatically, e.g. Mrs Orange.

[pvwebster]

Thank you ThxAlot. This will assist her considerably and has helped me learn some new things as well. Thanks again

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.