how to automate attendance sheet

[Farooqui Noor]

1) I am using Office 2013 and want to generate an automatic attendance sheet in Excel. In cell F2, I have the month written in a dropdown format. Similarly, in L2, I have the names of subjects, also in dropdown format. Below, in the table, dates and days are mentioned, and "A" is used for absent and "P" for present using a formula. However, the problem is that when I change the month in F2 or the subject in L2 using the dropdown list, the "A" and "P" disappear from the table below which is perfect. However, if I click on any cell, the previous formula is visible in the formula bar. If I delete this formula after changing the month or subject, the attendance for the previous month in that cell vanishes. Whereas, I want a fresh table for each month or each subject, where I can input daily attendance.
2) Similarly, in Sheet 3, I need to fill the summary table using formulas, detailing each subject for every student.

AUTO ATTENDANCE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1		P	A
2					Month	September		Start Date	1-Sep-23		Subject	TEN
3								End Date	30-Sep-23
4
5													microsoft office 2013
6						Today's Present	11		Total Students
7						Today's Absent	1
8						%			Total Lectures	9
9
10
11						Date	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20
12		%	present	absent	total lecture	name	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed
13			8	1		Alexander	P	P		P	P	A	P	P	P		P
14			9	0		Olivia	P	P		P	P	P	P	P	P		P
15			7	2		Ethan	A	P		A	P	P	P	P	P		P
16			9	0		Sophia	P	P		P	P	P	P	P	P		P
17			8	1		Liam	P	P		P	A	P	P	P	P		P
18			8	1		Ava	P	P		P	P	P	A	P	P		P
19			8	1		Noah	P	P		P	P	P	P	A	P		P
20			9	0		Isabella	P	P		P	P	P	P	P	P		P
21			9	0		Mason	P	P		P	P	P	P	P	P		P
22			9	0		Mia	P	P		P	P	P	P	P	P		P
23			9	0		Lucas	P	P		P	P	P	P	P	P		P
24			9	0		Amelia	P	P		P	P	P	P	P	P		P
25
26
Sheet1

Cell Formulas
Range	Formula
I2	I2	=DATEVALUE("1"&F2&F3)
I3	I3	=EOMONTH(I2,0)
G6	G6	=COUNTIF(G13:G24,"p")
G7	G7	=COUNTIF(G13:G25,"a")
J8	J8	=COUNTA(G13:Z13)
G11	G11	=I2
H11:Z11	H11	=IF(G11<$I$3,G11+1)
G12:Z12	G12	=TEXT(G11,"DDD")
C13:C24	C13	=COUNTIF($G13:$AK13,"p")
D13:D24	D13	=COUNTIF($G13:$AK13,"a")
Q13:Q24,J20:O24,J19:M19,J18:L18,J17,J16:K16,L14:M17,O13:O19,N13:N18,M13,K13:K15,J13:J14,G16:H24,G13:G14,H13:H15	G13	=IF(AND(TEXT(TODAY(), "mmmm") = $F$2, $L$2 = "TEN"), $B$1, "")
G15,N19,M18,K17,J15,L13	G15	=IF(AND(TEXT(TODAY(), "mmmm") = $F$2, $L$2 = "TEN"), $C$1, "")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R13:AK24	Expression	=R$12<>""	text	NO
F12:AK12	Expression	=F$12<>""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=Sheet2!$A$1:$A$12
F3	List	2023,2024
L2	List	=Sheet2!$C$1:$C$6

AUTO ATTENDANCE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Sr. No	Roll No	Enroll No	Name of student	MEM		TEN		MWM		BEE		EME		SOM
3					Total Lecture		Total Lecture		Total Lecture		Total Lecture		Total Lecture		Total Lecture
4					Lectures attended	Lect attended in %	Lectures attended	Lect attended in %	Lectures attended	Lect attended in %	Lectures attended	Lect attended in %	Lectures attended	Lect attended in %	Lectures attended	Lect attended in %
5				Alexander
6				Olivia
7				Ethan
8				Sophia
9				Liam
10				Ava
11				Noah
12				Isabella
13				Mason
14				Mia
15				Lucas
16				Amelia
17
Sheet3

 

[Farooqui Noor]

You're welcome. When you upgrade to 365 creating this can be easier. And I"m sure I've missed a few easier methods in this as well.
Thanks for your feed back and Best Wishes!

Sir, in this regard, I would like to request if you could provide a simpler method. If possible, could you do that? Also, it would be better if you use the following format for data entry. if possible, because following format is much easier for data entry.

Mr Excel Farooqui Noor.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	sr.no.	enroll no.	roll no	name of student	date	1/9/2023	1/9/2023	1/9/2023	1/9/2023	1/9/2023	1/9/2023	1/9/2023	2/9/2023	2/9/2023	2/9/2023	2/9/2023	2/9/2023	3/9/2023	3/9/2023	3/9/2023	3/9/2023	3/9/2023	3/9/2023	4/9/2023	4/9/2023	4/9/2023	4/9/2023	4/9/2023	4/9/2023
2					subject	MEM	BEE	MWM	TEN	MPR	DME	SOM	PPR	EEN	MWM	BEE	MEM	DME	SOM	PPR	MEM	BEE	MWM	TEN	MPR	DME	SOM	PPR	SOM
3			1	Name1		P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
4			2	Name2		P	P	P	P	P	P	P	A	A	P	P	P	P	P	P	P	P	P	A	P	P	P	P	P
5			3	Name3		P	A	P	P	P	P	P	A	A	P	P	P	P	P	P	P	A	P	A	P	A	A	P	P
6			4	Name4		P	P	P	P	P	A	P	P	P	P	P	P	A	A	P	P	P	P	A	P	A	A	P	P
7			5	Name5		P	P	P	P	P	A	A	A	P	P	P	P	A	A	P	P	P	P	A	P	A	A	P	P
8			6	Name6		P	P	P	P	P	A	A	A	P	P	P	P	A	A	P	P	P	P	A	P	P	P	P	P
9			7	Name7		P	P	P	P	P	A	A	A	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
10			8	Name8		P	P	P	P	P	P	P	P	P	P	A	A	P	P	P	P	P	P	P	P	P	P	P	P
11			9	Name9		P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
12
Sheet1

 

[awoohaw]

I think it is very unwise to input data in worksheets that are reports and then try to get other reporting in other worksheets based on that.
And what is so hard with filtering to the day you need for the class you need and having all the students listed in front of you. And then all of that data feeds into all of your reports automatically: I could not use xl2bb on this because the hidden rows of the filter are counted as part of the html object (i am not sure if that is the correct word). But, look at this, why is this hard?

 

[Farooqui Noor]

I think it is very unwise to input data in worksheets that are reports and then try to get other reporting in other worksheets based on that.
And what is so hard with filtering to the day you need for the class you need and having all the students listed in front of you. And then all of that data feeds into all of your reports automatically: I could not use xl2bb on this because the hidden rows of the filter are counted as part of the html object (i am not sure if that is the correct word). But, look at this, why is this hard?

View attachment 99247

No worries, sir. I just wanted to say that I found the format I sent earlier to be a bit easier, so I was hoping I could do data entry in that format. But it's okay, sir. Thank you for your valuable time. I appreciate you giving me your time.

 

[awoohaw]

No worries, sir. I just wanted to say that I found the format I sent earlier to be a bit easier, so I was hoping I could do data entry in that format. But it's okay, sir. Thank you for your valuable time. I appreciate you giving me your time.

If you say so. Instead of searching for the date column and class you just click the filter button. But, I understand you are comfortable with that.

- Best Wishes!