Overview per day

CarmenTejedaToledo

Hello, I am new using Sheetgo and I would like to know if there exist an option on knowing the attendance per day. The connections already included in the template give overview per student but it is cummulative. I would need one that helps me knowing this overview but per day. Any suggestions? Thanks

Karoline from Sheetgo

CarmenTejedaToledo Hi Carmen, hope you’re great!
Would you need to track this per day and do it cumulative or only when the day is “today”? For sure we can help you but was really helpful if you could give us more details about what you’re trying to achieve

thanks!

CarmenTejedaToledo

Karoline from Sheetgo Hello Karoline, thanks for your reply. That is exactly what I need, to track attendance per day, for knowing in the day if a student is present or absent. Of course, the cumulative option is great, that is what appears in the Overview per student sheet as I understand. But for my school, we also need to know a general overview of the day in which we are. I am not sure if I explain it better now. Thanks in advance.

Karoline from Sheetgo

CarmenTejedaToledo Hello Carmen, thank you for your explanation!
I think I have a solution, but it will require some manual work with formulas at the beginning. I will create some mockup data and reach you as soon as I have it.

CarmenTejedaToledo

Karoline from Sheetgo Thank you so much, that would be perfect. Kind Regards, Carmen

Karoline from Sheetgo

CarmenTejedaToledo Hi Carmen,
Thanks for your patience! I thought in a way but my manager made it pretty easy.

Step 1: Please, create a new tab

Step 2: On the A1 we’ll include a formula to bring all student’s names, so in this case, it will be: =ARRAYFORMULA({“Student Name”;Instructions!W10:W38}) (or W 39-40 depending on how many students you have.

Step 3:On the B1 cell the formula is a little bit complex:

Match - brings up the Column that my vlookup has to return
Indirect - You can create dynamic tab names with ease.
Switch - allows you to map tab names according to month

So, it will be:
=ARRAYFORMULA(IF(ROW(B:B)=1,“Status”,IF(A:A="“,”",VLOOKUP(A:A,INDIRECT(SWITCH(MONTH(C1),8,“August”,9,“September”,10,“October”,11,“November”)&“!A:AZ”),MATCH(C1,INDIRECT(SWITCH(MONTH(C1),8,“August”,9,“September”,10,“October”,11,“November”)&“!1:1”),0),FALSE))))

Step 4: On the cell C1 you include the date =TODAY(). Or, if you need to know some specific date, you can manipulate this formula as you wish (for instance in September you want to know what was the attendance for August 24th you pick your date and" =Today()-15 days"

Step 5: Repeat the steps to all subjects

Please let me know if it worked

CarmenTejedaToledo

Karoline from Sheetgo Hello Karoline, thank you so much for the help. Yes, it works perfectly. Now in each sheet, we have an overview per day of the day we want.

I have a doubt, with the formula included in B1, I will have the information I need only until November right, if I need it to know it in December I must add to the formula the other remaining months?

Thanks in advance. Kind regards, Carmen

Karoline from Sheetgo

CarmenTejedaToledo Hi there,

Yes, you can customize it by deleting and including months, but remember to copy exactly the previous month
=ARRAYFORMULA(IF(ROW(B:B)=1,“Status”,IF(A:A=““,””,VLOOKUP(A:A,INDIRECT(SWITCH(MONTH(C1),8,“August”,9,“September”,10,“October”,11,“November”,12,“December”)&“!A:AZ”),MATCH(C1,INDIRECT(SWITCH(MONTH(C1),8,“August”,9,“September”,10,“October”,11,“November",12,"December”)&“!1:1”),0),FALSE))))

;)

CarmenTejedaToledo

That is perfect. Thank you so much. Regards, Carmen