ExcelBanter - View Single Post

Cpt. Costanzo[_2_]

"T. Valko" wrote:

Try something like this:

A1 = H29 (a sheet name)

=VLOOKUP($B$3,INDIRECT("'"&A1&"'!A:H"),D$2,0)

Thanks that's perfect. Im going to have a bit of think about my new discovery INDIRECT!! Not quite sure I completely get it but it's certainly done the trick. Cheers
--
Biff
Microsoft Excel MVP

"Cpt. Costanzo" <Cpt. wrote in message
...
I wonder if anyone can work this one out for me!
I want the same Vlookup but applied to different sheets. We have lots of
different classes sitting the same exam. Each class has a different sheet.
We
average the mark for each question. I want to compare the averages for
each
question, for each class.
I use VLookup to get the average by looking up average in the sheet and
then
returing the questions scores, (=VLOOKUP($B$3,'H29'!$A:$H,D$2,FALSE))
So $B$3 is the word "Average" which appears in the array A:$H (In this
instance I want the question score at col index $D$2).
My question is how, without retyping can I substitute different classes,
on
different sheets (here 'H29') to appear in the Vlookup? It doesnt work if
you
just reference a cell with the correct class name, or using the ampasand.
Be
really pleased for an answer as I've puzzled this for ages!