Jason Chan
New Member
- Joined
- Jul 17, 2015
- Messages
- 27
I run a tuition center and at the moment, I only have 10 students. The center offers three subjects only - ENGLISH, MATH and HISTORY. Scores are aggregated by test components, there are total of seven components in my center. Each subject utilizes a portion of the test components which accords scores for every test taken to each student (ENG – 4 comps, MATH – 4 comps, HIST – 2 comps). The three subjects share one to two test components so some components are retaken with altered questions. All students enroll into ENG, but not all students enroll into MATH and HIST.
SHEET1 is the manual main tests data entry worksheet and has about twenty columns covering all components scores for all ENG, MATH. HIST. SHEET2 is the voluntary work from students’ own practical work which do not add to their overall scores, but provide a reference for my end of semester award. I have to manually enter each student’s scores in all ENG, MATH and HIST components test scores into SHEET1 after every test I give them, as well as SHEET2 after some of them offer to get together to form smaller voluntary projects.
Then, I create SHEETS 3,4,5 to automatically populate with a simple =IF(…,ELSE,””) function pasting them into rows 1 to 1000 in each of these three sheets 3,4,5 (1000 is the temporary number of rows that I estimate my tuition may grow to need for the foreseeable years). SHEET3, SHEET4 and SHEET5 are for auto-populating from SHEET1, filling only rows who meet certain criteria. I do not plan to manually enter anything into SHEETs 3,4,5. After they self-populate every time, my 1st problem is, I need each sheets 3,4,5 to all be instantly auto-sorted ascendingly by the date column in column C, all rows consolidated (no blank rows in-between the 1st and last filled rows) and rows that although look blank but with function to automatically go beneath the last filled row, but not eliminating or deleting those blanks with functions.
Finally, comes SHEET6, which is my 2nd problem, I need to automatically populate continuously by consolidating and merging ALL rows from SHEETs 2,3,4,5 via VBA, then continuously auto-sorting all these rows on SHEET6 whenever a new entry is made in SHEET1 or SHEET2, sorted by the students’ ID in column B. So, the total no.of rows in SHEET6 would be the total combined of all rows in SHEETs 2,3,4,5.
Sadly, I have tried some depressing attempts on my own and a couple of assisted ones, but none have really helped me. For instance, I tried a code to sort the auto-populate sheets, but my function rows that are still blank, all get sorted to the top, while still showing blank. Some people offered me codes, but those codes delete my functions rows that return blanks. I don't want those blanks deleted. I need the filled function rows that are despite not populated, to just be sorted to the bottom, while the filled names and scores rows to the top. And, I have not gotten any help so far for sorting & consolidating the auto-populated sheets to SHEET6 yet. I’m not VBA fluent. I will appreciate if there are any kind expert out here who could help me with a VBA code to complete the above requests for my SHEETs 3,4,5,6?
I have created a cropped miniature sample workbook (of my actual workbook) but I can't find any location here to upload. Please let me know if needed and how to show you.
Waiting for the kind expert at my center,
Jason .
SHEET1 is the manual main tests data entry worksheet and has about twenty columns covering all components scores for all ENG, MATH. HIST. SHEET2 is the voluntary work from students’ own practical work which do not add to their overall scores, but provide a reference for my end of semester award. I have to manually enter each student’s scores in all ENG, MATH and HIST components test scores into SHEET1 after every test I give them, as well as SHEET2 after some of them offer to get together to form smaller voluntary projects.
Then, I create SHEETS 3,4,5 to automatically populate with a simple =IF(…,ELSE,””) function pasting them into rows 1 to 1000 in each of these three sheets 3,4,5 (1000 is the temporary number of rows that I estimate my tuition may grow to need for the foreseeable years). SHEET3, SHEET4 and SHEET5 are for auto-populating from SHEET1, filling only rows who meet certain criteria. I do not plan to manually enter anything into SHEETs 3,4,5. After they self-populate every time, my 1st problem is, I need each sheets 3,4,5 to all be instantly auto-sorted ascendingly by the date column in column C, all rows consolidated (no blank rows in-between the 1st and last filled rows) and rows that although look blank but with function to automatically go beneath the last filled row, but not eliminating or deleting those blanks with functions.
Finally, comes SHEET6, which is my 2nd problem, I need to automatically populate continuously by consolidating and merging ALL rows from SHEETs 2,3,4,5 via VBA, then continuously auto-sorting all these rows on SHEET6 whenever a new entry is made in SHEET1 or SHEET2, sorted by the students’ ID in column B. So, the total no.of rows in SHEET6 would be the total combined of all rows in SHEETs 2,3,4,5.
Sadly, I have tried some depressing attempts on my own and a couple of assisted ones, but none have really helped me. For instance, I tried a code to sort the auto-populate sheets, but my function rows that are still blank, all get sorted to the top, while still showing blank. Some people offered me codes, but those codes delete my functions rows that return blanks. I don't want those blanks deleted. I need the filled function rows that are despite not populated, to just be sorted to the bottom, while the filled names and scores rows to the top. And, I have not gotten any help so far for sorting & consolidating the auto-populated sheets to SHEET6 yet. I’m not VBA fluent. I will appreciate if there are any kind expert out here who could help me with a VBA code to complete the above requests for my SHEETs 3,4,5,6?
I have created a cropped miniature sample workbook (of my actual workbook) but I can't find any location here to upload. Please let me know if needed and how to show you.
Waiting for the kind expert at my center,
Jason .
.
