Forum Discussion
Linking table to part of another table that sorts adjacent data
Hi JKPieterse
Here is a snapshot of what the sheet looks like currently in the snapshot shows an example of a formula used:
- Columns D, E, F, J, K, O, P, Q and R are entered manually. These are the total marks for each section in the tests of the respective subjects
- Totals (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. =IF([@[MathTotal]]="","",([@[MathTotal]]/$G$4)*100)
- Columns I, N and U use a version of this function: =IF([@[M%]]="","",([@[M%]]/$H$4)*$I$4)
- And the overall grade is calculated using the data in columns I, N and U i.e. =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
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.
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.
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.
So where I'm going--and for the reasons noted I'll have to leave it to JKPieterse 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.
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.
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.
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.
