VaMu
05-16-2007, 06:37 AM
Hello,
I am new here and know nearly nothing about VBA. I am trying to make an attendance/absent system for a school. It is quite complicated, because we have six periods, 40 teachers, over 400 pupils, which can belong to several groups at the same time. Several teachers teach the same group during the day. Teachers want to know the attendance data of their indivicual courses, and management wants to sum all up. So, I should use Access, for example...
Anyway, now I am trying rather "simple" solution (which leads to large files I'm afraid):
First I have the Main workbook, all data summed up: names of all the students are in column A, they can belong to five different groups (next five columns). To the right all the absent data is summed from other workbooks, which have the same structure, except that these other workbooks are for individual teachers, and they have ten worksheets, at the maximum, for their courses per period, that is six weeks.
For selecting/showing the correct group of pupils Auto Filter is used. I have to copy every 400 names to ten worksheets, for example, and there must be the same autofilter on, in every sheet. The file size is 1,2 MB so far, not too bad, yet.
But then there should be dates in columns. Perhaps not for the whole year (only 255 columns in Excel 2003?), but at least for three periods, weekends should be excluded. Dates should be in reverse order, passed date to the right.
My problem: it is going to be a large table. When a teacher activates a spreadsheet (that he/she has renamed, I do not know the name beforehand), today's date/column should be selected/activated automatically as the first column after column, say G. The window is split vertically after column F. For example, if in cell AZ5 reads 16.5.2007, the code should start from G5, for example, and compare today's value until it reaches AZ5, which is the same. Cell AZ5 should be selected and column AZ the first one shown to the right of window split. Could you help me? We are using Excel 2003 at school.
By the way, I have to sum up hours, which should be categorized: sick, sleepy etc... and calculate percentages... perhaps I should give up... probably the file size would be enormous.
I am new here and know nearly nothing about VBA. I am trying to make an attendance/absent system for a school. It is quite complicated, because we have six periods, 40 teachers, over 400 pupils, which can belong to several groups at the same time. Several teachers teach the same group during the day. Teachers want to know the attendance data of their indivicual courses, and management wants to sum all up. So, I should use Access, for example...
Anyway, now I am trying rather "simple" solution (which leads to large files I'm afraid):
First I have the Main workbook, all data summed up: names of all the students are in column A, they can belong to five different groups (next five columns). To the right all the absent data is summed from other workbooks, which have the same structure, except that these other workbooks are for individual teachers, and they have ten worksheets, at the maximum, for their courses per period, that is six weeks.
For selecting/showing the correct group of pupils Auto Filter is used. I have to copy every 400 names to ten worksheets, for example, and there must be the same autofilter on, in every sheet. The file size is 1,2 MB so far, not too bad, yet.
But then there should be dates in columns. Perhaps not for the whole year (only 255 columns in Excel 2003?), but at least for three periods, weekends should be excluded. Dates should be in reverse order, passed date to the right.
My problem: it is going to be a large table. When a teacher activates a spreadsheet (that he/she has renamed, I do not know the name beforehand), today's date/column should be selected/activated automatically as the first column after column, say G. The window is split vertically after column F. For example, if in cell AZ5 reads 16.5.2007, the code should start from G5, for example, and compare today's value until it reaches AZ5, which is the same. Cell AZ5 should be selected and column AZ the first one shown to the right of window split. Could you help me? We are using Excel 2003 at school.
By the way, I have to sum up hours, which should be categorized: sick, sleepy etc... and calculate percentages... perhaps I should give up... probably the file size would be enormous.