I'm trying to find out if an individual's has been scheduled in order for another sheet to reference that fact.
The name sheet:
The other sheet looks something like this:
So 'x' show's up for AM if 'Brooke' is scheduled in the AM for that day. But not for the PM if she hasn't been scheduled for the PM yet (in this case she has been, but this is just an example).
Is there a way to do something like check if a string is present in a column only for rows where the preceding cell is a string 'x'? It would need to also take into account merged cells as well.
CodePudding user response：
try:
=INDEX(IFNA(REGEXREPLACE(SUBSTITUTE(VLOOKUP(IF(B40:B="",,
VLOOKUP(ROW(A40:A), IF(A40:A<>"", {ROW(A40:A), A40:A}), 2, 1)&"×"&
B40:B), QUERY(SPLIT(FLATTEN(IF(B5:H37="",,B5:H37&"×"&
VLOOKUP(ROW(A5:A37), IF(A5:A37<>"", {ROW(A5:A37), A5:A37}), 2, 1)&"♦"&
COLUMN(B4:H4)-1)), "♦"),
"select Col1,count(Col1) where Col1 matches '.*AM|.*PM' group by Col1 pivot Col2"),
SEQUENCE(1, 7) 1, 0), 1, "X")&"", "\d ", "ERROR")))
