Excel 2007 : Lookup across multiple sheets

Hello again guys,

I'm in a bit of a pickle.

I have 10 different sheets in a workbook with a large amount of students on it. Each sheet is a seperate module and a lot of the students are doing 3 or 4 of these modules but aren't doing all of them.

I am going to add an 11th sheet. This sheet will have the Module titles as headings (all of them) and then all the different student names down the left hand colomn.

Now What I would like to do is use a formula to find the aprorpriate student id Code (e.g. B10054) and then copy the whole row of data and right it into my master sheet.

I am surfing this forum on anything to do with VLOOKUP that might help me but so far nothing is relative to what im trying to acomplish, which makes me wonder if I am using the wrong formula?

Cheers for any ideas!

What if that student is in multiple modules.. what needs to be copied over then?

Why not just point to the first cell in the relevant sheet, in the first row of names and use that , then copy down.

e.g.

To get the first Student code from Food Technologies sheet... go to A4 of the Main sheet and type = sign, then go to the Food Technologies sheet and point to cell D7... then hit enter... and copy down the formula... do the same for the other categories, pointing to the relevant cells. and copying down.

Note: If you want to be able to copy down further in case of additions to the individual sheets, then add an error handler to return blanks after last entry is gotten.

e.g. in A4 of main sheet, =IF('Food Technology'!D7="","",'Food Technology'!D7)

copied down as far as you want... so that future additions automatically get inserted.