Vlookup using different sheets
 

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!

Vlookup using different sheets
 

Try something like this:

A1 = H29 (a sheet name)

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


--
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!

Vlookup using different sheets
 

"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!

Vlookup using different sheets
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Cpt. Costanzo" wrote in message
...


"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!