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

 

[awoohaw]

My opinion (and just that) is that you're trying to put data entry into a view that is pleasant to understand like a report (although this isn't technically a report) combined with formulas for ease of functionality. That is difficult to manage.
Have you looked into using the Data Entry Form tool in Excel? The data would be in a flat file, but you then could build report worksheets designed to look like you calendar (sheet 1) . as well as summary reports (sheet 3). The issue that you would have would be some repitition, but you could resolve that with some fixed formula fields in how you set up the worksheet.

 

[Farooqui Noor]

My opinion (and just that) is that you're trying to put data entry into a view that is pleasant to understand like a report (although this isn't technically a report) combined with formulas for ease of functionality. That is difficult to manage.
Have you looked into using the Data Entry Form tool in Excel? The data would be in a flat file, but you then could build report worksheets designed to look like you calendar (sheet 1) . as well as summary reports (sheet 3). The issue that you would have would be some repitition, but you could resolve that with some fixed formula fields in how you set up the worksheet.

Then what should I do? Should I continue working on this project or abandon it?

 

[awoohaw]

Well, don't abandon the project. But, try to come up with a different way.
What is the requirement?
My thought is you want to have a
1. form to input class attendance each month.
2. the form prefills month and class
3. for each day of the month you want to record attendance.
4. you want to see a daily view that is filterable by month and by class for all the students (each student per row).
5. you want a summary of students attendance (assuming for combined, as I don't see a monthly filter).

Is that right?

 

[Farooqui Noor]

Well, don't abandon the project. But, try to come up with a different way.
What is the requirement?
My thought is you want to have a
1. form to input class attendance each month.
2. the form prefills month and class
3. for each day of the month you want to record attendance.
4. you want to see a daily view that is filterable by month and by class for all the students (each student per row).
5. you want a summary of students attendance (assuming for combined, as I don't see a monthly filter).

Is that right?

No, I don't need any form. There are 7 lectures every day for different subjects. At the end of the day, I have to manually enter the attendance for all 7 lectures in Excel. I take attendance on a hard copy in the class, and then I enter it into Excel on a daily basis using that hard copy. I need a monthly report and a total for the semester as well in a very systematic manner. Can you suggest a better method for this?

 

[awoohaw]

Okay, well in my mind your entry fields in Sheet1 are what forms do.
My suggestion is above. Create a form in excel (it is not VBA) and have that feed into your worksheet.

take a look at this. If you choose to use the data entry form feature of excel or not this workbook should help you.
The data entry form can be used on the worksheet labeled 'data entry'.
One worksheet is for drop downs.
The other worksheets are for you monthly summaries and the daily class summary.

Mr Excel Farooqui Noor.xlsm

1drv.ms

 

[Farooqui Noor]

Okay, well in my mind your entry fields in Sheet1 are what forms do.
My suggestion is above. Create a form in excel (it is not VBA) and have that feed into your worksheet.

take a look at this. If you choose to use the data entry form feature of excel or not this workbook should help you.
The data entry form can be used on the worksheet labeled 'data entry'.
One worksheet is for drop downs.
The other worksheets are for you monthly summaries and the daily class summary.

Mr Excel Farooqui Noor.xlsm

1drv.ms

THANK YOU VERY MUCH, SIR. YOU HAVE PROVIDED ME WITH EXCELLENT SUPPORT. HOWEVER, I HAVE TWO OR THREE MORE POINTS THAT I WOULD LIKE TO DISCUSS WITH YOU.

1. I'M NOT SURE HOW TO ENTER THE NAMES OF STUDENTS AND SUBJECTS, AS WELL AS THE DATE, IN THE DATA ENTRY TAB. WHEN I TRY TO TYPE NAMES, THE FORMULA DISAPPEARS.
2. CAN THE FORMAT OF THE DATA ENTRY TAB BE CHANGED? LIKE I HAVE INDICATED IN THE TABLE BELOW. ENTERING DATA IN THIS FORMAT WOULD BE EASIER, AS THERE ARE 100 STUDENTS FOR ONE LECTURE. WITH 7 LECTURES IN A DAY, I WOULD NEED TO ENTER STUDENT NAMES AND DATE IN 700 ROWS IN ONE DAY. THE SEMESTER LASTS FOR 4 MONTHS, SO THE DATA WILL BE QUITE EXTENSIVE. I HOPE YOU WILL CONSIDER THE SAME AND THINK ABOUT THE FOLLOWING FORMAT FOR DATA ENTERY.

3. 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
   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
   Sheet1

 

[awoohaw]

Okay, what you're trying to do will wind up with 260000 records in a year. My suggestioni would be to do this in MS-Access or use Power Query to manage the data and tables.

You can use the attached work book to put this into power query. I am not an expert on that but there is a forum on Mr. Excel called "Power Tools" that you can ask for assistance with.

If you want to stick with the workbook, what I have attached below is something that you can start with. There are MANY conditions that you must abide by so you don't mess it up.
1. Always Save a copy of what I have created as your restore template. Copy this to start and make changes in the copy.
2. With 260000 records calculating will take some time. You must be patient. To speed up things when you are inputting information you should turn calculation off and then restart it after you have finished adding new records. Or leave it off always and press F9 to calculate when you want.
3. This is generic and assumes that you have the SAME students in the ALL classes. I'm not sure how this will mess up your counting.
4. You can speed efficiency by reducing the number of months in the data entry sheet and using multiple workbooks.
5. Take 1 or 2 hours each semester / year and prepopulate all the base fields in your data entry sheet. There are formulas that will calculate and place the date, lecture, and student in the first three columns of the data entry sheet. The ONLY column you need to change is the "A or P" column.
6. To easily update each days classes you can use AUTO FILTER to filter for class and date so you only see the students in that class
7. On the Lookups worksheet you should enter your classes and all of you students. You will need to "refresh" the defined range names for this sheet when you make updates.
8. What to do:

a. DO NOT change the formulas in any area that is purplish pink until you are absolutely ready (but the summary and monthly sheets should never be altered).​

b. Green cells have drop downs or cells that you can change values.​

c. The data entry sheets is fixed for exactly 100 students and 7 classes. If you have a total of more than 100 student names or more than 7 lectures then you really need to update the underlying prefill calculations in the 'data entry sheet'. If it is less than 100 or you have students only in some of your classes put all the students in the Students Lists on the look up worksheet. After that is done and you are certain you have all classes and students in the Lookup table you can use convert the data on the 'data entry' worksheet to values. Then use AUTO FILTER to select all of your classes one by one and filter on students to delete for classes they are not in.​

Select the 4 columns and "refresh" the named ranges.​

​

this is a workbook with a new name.

Mr Excel Farooqui Noor p1.xlsm

1drv.ms

Finally, please do not reply to me or write any post in the forum in ALL CAPS like you did earlier. It is similar to SCREAMING. The people in this forum are here because they like to help people and are unpaid for their efforts. Please keep this in mind.

If you have some questions about the workbook or formulas, just reply. If you have many questions about this, then I suggest watching some You Tube videos on any of the following You Tube channels:
Mr. Excel
ExcelIsFun
Leila Gharani
My Online Training Hub (Minda Treacy)

- Best Wishes.

 

[Farooqui Noor]

Okay, what you're trying to do will wind up with 260000 records in a year. My suggestioni would be to do this in MS-Access or use Power Query to manage the data and tables.

You can use the attached work book to put this into power query. I am not an expert on that but there is a forum on Mr. Excel called "Power Tools" that you can ask for assistance with.

If you want to stick with the workbook, what I have attached below is something that you can start with. There are MANY conditions that you must abide by so you don't mess it up.
1. Always Save a copy of what I have created as your restore template. Copy this to start and make changes in the copy.
2. With 260000 records calculating will take some time. You must be patient. To speed up things when you are inputting information you should turn calculation off and then restart it after you have finished adding new records. Or leave it off always and press F9 to calculate when you want.
3. This is generic and assumes that you have the SAME students in the ALL classes. I'm not sure how this will mess up your counting.
4. You can speed efficiency by reducing the number of months in the data entry sheet and using multiple workbooks.
5. Take 1 or 2 hours each semester / year and prepopulate all the base fields in your data entry sheet. There are formulas that will calculate and place the date, lecture, and student in the first three columns of the data entry sheet. The ONLY column you need to change is the "A or P" column.
6. To easily update each days classes you can use AUTO FILTER to filter for class and date so you only see the students in that class
7. On the Lookups worksheet you should enter your classes and all of you students. You will need to "refresh" the defined range names for this sheet when you make updates.
8. What to do:

a. DO NOT change the formulas in any area that is purplish pink until you are absolutely ready (but the summary and monthly sheets should never be altered).​

b. Green cells have drop downs or cells that you can change values.​

c. The data entry sheets is fixed for exactly 100 students and 7 classes. If you have a total of more than 100 student names or more than 7 lectures then you really need to update the underlying prefill calculations in the 'data entry sheet'. If it is less than 100 or you have students only in some of your classes put all the students in the Students Lists on the look up worksheet. After that is done and you are certain you have all classes and students in the Lookup table you can use convert the data on the 'data entry' worksheet to values. Then use AUTO FILTER to select all of your classes one by one and filter on students to delete for classes they are not in.​

Select the 4 columns and "refresh" the named ranges.​

​

this is a workbook with a new name.

Mr Excel Farooqui Noor p1.xlsm

1drv.ms

Finally, please do not reply to me or write any post in the forum in ALL CAPS like you did earlier. It is similar to SCREAMING. The people in this forum are here because they like to help people and are unpaid for their efforts. Please keep this in mind.

If you have some questions about the workbook or formulas, just reply. If you have many questions about this, then I suggest watching some You Tube videos on any of the following You Tube channels:
Mr. Excel
ExcelIsFun
Leila Gharani
My Online Training Hub (Minda Treacy)

- Best Wishes.

Thank you very much, sir. I want to express my sincere gratitude to you. You devoted your valuable time to create this Excel sheet for me. Of course, I will make sure not to reply in ALL CAPS. I understand that it can come across as shouting, and the people in this forum are volunteers who genuinely enjoy helping others without any monetary compensation. I appreciate their willingness.

 

[awoohaw]

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!