hi all,
I am working on a spreadhseet where in sheets 1 - 39 I have pupil data and test scores, each sheet is named after a pupil so sheet 1 = Bob, sheet 2 = Fred etc.
Each sheet is laid out the same
Cell A1 is Subject B1 Course Code C1 Test Score
Cell A2 is Maths B2 MA023 C2 87
Cell A3 etc etc as per A2
I created a named range of all the pupils names called Pupils.
In worksheet 40 I am tring to create a quick tool to find test scores
Cell A1 is data validated using the list "Pupils"
Cell B1 is data validated using in Indirect list of A1 (i.e. it gives me the subjects for that pupil)
Cell C1 needs to be a vlookup using the indirect so that it looks up the pupil worksheet and the subject from A1 and B1 and then returns the test score
So it should look like
Bob Maths 87
I cant semm to get the vlookup argument right - any help greatly appreciated
I am working on a spreadhseet where in sheets 1 - 39 I have pupil data and test scores, each sheet is named after a pupil so sheet 1 = Bob, sheet 2 = Fred etc.
Each sheet is laid out the same
Cell A1 is Subject B1 Course Code C1 Test Score
Cell A2 is Maths B2 MA023 C2 87
Cell A3 etc etc as per A2
I created a named range of all the pupils names called Pupils.
In worksheet 40 I am tring to create a quick tool to find test scores
Cell A1 is data validated using the list "Pupils"
Cell B1 is data validated using in Indirect list of A1 (i.e. it gives me the subjects for that pupil)
Cell C1 needs to be a vlookup using the indirect so that it looks up the pupil worksheet and the subject from A1 and B1 and then returns the test score
So it should look like
Bob Maths 87
I cant semm to get the vlookup argument right - any help greatly appreciated