Google Sheet: Admission System in a hospital-like Facility.

I assume that all of this will work on google sheets, if not perhaps someone that knows google sheet formula syntax will be able to make the conversions.
1. Put the bed statuses into two columns bed no. and vacant
2. The vacant column is populated using: =IFERROR(INDEX('BED ALLOCATION'!H$2:H$16,MATCH(C2,'BED ALLOCATION'!G$2:G$16,0))>0,TRUE)
This assumes that if there is a date in the date of discharge/release column that the bed is vacant.
3. The "VACANT BED POOL All Blocks" column is populated using: =IF(ROWS(A$1:A1)>COUNTIFS(D$2:D$897,TRUE),"",INDEX(C$2:C$897,AGGREGATE(15,6,(ROW(C$2:C$897)-ROW(C$1))/(D$2:D$897),ROWS(A$1:A1))))
4. The data validation dropdown on the bed allocation sheet has the source: =OFFSET('BED STATUS'!R$2,0,0,COUNTIFS('BED STATUS'!R$2:R$897,"?*"))
Let us know if you have any questions.

I should have used one of the following Excel formulas in column D on the bed status sheet:
=IFERROR(INDEX('BED ALLOCATION'!H$2:H$16,MAX(IF('BED ALLOCATION'!G$2:G$16=C2,(ROW(H$2:H$16)-ROW(H$1))),1))>0,TRUE)
Note that the formula above is array entered which means that it is not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
=IFERROR(INDEX('BED ALLOCATION'!H$2:H$16,AGGREGATE(14,6,(ROW(H$2:H$16)-ROW(H$1))/('BED ALLOCATION'!G$2:G$16=C2),1))>0,TRUE)
I don't believe that there is an AGGREGATE function in google sheets, and not sure how array functions work in google sheets. Hopefully one of the other contributors can help with that.
Let us know if you have any questions.

If you would like to see a count of the beds that are occupied and vacant by block then you could:
1. List the blocks in cells L4:L8
2. Use Vacant and Occupied in M3:N3 respectively as column headings.
3. Populate the vacant column using: =COUNTIFS('BED STATUS'!$D$2:$D$897,TRUE,'BED STATUS'!$B$2:$B$897,$L4)
4. Populated the occupied column using: =COUNTIFS('BED STATUS'!$D$2:$D$897,FALSE,'BED STATUS'!$B$2:$B$897,$L4)
Going back to the topic of array formulas, linked below is an article on using them in google sheets. If I understand correctly then the first formula from post #4 could be modified to read:
=ARRAYFORMULA(IFERROR(INDEX('BED ALLOCATION'!H$2:H$16,MAX(IF('BED ALLOCATION'!G$2:G$16=C2,(ROW(H$2:H$16)-ROW(H$1))),1))>0,TRUE))
Note that the formula above is untested and may need modification.
https://support.google.com/docs/answer/3093275?hl=en