Index & Match to Bring Over Information

[MatthewIJClark]

I've got a spreadsheet where on 1 tab I have a collective amount of information and then for each individual student they have their own tab.

I'm trying to work out how I get the information from column 1 on sheet 1 onto the students individual tab if there is a mark entered for that module code.

Because there is likely to be a collective list of nearly 50 odd module codes I only want to see on the students individual tab what they have a mark for.

Any help will be great appreciated.

[63falcondude]

All of the sheets (except Sheet1) are blank.

Instead of us guessing on what those sheets look like, I recommend updating your sample to include that.
Include the desired results, which you can enter manually.

[WideBoyDixon]

Based on your example and using a complex array formula which has to extract the tab name, I came up with the following in A1 of tab "123456":

Please Login or Register  to view this content.

If the students are not actually numbers then you may need to remove the "+0" both times it occurs. See attached example.

WBD

[kvsrinivasamurthy]

I eac sheet in B2 then dragged across

Please Login or Register  to view this content.

[MatthewIJClark]

Hi All,

Really sorry for my totally confusing first post, I've not attached the spreadsheet with what I'd like it to look like and I've being trying to us the following formulas without success:

=INDEX('Part 1'!$E$1:$BE$1,MATCH('26002881'!$A$3,'Part 1'!$A:$A,0),MATCH('Part 1'!E3:BE3>0,0))

So index and match seems to work up to the second match criteria and is does like me referencing the marks for each student to try and pull through the module code in row 1 on sheet 1 to the students individual tab in row 1.

Any help greatly appreciated

Thanks

[shukla.ankur281190]

Try

B3

Please Login or Register  to view this content.

Copy across

[WideBoyDixon]

Based on my original solution but simplified with the value being available in A3 on those sheets. See attached.

WBD

[MatthewIJClark]

Based on my original solution but simplified with the value being available in A3 on those sheets. See attached.

WBD

Hi WideBoyDixon,

Many thanks for the second reply, I've tried using your formula on my actual spreadsheet and unfortunately it brings the module code but not for the particular student just on the one first in the list, see formula below:

=IFERROR(INDEX('Part 1'!$1:$1,SMALL(IF(INDEX('Part 1'!$E$1:$BE$220,MATCH('26002881'!$A$3,'Part 1'!$A:$A,0),)<>"",COLUMN(INDEX('Part 1'!$E$1:$BE$220,MATCH('26002881'!$A$3,'Part 1'!$A:$A,0),))),COLUMNS($A$1:A$1))),"")

So on the tab with all the students on it there are about 50 to 60 module codes but a student is not likely to have no more than 10 modules codes that they will receive results on, plus I've tried drag the formula across and I get no results at all.

Once again, I really appreciate the work so far and any more given.

Thanks

[WideBoyDixon]

I suspect you're making this harder by posting one sheet but wanting the results on a different format. Some questions:

1. On sheet "Part 1", which column contains the student ID and which columns/rows contain the course codes and scores?
2. Are you using Ctrl+Shift+Enter to enter the formula as it's an array formula?

WBD

[MatthewIJClark]

I suspect you're making this harder by posting one sheet but wanting the results on a different format. Some questions:

1. On sheet "Part 1", which column contains the student ID and which columns/rows contain the course codes and scores?
2. Are you using Ctrl+Shift+Enter to enter the formula as it's an array formula?

WBD

Apologies, WideBoyDixon, would love to be able to upload my actual spreadsheet but it has sensitive information which I can't allow to be uploaded.

Answers to your questions:

1) Student ID is in column A in both the part 1 spreadsheet and the student individual spreadsheet.

2) Now I'm a complete idiot, didn't realise it required Ctrl+Shift+Enter, it is all now working.

Thank you so much.

[WideBoyDixon]

Ah good. Glad we got there in the end.

WBD