 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2007PROBLEM 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 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 > Hi, I hope someone can help me > [quoted text clipped - 14 lines] > Regards > tanya Thank you, it works well. Regards Tanya > Try moving the closed bracket to the end of the formula like so: > [quoted text clipped - 24 lines] > > Regards > > tanya welcome, good to hear that.  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Thank you, it works well. > > Regards > Tanya Sorry, Pete. Posted to the wrong branch SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Easily done <bg> Pete > Sorry, Pete. Posted to the wrong branch > -- > Max > Singaporehttp://savefile.com/projects/236895 > xdemechanik > --- You're welcome, Tanya - thanks for feeding back (to us all). Pete > Thank you, it works well. > [quoted text clipped - 31 lines] > > - Show quoted text - 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 > Hi, I hope someone can help me > [quoted text clipped - 14 lines] > Regards > tanya Thank you for your support, it is greatly appreciated. Regards Tanya > Do you want to refer to cell A5 of the chosen sheet? Then: > [quoted text clipped - 32 lines] > > Regards > > tanya One guess .. Try it as: =INDIRECT("'"&VLOOKUP(B2,D41:F50,3,TRUE)&"'!A5") SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi, I hope someone can help me > [quoted text clipped - 14 lines] > Regards > tanya Thank you very much, it worked a treat and saved me a lot of time. Regards Tanya > One guess .. > [quoted text clipped - 18 lines] > > Regards > > tanya welcome, good to hear that. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > > Thank you very much, it worked a treat and saved me a lot of time. > > Regards > Tanya |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.