1

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!

1

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:

| improve this answer | |
  • Your solution is much better than mine. Thanks! – Tizz Aug 14 at 0:09
  • 1
    This was giving me the month ahead of today(), so I added an EDATE -1 and now its working properly for me ... =QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(EDATE(A2,-1))&"") – Tizz Aug 14 at 0:19
  • No need to complicate things using the EDATE function. Just use month(A)="& month(A2)-1&". – marikamitsos Aug 14 at 0:25
1

I found a way to do it myself. Only took 4 hours :(

=Index(Responsibilities!$B$2:$B$7, MATCH(DATEVALUE(CONCATENATE(MONTH(today()),"/1/",Year(today()))),Responsibilities!A2:A7,0),1)
| improve this answer | |
  • Great work. You can try my solution as well. I believe it is simpler. – marikamitsos Aug 14 at 0:10
  • Yours will auto fill with one formula, I will have more than these 2 columns so its nice, but if I dont want to have cells next to eachother, I will have to break up your solution as well. – Tizz Aug 14 at 0:14
  • Since it is a query you can rearrange the columns and even omit some according to your needs. – marikamitsos Aug 14 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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