Linking table to part of another table that sorts adjacent data

[mayabrx]

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.

[#2]

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

[#3]

Hi @Jan Karel Pieterse 

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

[#4]

@mayabrx 

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 @Jan Karel Pieterse 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.

[#5]

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

[#6]

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

[#7]

@mayabrx It would help if you could upload an anonymized version of your current workbook.

[#8]

@Jan Karel Pieterse I'm not 100% sure how to do that. I initially tried uploading the doc and wasn't successful. Would I upload to excel online and link it here?

[#9]

@mayabrx 

Linking one table to a specific part of another table while ensuring adjacent data remains sorted can be achieved through a process known as "cell linking" or "cell referencing." This allows you to connect data from different tables while maintaining the desired order.

Here's how you can do it:

1. Identify the Source Table: Determine the table containing the data you want to link to another table. This will be your source table.

2. Select the Target Cell: In the source table, select the specific cell or range of cells that you want to link to the other table.

3. Copy the Data: Copy the selected data (Ctrl+C on Windows or Command+C on Mac).

4. Move to the Target Table: Navigate to the location in the target table where you want to insert the linked data.

5. Paste the Data as a Link: Right-click on the target cell and choose "Paste Special" or "Paste as Link" (the exact option may vary depending on your software). This will paste the data from the source table as a link in the target table.

6. Verify Data Integrity: Ensure that the adjacent data in the target table remains sorted as desired. The linked data will automatically update if changes are made in the source table.

This method allows you to maintain a connection between the two tables while preserving the sorting order of adjacent data in the target table. It's particularly useful when you need to reference data from one table without disrupting the organization of another table.