0

I am currently using google sheets for employee scheduling. On the "Schedule" sheet Row 1 starting from column B I have the employee names listed across the top and column A starting from row 2 has dates. I place an "X" in the cell corresponding to the date and name of the employee if they are on duty, otherwise "OFF", or "LEAVE", are used to indicate they are not on duty. It's a simple schedule since there's only one shift and all employees are full time.

I want to create a new tab "Daily Plan" where I can generate a list of all employees that are on duty on a particular day, based upon the date in cell A2 of the daily plan tab. Basically I'm looking for a formula that will look at the date in Cell A2 of Daily Plan, match it to the date in A2:A of the "Schedule Sheet" find the all the columns with "X" in that row, and return the names contained in Row 1 of those columns. I've tried a combination of if(and( and index(match functions, but can't seem to get it to work correctly. I know there has to be a logical way to do this or perhaps a cleaner way of laying out the schedule.

I've created a sample sheet for better clarification on what I'm trying to do here: https://docs.google.com/spreadsheets/d/1F54iltBk2t6A79jOYp-un7ZE5uN7JrUHmu_LILfvwe4/edit#gid=0

Any guidance on how to get this would be really appreciated.

1
  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Jan 8, 2023 at 12:39

1 Answer 1

0

Use filter(), like this:

=join( 
  ", ", 
  filter( 
    Schedule!B1:Z1, 
    "X" 
    = 
    filter(Schedule!B2:Z, Schedule!A2:A = A2) 
  ) 
)

See your sample spreadsheet.

3
  • Thank you for the suggestion. I think my original description did not fully describe the layout of the schedule tab and daily plan tab. I've edited my original post to include a sample layout linked here: docs.google.com/spreadsheets/d/… Commented Jan 9, 2023 at 9:06
  • Edited the answer per your new requirements. Commented Jan 9, 2023 at 9:20
  • That’s exactly what I was looking for! Thank you so much! Commented Jan 9, 2023 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.