Linking Data

[youngy_6948]

I have a list of students and there appropriate targets and grades they are separated into there classes in separate tabs at the moment i need away of linking each students data onto a master sheet as the separate tabs have more info than the master sheet requires is there any way of doing this with out linking each individual tab as there is a chance that a student may move groups so if i change there group i need it to reference all there data across as well?????

 

[Trevor_S]

Would be useful to know what is in each column, and have example sheet names. But perhaps the following is an idea of what can be done.

Say that you have three sheets: Master, Class 1 and Class 2. All three sheets have student reference numbers in column A, their class in column B and their name in column C. The master sheet has all of the student reference numbers, some of which are on the class 1 sheet, the others are on the class 2 sheet.

On the master sheet, you enter the student number and class in columns A and B. The class in column B is exactly the same as the sheet name of the appropriate class sheet. The formula that you need in C1 to pick up the student name from the relevant class sheet is:

=VLOOKUP(A1,INDIRECT("'"&B1&"'!A:C"),3,FALSE)

I've changed the colour of the two single quotes so that you can see them more easily.

The formula does a vlookup on whatever sheet name is in cell B1. Therefore if someone changes class, you only need to move their data from one class sheet to the other, and amend the class name in the column on the master sheet. You don't need to amend the formula, and can use the same formula for all students, regardless of which class they are in.

Obviously you will have many more columns of data than this, so amend the A:C and 3 in the vlookup formula accordingly.

 

[youngy_6948]

hi thanks for your response,

i have 6 sheets;
11H1
11H2
11H3
11H4
11A1
11A2

that i want to to put into 1 master there is approximately 150 columns im trying to carry across hence why just linking a cell seemed out of the question.

as for cell contents it is mainly grades and test scores i would happily send you the spreadsheet if that helped but in essence there is some data in the 11H1-11A2 that i do need on the master and some information that i dont and if a child was to move from for arguments sake 11H1 to 11H2 i dont particularly want to have to redo all of the cell associations i also need what is in the master sheet to be a live representation of what is in the other sheets so if it changes in 11H1 it also changes on the master.

im not sure if im asking too much of excel here and its been a looooong time since ive had to do something so complicated

 

[Trevor_S]

Excel should be able to cope!

Make sure that column A of all sheets contains something unique to the student (as this is needed for a VLOOKUP). Hence my suggestion of a student reference number. A name would be ok instead, provided that there are no two students with the same name, and it is spelt the same on both the class and master sheets.

150 columns of data on the class sheets is not a problem! But you must be using the same columns for the same data in the same order on each class sheet.

A column within the master (say column B for the purpose of this example) contains the class (sheet name) that the student is currently in. This column is the only thing that would need to be changed on the master when a student moves class. So if a student changes from 11H1 to 11H2, you'll have to move their data from the 11H1 sheet to the 11H2 sheet, and you'll have to change column B on the master sheet to say 11H2 instead of 11H1. But you won't have to change all the formula containing the data brought across from the class sheets. The "indirect" part of my example formula takes the sheet name from the text in column B, so it is a live link.

So ... on the master, in each column that you want data to be bought across into, enter the formula below:

=VLOOKUP($A_,INDIRECT("'"&$B_&"'!#:#"),?,FALSE)

where:
_ is the current row number on the master sheet,
#:# is the column range of the data on the class sheets - including the first column containing the unique reference
? is the number of the column within that range containing the data that you want to appear in the master sheet.

You can then copy the first row of formulae down alongside all of the student references/class names on the master sheet.

 

[youngy_6948]

not sure how to get this to work properly as i would want on the class sheets;

[TABLE="width: 500"]
<tbody>[TR]
[TD]unique identifier[/TD]
[TD]Name[/TD]
[TD]Class[/TD]
[TD]Percentage[/TD]
[TD]grade[/TD]
[TD]rag data[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]john[/TD]
[TD]11h1[/TD]
[TD]56[/TD]
[TD]c[/TD]
[TD]^[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]dave[/TD]
[TD]11h1[/TD]
[TD]55[/TD]
[TD]c[/TD]
[TD]^[/TD]
[/TR]
</tbody>[/TABLE]


yet on the master

[TABLE="width: 500"]
<tbody>[TR]
[TD]unique identifier[/TD]
[TD]name[/TD]
[TD]class[/TD]
[TD]grade[/TD]
[TD]rag data[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]john[/TD]
[TD]11h1[/TD]
[TD]c[/TD]
[TD]^[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]dave[/TD]
[TD]11h1[/TD]
[TD]c[/TD]
[TD]^[/TD]
[/TR]
</tbody>[/TABLE]


so there would be columns missing from each persons record as all the info is not required on the master sheet

 

[Trevor_S]

So... in this example Class sheet "11h1" has columns A:F, Master sheet has columns A:E

On the master sheet, columns A and C are typed in.
Type these formulas in the remaining columns of row 2 (i.e. the first row of data below the headers):
B2 =VLOOKUP($A2,INDIRECT("'"&$C2&"'!A:F"),2,FALSE)
D2 =VLOOKUP($A2,INDIRECT("'"&$C2&"'!A:F"),5,FALSE)
E2 =VLOOKUP($A2,INDIRECT("'"&$C2&"'!A:F"),6,FALSE)

A:F because that is the range covered by data in the Class sheet
2 because the name is in the second column of the Class sheet
5 because the grade is in the fifth column of the Class sheet
6 because the rag data is in the sixth column of the Class sheet

Copy these formulae down to row 3.

To test it further, create a new sheet called 11h2. On row 1, put the same headers as on sheet 11h1. Move the data for either of the students from 11h1 to 11h2. If you look at the master sheet, the details for the student that you moved will probably say #n/a. But then type their new class name into column B, and their details will reappear automatically...

The column order of the master sheet is unimportant, provided that the formula in those columns point to the column number in the class sheet that contains the data you want.