Query Formula Google Sheets Date & other conditions

user_9817355 0
Viewed 41

Hello Brilliant People!

I have a spreadsheet with the following column headers: student ID, student name, Monday, Tuesday, Wednesday, Thursday, Friday, 9/13, 9/14, 9/15...9/30. The dates are populated with A for Absent and P for Present. The M, T, W, Th, F columns are populated with the course the student has for second period on that specific day (it's different every day).

I would like to query this data to return the following:

If today were a Monday, it would return the student ID, student name, and the course the student has for second period (Monday column) and the just the students who are absent for that date.

If today were a Tuesday, it would return the student ID, student name, the course the student has for second period (Tuesday column) and just the students who are absent for that date.

Same for W, Th, & F. Tried lots of different things, but nothing seems to work.

Thank you in advance for any help!

google-sheets-formula
1 Answers

Try

=query({Dashboard!A1:U},"select Col1,Col2,Col3,Col"& (MATCH(today(),Dashboard!1:1,0)) &",Col4,Col"& (MATCH(choose(weekday(today(),2),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"),Dashboard!1:1,0)) &" where Col"& (MATCH(today(),Dashboard!1:1,0)) &"='A' ",1)

in this formula

  • (MATCH(today(),Dashboard!1:1,0)) will give you the column where you can find the date,
  • and (MATCH(choose(weekday(today(),2),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"),Dashboard!1:1,0)) the column where you can find the day of week.

take a copy of the spreadsheet here

user_150193800
Related