Linking table to part of another table that sorts adjacent data

Question

I'm creating a marking sheme for teachers and want to link tables across multiple sheets.

1. The file will have multiple sheets for each test.

eg:

Sheet 1 = Test 1

Sheet 2 = Test 2 etc

There are 11 tests in all so there will be 11 of these sheets.

2. Grades are inputted for 3 different subjects in a sheet by test section, then totalled, a percentage calculated, a weighted percent calculated, then an overall % across all subjects calculated based on the weight.

ISSUE: I want teachers to be able to input their students' names in a table on one sheet and the names link to all other sheets. If the names are sorted in the table, it should be sorted across all sheets, but I'm having issues with the grade data being sorted along with the names.

mathetes · Answer

mayabrx&nbsp;&nbsp;I'm going to chime in from the sidelines here, sidelines because I'm not going to be able to follow-up, as my wife and I are departing in about two hours for a two week work/vacation trip a third of the way around the world.&nbsp;When I read that people are creating a workbook that consists of multiple sheets with repeating data sets (different scores/grades, but essentially same format, maybe even in this case same subjects(?), perhaps being collected over the course of an academic year?) it raises a red flag in my design-sensitive mind. Excel is really good--one could even say it excels--at summarizing data from a single extensive database. We humans think it's clearer when we separate Test 1 from Test 2 from Test 3, etc., because that's how we'd do it on sheets of paper.&nbsp;The second flag--a yellow flag this time--is that it appears you're mixing output and input on the same page. That can work, but, again, it's how we'd do it as humans on paper, but it actually can get in the way of simple Excel functions to summarize the whole year's worth (all eleven sheets' worth) of data.&nbsp;So where I'm going--and for the reasons noted I'll have to leave it to&nbsp;JKPieterse&nbsp;or others to pursue if it's even deemed feasible--is that I'd want to see if a single database could be constructed, into which all of the scores for all of the students for all of the tests would be entered. All the input into one database. Each entry would include the period/week/semester, student ID, class, section, grade ... whatever is needed for each discrete grade to be tracked and connected with whatever now is differentiating between Test 1, Test 2, etc.&nbsp;And then design a "dashboard" sheet that delivers the output, does all the data retrieval necessary to calculate the averages, means, min's, max's, etc, and can do so slicing it in whatever ways you need.&nbsp;&nbsp;Bottom line: Separating Input from Output makes for cleaner, more robust workbooks, and allows Excel to do more of the work of slicing and dicing.&nbsp;To the extent I can, I'll try to follow along from abroad--and it's fine if you all choose to ignore this comment from the sidelines--because I do find what you've asked here to be really challenging, and I'd love to see it work well for you.

jkpieterse · Answer

How are you pulling in those grades next to the names? (which formula are you using)

mayabrx · Answer

Hi JKPieterse&nbsp;&nbsp;Here is a snapshot of what the sheet looks like currently in the snapshot shows an example of a formula used:&nbsp;Columns D, E, F,&nbsp; &nbsp;J, K,&nbsp; &nbsp;O, P, Q and R are entered manually. These are the total marks for each section in the tests of the respective subjectsTotals&nbsp;(i.e. columns G, L and S) are calculated using the SUM function (columns G, L and S)Columns H, M and T use a version of the function seen in the snapshot i.e.&nbsp;=IF([@[MathTotal]]="","",([@[MathTotal]]/$G$4)*100)Columns I, N and U use a version of this function:&nbsp;=IF([@[M%]]="","",([@[M%]]/$H$4)*$I$4)&nbsp;And the overall grade is calculated using the data in columns I, N and U i.e.&nbsp;=IF(OR([@[MW%]]="",[@[ELAW%]]="",[@[CWW%]]=""),"",(([@[MW%]]+[@[ELAW%]]+[@[CWW%]])/2)*100)If and OR functions are used so the cell remains blank if there is no data to pull

jkpieterse · Answer

mayabrx&nbsp;I totally agree with what&nbsp;mathetes&nbsp;had to say about this. Do you need help to try to implement his suggestions?

mayabrx · Answer

Jan_Karel_Pieterse if this would be the best way to go about it then yes, help or suggestions will definitely be needed

Forum Discussion

Linking table to part of another table that sorts adjacent data

Share

Resources