Google-sheets – Dashboard based on Todays Date – From a table

google sheetsgoogle-sheets-query

I have a list of students with responsibilities and I would like to create a dashboard in Google Sheets with information about the current month.

As an example:

enter image description here

then in September 2020, I want the dashboard to show:

enter image description here

… and also update automatically to Jose and Gina once we are in Oct 2020

Here is the demo sheet: https://docs.google.com/spreadsheets/d/113ZAUJgbzgtjEGai9LkM22h-AEyaPymxUHMnu-RLYEw/edit?usp=sharing

Thanks for any help!

Best Answer

EDIT

As very correctly noticed in the comments, there is a hasty, common, still unforgivable mistake in my original formula.

The correct formula is

=QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(A2)-1&"")

Please do notice that in order to get the current month one needs to subtract 1 month.

WHY?
Because in a query formula, when expressing months in numbers January corresponds to 0
So the order of the months is:

  January       0  
  February      1  
  March         2  
  April         3  
  May           4  
  June          5  
  July          6  
  August        7  
  September     8  
  October       9  
  November     10  
  December     11

Original answer:

You would need 2 formulas in your dashboard sheet

Formula 1
You would first need to put today's date in a cell and refer to that cell to get the current month. That formula is:

=TODAY()

Formula 2
You can now use the following formula to get your results

=QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(A2)&"")

enter image description here

Functions used:

Related Topic