I am working on a school rooming timetable system. I am finding a formula which finds if a particular room is used during a specific period by which class and teacher.
There is a staff overview with clean data of which class and room they are in, alongside preferred results.
https://docs.google.com/spreadsheets/d/1H9Q8K9KBLgqCsvVZ_ekqsLPbZKeMzvldDnyI-KerOD4/edit?usp=sharing
For example, for on the overview, Monday Period 1 (Column B) Staff 1 uses room BG01 for the class 07MA01. Thus on the room overview, it shows that in Period 1, Staff 1 uses BG01 for 07MA01.
return cell from another sheet if it contains string in column
flutters
0
Viewed 25
1 Answers
Use filter() and regexmatch(), like this:
=iferror(
textjoin( "; ", true,
filter(
regexreplace('Staff Overview'!B$3:B, "@.+", "") & "(" & trim('Staff Overview'!$A$3:$A) & ")",
regexmatch('Staff Overview'!B$3:B, $A3)
)
),
"FREE"
)
Related