INDIRECT & VLOOKUP functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
Click to expand...
 
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.

Carlo said:
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

Tanya said:
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
Click to expand...
Click to expand...
 
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

Dave Peterson said:
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
Click to expand...
Click to expand...
 
Back
Top