INDIRECT & VLOOKUP functions

[Guest]

Hi, I hope someone can help me, several of you helped me with this problem
earlier and I am finding however that the following formula does not update?

Rolls!A4 contains the following formula

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5")

What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1

Any assistance would be appreciated.
Regards
tanya

 

[Guest]

So in Rolls!B2 you have "eg 9ISTX"
That means your vlookup formula gives you "1"
so the indirect formula gives you 1!A5
what are the names of your other sheets?
if there is a sheet called 1 then it should work!

Carlo

 

[Dave Peterson]

A couple of questions...

Is the sheet name really named "1"?
If yes, then you'd want:

=INDIRECT("'" & VLOOKUP(B2,D41:F50,3,TRUE)&"'!A5")
(added a pair of apostrophes)

Since you're matching by classname, I would think you'd want an exact match
which means you would would want:
=INDIRECT("'" & VLOOKUP(B2,D41:F50,3,False)&"'!A5")
(changed True to False)

And if the formula doesn't update, I'd check the calculation mode setting.
In xl2003, it's Tools|Option|calculation tab
make sure it's set to automatic.

Hi, I hope someone can help me, several of you helped me with this problem
earlier and I am finding however that the following formula does not update?

Rolls!A4 contains the following formula

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5")

What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1

Any assistance would be appreciated.
Regards
tanya

 

[Guest]

Hello Carlo
Your are correct, there are 10 sheets in total numbering 1 - 10
Further investigation shows that it is actually calculating from sheet 2 not
1 which is really weird.

So in Rolls!B2 you have "eg 9ISTX"
That means your vlookup formula gives you "1"
so the indirect formula gives you 1!A5
what are the names of your other sheets?
if there is a sheet called 1 then it should work!

Carlo

Hi, I hope someone can help me, several of you helped me with this problem
earlier and I am finding however that the following formula does not update?

Rolls!A4 contains the following formula

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5")

What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1

Any assistance would be appreciated.
Regards
tanya

 

[Guest]

Hi Dave

You have hit the nail on the head, the problem was I used 'TRUE' instead of
false.

Thank you very much!

Cheers
Tanya

A couple of questions...

Is the sheet name really named "1"?
If yes, then you'd want:

=INDIRECT("'" & VLOOKUP(B2,D41:F50,3,TRUE)&"'!A5")
(added a pair of apostrophes)

Since you're matching by classname, I would think you'd want an exact match
which means you would would want:
=INDIRECT("'" & VLOOKUP(B2,D41:F50,3,False)&"'!A5")
(changed True to False)

And if the formula doesn't update, I'd check the calculation mode setting.
In xl2003, it's Tools|Option|calculation tab
make sure it's set to automatic.

Hi, I hope someone can help me, several of you helped me with this problem
earlier and I am finding however that the following formula does not update?

Rolls!A4 contains the following formula

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5")

What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1

Any assistance would be appreciated.
Regards
tanya