| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
Hi, I hope someone can help me
I have the following formula and as I step through it, I can't see any problem with it, however it keeps returning an error. 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
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
Try moving the closed bracket to the end of the formula like so:
=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5") I assume in your example that you want to get data from 1!A5 Hope this helps. Pete On Oct 11, 11:29 am, Tanya wrote: Hi, I hope someone can help me I have the following formula and as I step through it, I can't see any problem with it, however it keeps returning an error. 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
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
Do you want to refer to cell A5 of the chosen sheet? Then:
=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5" The issue here is what does A5 contain. It should contain a cell address like B1, AF13 etc. Is there a chance that your sheet names have space? If so: =INDIRECT("'"&VLOOKUP(B2,D41:F50,3,TRUE)&"'!")&A5 Notice the extra single quotes. The solution might be in a combination of the two suggestions. HTH Kostis Vezerides On Oct 11, 1:29 pm, Tanya wrote: Hi, I hope someone can help me I have the following formula and as I step through it, I can't see any problem with it, however it keeps returning an error. 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
One guess ..
Try it as: =INDIRECT("'"&VLOOKUP(B2,D41:F50,3,TRUE)&"'!A5") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tanya" wrote: Hi, I hope someone can help me I have the following formula and as I step through it, I can't see any problem with it, however it keeps returning an error. 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
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
Thank you very much, it worked a treat and saved me a lot of time. Regards Tanya "Max" wrote: One guess .. Try it as: =INDIRECT("'"&VLOOKUP(B2,D41:F50,3,TRUE)&"'!A5") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tanya" wrote: Hi, I hope someone can help me I have the following formula and as I step through it, I can't see any problem with it, however it keeps returning an error. 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
Thank you, it works well.
Regards Tanya "Pete_UK" wrote: Try moving the closed bracket to the end of the formula like so: =INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5") I assume in your example that you want to get data from 1!A5 Hope this helps. Pete On Oct 11, 11:29 am, Tanya wrote: Hi, I hope someone can help me I have the following formula and as I step through it, I can't see any problem with it, however it keeps returning an error. 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
Thank you for your support, it is greatly appreciated. Regards Tanya "vezerid" wrote: Do you want to refer to cell A5 of the chosen sheet? Then: =INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5" The issue here is what does A5 contain. It should contain a cell address like B1, AF13 etc. Is there a chance that your sheet names have space? If so: =INDIRECT("'"&VLOOKUP(B2,D41:F50,3,TRUE)&"'!")&A5 Notice the extra single quotes. The solution might be in a combination of the two suggestions. HTH Kostis Vezerides On Oct 11, 1:29 pm, Tanya wrote: Hi, I hope someone can help me I have the following formula and as I step through it, I can't see any problem with it, however it keeps returning an error. 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
welcome, good to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tanya" wrote in message ... Thank you, it works well. Regards Tanya |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
welcome, good to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tanya" wrote in message ... Thank you very much, it worked a treat and saved me a lot of time. Regards Tanya |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
Sorry, Pete. Posted to the wrong branch
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
You're welcome, Tanya - thanks for feeding back (to us all).
Pete On Oct 11, 1:01 pm, Tanya wrote: Thank you, it works well. Regards Tanya "Pete_UK" wrote: Try moving the closed bracket to the end of the formula like so: =INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5") I assume in your example that you want to get data from 1!A5 Hope this helps. Pete On Oct 11, 11:29 am, Tanya wrote: Hi, I hope someone can help me I have the following formula and as I step through it, I can't see any problem with it, however it keeps returning an error. 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- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PROBLEM WITH INDIRECT & VLOOKUP FORMULA
Easily done <bg
Pete On Oct 11, 1:48 pm, "Max" wrote: Sorry, Pete. Posted to the wrong branch -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF and SUMPRODUCT with INDIRECT formula problem | Excel Discussion (Misc queries) | |||
| Indirect Formula Problem | Excel Worksheet Functions | |||
| Indirect vlookup problem | Excel Discussion (Misc queries) | |||
| vlookup + indirect formula | Excel Discussion (Misc queries) | |||
| how to use 2 worksheets in one formula with INDIRECT & VLOOKUP | Excel Worksheet Functions | |||
Linear Mode
