I am in need of creating a shift roster.

I have a tab on google sheet called Shift Availability containing the availability of staff as below (Y denotes that the employee is available)

I have another tab where I am trying to populate every cell of column 'B' with available employee names from the Shift Availability tab

The cell values should be a dropdown that I can choose from Employee 1,Employee 2,Employee 3,Employee 4 etc based on whether they are available then.

I tried following some articles & writing a VLOOKUP query but not able to get this right. This is first time I am using google sheets & I've used VLOOKUP about twice in my career. Please can someone guide me to get this right?

The VLOOKUP at present looks as below -

=iferror(query(query(ShiftAvailability!A:G,"Select * where A=12/1/2022",1)),"Select Col1 where Col2='Y'")

LINK - https://docs.google.com/spreadsheets/d/1ERlXg8ZDqz9nAwfiaBMuFuMkG5UKQjRQDestgRnQbcI/edit?usp=sharing

CodePudding user response：

Put this formula in C2 and apply to the entire column C:

=TRANSPOSE(IFNA(QUERY(TRANSPOSE(ShiftAvailability!A:F),"select Col1 where Col"&MATCH(A2,ShiftAvailability!A:A,0)&"='Y'",1),))

Set the drop down list as follows:

• Cell range: B2:B
• List range: =C2:2