List contents from specific text in another worksheet

[JaSonic75]

Hi Guys

I've created a sample worksheet which:
On sheet1, is listed by date and driver name, with the drivers status for that day, ie working etc.

I would like on sheet 2 if its possible to list the drivers according to the date specified but only if they are 'Working'

I've tried hiding using conditional formatting those that are not working, but that leaves blank cells in the list which is what I am trying to avoid.

Is there a way of creating a list of drivers who are 'Working' only on sheet2 without leaving blank cells?

Many thanks.

[sandy666]

You can try with PowerQuery (Get&Transform)

[samba_ravi]

a5=INDEX(Rota!$2:$2,SMALL(INDEX((INDEX(Rota!$B$3:$U$22,MATCH($C$2,Rota!$A$3:$A$22,0),)<>"Working")*10^10+COLUMN(Rota!B2:U2),0),ROWS(B$5:B5)))

Try this and copy towards down

[shukla.ankur281190]

Try

A5

Please Login or Register  to view this content.

copy down!

[AliGW]

Try this:

=IFERROR(INDEX(Rota!$A$2:$U$2,SMALL(IF(Rota!$A$3:$A$22=Working!C$2,IF(Rota!$B$3:$U$22="Working",COLUMN(Working!$B$2:$U$2))),ROWS($A$1:A1))),"")

... 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 your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Then drag copy down.

[JaSonic75]

Please Login or Register  to view this content.

When adapting this to my actual spreadsheet, this worked a treet, Thankyou very much.

Marking forum as solved.
+1 to you reputation

Many thanks

[AliGW]

You're welcome!

For future reference, please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.