Hello! Need help scanning columns for particular text to fill a list

Hi all!


I have a roster with one last function i'm hoping to get working with no success. I have to present this in 12 hours so I"m panicking haha

I have attached the spreadsheet where you can see the columns as dates and the rows as staff member names.


Need a little help with a function to scan a column (a particular date) in SHEET1 and extract all the cells that contain "X" or "R/O" to then pull out all the staff surnames into a list into the "team sheet". This column is to show who is available and is unrostered (marked by "X" and "R/O")

My "team sheet" shows the extracted staff names that had "X" and "R/O for any particular date (column is date). I am hoping to automate this .

Thanks so much in advance! Any advice appreciated.

I am a little confused by row 73 of sheet 1... This looks like a pointless duplicate of row 74. Why is it there? Your explanation was not fully clear.

This array formula will give you the result that you want:
=IFERROR(INDEX(Sheet1!$C:$C,SMALL(IF(Sheet1!D$4:D$50={"X","R/O"},ROW(Sheet1!D$4:D$50)),ROWS($1:1))),"")

copy across and down.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

Thanks for the Reputation that you have added. It's much appreciated. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

The forum does do funny things, quite often!!

Anyway, I'm glad you're happy!!