Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT & VLOOKUP functions
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT & VLOOKUP functions
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT & VLOOKUP functions
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. Tanya wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT & VLOOKUP functions
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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT & VLOOKUP functions
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" wrote: 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. Tanya wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
indirect.ext / pull functions | Excel Discussion (Misc queries) | |||
Sumproduct & Indirect Functions | Excel Worksheet Functions | |||
row-indirect functions | Excel Discussion (Misc queries) | |||
Offset and Indirect functions | Excel Discussion (Misc queries) |