Updating Excel tables across several sheets

[mystiedx]

Hello everyone!

I have a workbook to store pupils' score in different subjects. I created the worksheets as follows:
I created the first sheet with a table with headings

Serial No

Class

Name

Score

I inserted all the pupils' names and their class. There are several classes. The serial number starts at 1 for each class. There may be up to 700 entries.

Then I duplicated the sheet for each subject I need. There may be up to 15 subjects.

Then I inserted slicers on each subject's sheet. The slicers filter the Class column.

The issue I'm having with this design is that pupils sometimes change classes or we get new students. So i need to update each sheet to reflect these changes, which is a tedious task.

To solve this issue, I thought of adding another sheet with the names of the pupils and their classes, and use formulas to update the names in the tables in the Subjects' sheets. Now the problem is the data needs to be sorted classwise and in alphabetical order of names. So if it happens that scores have already been entered for pupils in a class and then I need to add a name to that class, the scores won't move which would mean some pupils will have the wrong score.

Also the number of pupils vary year by year, so even if I use formulas I can't fix the range for specific classes. The serial number also will change accordingly.

I don't know how to solve this problem and I need help with it.

 

[HighAndWilder]

Hello everyone!

I have a workbook to store pupils' score in different subjects. I created the worksheets as follows:
I created the first sheet with a table with headings

Serial No

Class

Name

Score

I inserted all the pupils' names and their class. There are several classes. The serial number starts at 1 for each class. There may be up to 700 entries.

Then I duplicated the sheet for each subject I need. There may be up to 15 subjects.

Then I inserted slicers on each subject's sheet. The slicers filter the Class column.

The issue I'm having with this design is that pupils sometimes change classes or we get new students. So i need to update each sheet to reflect these changes, which is a tedious task.

To solve this issue, I thought of adding another sheet with the names of the pupils and their classes, and use formulas to update the names in the tables in the Subjects' sheets. Now the problem is the data needs to be sorted classwise and in alphabetical order of names. So if it happens that scores have already been entered for pupils in a class and then I need to add a name to that class, the scores won't move which would mean some pupils will have the wrong score.

Also the number of pupils vary year by year, so even if I use formulas I can't fix the range for specific classes. The serial number also will change accordingly.

I don't know how to solve this problem and I need help with it.

Is each student only identified by their name? Do they have an ID?

How do you guard against more than one pupil having the same name?

Why does a a class need to be identified both by its name AND a serial number.

When a sheet is sorted all data WILL follow. If it is done CORRECTLY then the scores will move.

I am assuming that a pupils class will be the same irrespective of subject. Is this correct?

Another sheet containing the name of the pupil and their class will make changing classes easier. A lookup would be needed on the subject sheet to get the class.

 

[mystiedx]

Is each student only identified by their name? Do they have an ID?

How do you guard against more than one pupil having the same name?

Why does a a class need to be identified both by its name AND a serial number.

When a sheet is sorted all data WILL follow. If it is done CORRECTLY then the scores will move.

I am assuming that a pupils class will be the same irrespective of subject. Is this correct?

Another sheet containing the name of the pupil and their class will make changing classes easier. A lookup would be needed on the subject sheet to get the class.

Hello HighandWilder.

Thank you for the reply.
The serial number is for the students. Let me give a sample:

Serial No	Class	Name	Score
1	7B	Max	50
2	7B	Mark	76
3	7B	John	62
			and so on for class 7B
1	7R	Will	95
2	7R	Sue	77

Like this all classes are listed, and the serial is reset for each class.

There is no student id.

Like I said, I've thought about using another sheet for the pupil names and then using lookup, but then the number of pupils isn't fixed. They may change over the course of the year with new pupils or pupils who leave etc. So then how do i specify the range for the lookup? And how do I keep the data in the subject sheets sorted automatically with score in their correct place?

 

[HighAndWilder]

Use a table for the Pupil - Class lookup table and then the range identification problem is eliminated.

I would put all of the score data into one table and then split it by subject / class / pupil ONLY as and when you need to. You would need an additional column to store the subject.

As regards keeping it sorted. It does not need to be sorted all of the time (when new rows are added, scores altered or when classes change) but ONLY when you need it to be sorted.

If all of the score data was in one table then you would only need to sort one table.

Why do you think that the score won't be in the right place when you sort a subject sheet?

 

[mystiedx]

Use a table for the Pupil - Class lookup table and then the range identification problem is eliminated.

I would put all of the score data into one table and then split it by subject / class / pupil ONLY as and when you need to. You would need an additional column to store the subject.

As regards keeping it sorted. It does not need to be sorted all of the time (when new rows are added, scores altered or when classes change) but ONLY when you need it to be sorted.

If all of the score data was in one table then you would only need to sort one table.

Why do you think that the score won't be in the right place when you sort a subject sheet?

Because the scores are entered afterwards. Teachers are provided with the template, with the names of the students. And then each teacher will input the scores for his/her pupils. Pupils don't take the same subjects. So let's say a teacher enters a score for a pupil in Maths. That pupil name, with his score, is currently in row 20. Now if a new pupil needs to be added to that class, and alphabetically the new pupil should be in row 15, the pupil name in row 20 will move down by one row. But not his score.

 

[HighAndWilder]

Because the scores are entered afterwards. Teachers are provided with the template, with the names of the students. And then each teacher will input the scores for his/her pupils. Pupils don't take the same subjects. So let's say a teacher enters a score for a pupil in Maths. That pupil name, with his score, is currently in row 20. Now if a new pupil needs to be added to that class, and alphabetically the new pupil should be in row 15, the pupil name in row 20 will move down by one row. But not his score.

New pupils should ideally be added to the bottom and the sheet resorted. If you need to insert a new pupil and keep the order and scores correct then do a row insert in the right place and all of the data needs to be entered.

I suggest that the former is a safer way.

 

[mystiedx]

New pupils should ideally be added to the bottom and the sheet resorted. If you need to insert a new pupil and keep the order and scores correct then do a row insert in the right place and all of the data needs to be entered.

I suggest that the former is a safer way.

Is there any way I can upload the file for you to have a look? I think you'll get a better understanding of what I wish to do?

 

[HighAndWilder]

Is there any way I can upload the file for you to have a look? I think you'll get a better understanding of what I wish to do?

I suggest that you provide a small set of dummy sample but realistic data and the expected results with XL2BB and explain again in relation to that sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts