Hello.
I have this (rather ungainly) workbook in which we track student information: demographics, tutoring notes, attendance, etc. I'd like to say right up front that I inherited the overall structure of the workbook, and I've been working hard both to streamline things and add functionality. I am also working on a complete redesign, but for now I'm stuck with the current structure. I feel strongly that having a separate worksheet of each type for each month is most likely hugely inefficient, but it's what I have for now.
One function I've added is a sort of "autocomplete" for the Student Info worksheets so that when a chronically ill patient is readmitted, much of their demographic information (DOB, grade, school, district, city, county, state) from previous months is automatically filled in. I suspect that this is part of my problem, but I'm not convinced, and I don't have good ideas for alternatives. The number of cells depending on any given entry for this information lessens through the semester: February looks back at January, March looks back at February & January, etc.; June doesn't have anything looking at it.
Notes on structure:
-Each month has a Student Info worksheet, where demographic info & tutoring notes go. Unfortunately, the tables in these worksheets are named Entry: JanEntry, FebEntry, etc. Sorry about that.
-Each month has an Attendance worksheet that refers to Student Info for that month to pull in student ID, name, school district. This can be sorted independently of Student Info, and attendance will remain properly attributed.
-Each month has a Communication worksheet, which is used to generate a printed worksheet for tutors to use as a reference throughout the day. It refers to Student Info. Nothing refers to Communication.
-Each month has a 150 Form worksheet, which is used to generate a form letter for the local district office on a monthly basis. It refers to Student Info and Attendance.
-There is a single Schools worksheet that is used to provide dynamic data validation menus for entering school & district information. We add to it as we discover new kids from schools we haven't served yet.
-The Data worksheet pulls together the information from the Student Info and Attendance worksheets.
-The Menus worksheet has information used for data validation menus & INDEX lookups of Zip/City/County information.
-The Reports worksheet provides summary information, referring to the Data worksheet.
The only volatile functions I can find in the whole workbook are the OFFSET formulas that are used in the data validation for districts and schools on the Student Info worksheets. It is my understanding that using OFFSET in data validation doesn't increase volatility, so I've worked to keep it there!
My problem, in a nutshell: When I enter information that doesn't have a lot of other cells depending on that information (a new student's grade level in June, for example), there's still a significant amount of calculation time following the entry before I can move on. I'd like to understand why this is the case, and it'd be even better to find a fix!
Sorry for the long post, but I thought a bit of a tour would be useful. If you're interested in helping and have any questions, please don't hesitate to ask. I've added 3 sample students to January and February so you can see a little bit of the functioning of the workbook. I haven't added any attendance data, though, since it's the Student Info worksheets that made me scratch my head.
The file is too large to upload; it can be downloaded here: https://drive.google.com/file/d/0B7kLqoPt5NtfalJEZkdEUFgwcTQ/edit?usp=sharing
I have this (rather ungainly) workbook in which we track student information: demographics, tutoring notes, attendance, etc. I'd like to say right up front that I inherited the overall structure of the workbook, and I've been working hard both to streamline things and add functionality. I am also working on a complete redesign, but for now I'm stuck with the current structure. I feel strongly that having a separate worksheet of each type for each month is most likely hugely inefficient, but it's what I have for now.
One function I've added is a sort of "autocomplete" for the Student Info worksheets so that when a chronically ill patient is readmitted, much of their demographic information (DOB, grade, school, district, city, county, state) from previous months is automatically filled in. I suspect that this is part of my problem, but I'm not convinced, and I don't have good ideas for alternatives. The number of cells depending on any given entry for this information lessens through the semester: February looks back at January, March looks back at February & January, etc.; June doesn't have anything looking at it.
Notes on structure:
-Each month has a Student Info worksheet, where demographic info & tutoring notes go. Unfortunately, the tables in these worksheets are named Entry: JanEntry, FebEntry, etc. Sorry about that.
-Each month has an Attendance worksheet that refers to Student Info for that month to pull in student ID, name, school district. This can be sorted independently of Student Info, and attendance will remain properly attributed.
-Each month has a Communication worksheet, which is used to generate a printed worksheet for tutors to use as a reference throughout the day. It refers to Student Info. Nothing refers to Communication.
-Each month has a 150 Form worksheet, which is used to generate a form letter for the local district office on a monthly basis. It refers to Student Info and Attendance.
-There is a single Schools worksheet that is used to provide dynamic data validation menus for entering school & district information. We add to it as we discover new kids from schools we haven't served yet.
-The Data worksheet pulls together the information from the Student Info and Attendance worksheets.
-The Menus worksheet has information used for data validation menus & INDEX lookups of Zip/City/County information.
-The Reports worksheet provides summary information, referring to the Data worksheet.
The only volatile functions I can find in the whole workbook are the OFFSET formulas that are used in the data validation for districts and schools on the Student Info worksheets. It is my understanding that using OFFSET in data validation doesn't increase volatility, so I've worked to keep it there!
My problem, in a nutshell: When I enter information that doesn't have a lot of other cells depending on that information (a new student's grade level in June, for example), there's still a significant amount of calculation time following the entry before I can move on. I'd like to understand why this is the case, and it'd be even better to find a fix!
Sorry for the long post, but I thought a bit of a tour would be useful. If you're interested in helping and have any questions, please don't hesitate to ask. I've added 3 sample students to January and February so you can see a little bit of the functioning of the workbook. I haven't added any attendance data, though, since it's the Student Info worksheets that made me scratch my head.
The file is too large to upload; it can be downloaded here: https://drive.google.com/file/d/0B7kLqoPt5NtfalJEZkdEUFgwcTQ/edit?usp=sharing