 |
 | 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 / November 2006Compare
Ok This is the setup..... A24 -------------------------------------------------------- George Shirley Range -------------------------------------------------------- HiddenStats!A20:A150 Format of Names in Range -------------------------------------------------------- G. Shirley Code -------------------------------------------------------- =INDEX(HiddenStats!$A$20:$N$150,MATCH(A24,HiddenStats!$A$20:$A$150,FALSE),7) I need it to recognise "G.Shirley" as George Shirley, is there a way? Any help would be appreciated! Try this: =INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)&"."&MID(A24,FIND(" ",A24),50),HiddenStats!$A$20:$A$150,FALSE),7)  SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Ok This is the setup..... A24 -------------------------------------------------------- George Shirley Range -------------------------------------------------------- HiddenStats!A20:A150 Format of Names in Range -------------------------------------------------------- G. Shirley Code -------------------------------------------------------- =INDEX(HiddenStats!$A$20:$N$150,MATCH(A24,HiddenStats!$A$20:$A$150,FALSE),7) I need it to recognise "G.Shirley" as George Shirley, is there a way? Any help would be appreciated! Use a helper cell to parse the name: A24 = George Shirley A25 = formula: =LEFT(A24)&". "&MID(A1,FIND(" ",A24)+1,255) Returns: G. Shirley Then: .........MATCH(A25,HiddenStats!$A$20:$A$150,FALSE)........ Or: .........MATCH(LEFT(A24)&". "&MID(A1,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE)........ Biff > Ok This is the setup..... > [quoted text clipped - 16 lines] > I need it to recognise "G.Shirley" as George Shirley, is there a way? > Any help would be appreciated! Thanks for the help guys! I combined what u both said into this and it works perfectly! =INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)&". "&MID(A24,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE),7) Dennis > Use a helper cell to parse the name: > [quoted text clipped - 37 lines] > > I need it to recognise "G.Shirley" as George Shirley, is there a way? > > Any help would be appreciated!- Hide quoted text -- Show quoted text - One more question, is there a way to have the cells not display #VALUE when A25 is blank? Dennis > Use a helper cell to parse the name: > [quoted text clipped - 37 lines] > > I need it to recognise "G.Shirley" as George Shirley, is there a way? > > Any help would be appreciated!- Hide quoted text -- Show quoted text - Try this: =IF(A24<>"",INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)&"."&MID(A24,FIND(" ",A24),50),HiddenStats!$A$20:$A$150,FALSE),7),"") SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== One more question, is there a way to have the cells not display #VALUE when A25 is blank? Dennis On Nov 4, 2:50 pm, "Biff" <biffinp...@comcast.net> wrote: > Use a helper cell to parse the name: > [quoted text clipped - 40 lines] > > I need it to recognise "G.Shirley" as George Shirley, is there a way? > > Any help would be appreciated!- Hide quoted text -- Show quoted text - Thanks, but I figured it out, I used this... =IF(ISERROR(INDEX(HiddenStats!$A$20:$N$148,MATCH(LEFT(A24)&". "&MID(A24,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$148,FALSE),2)),"",INDEX(HiddenStats!$A$20:$N$148,MATCH(LEFT(A24)&". "&MID(A24,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$148,FALSE),2)) For cell A24 of course, not A25 > Try this: > [quoted text clipped - 61 lines] > > > I need it to recognise "G.Shirley" as George Shirley, is there a way? > > > Any help would be appreciated!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - >is there a way to have the cells not display #VALUE >when A25 is blank? Try this. It will leave the cell blank: =IF(A25="","",your_formula_here)) Biff One more question, is there a way to have the cells not display #VALUE when A25 is blank? Dennis On Nov 4, 2:50 pm, "Biff" <biffinp...@comcast.net> wrote: > Use a helper cell to parse the name: > [quoted text clipped - 40 lines] > > I need it to recognise "G.Shirley" as George Shirley, is there a way? > > Any help would be appreciated!- Hide quoted text -- Show quoted text - >is there a way to have the cells not display #VALUE >when A25 is blank? Try this. It will leave the cell blank: =IF(A25="","",your_formula_here)) Biff One more question, is there a way to have the cells not display #VALUE when A25 is blank? Dennis On Nov 4, 2:50 pm, "Biff" <biffinp...@comcast.net> wrote: > Use a helper cell to parse the name: > [quoted text clipped - 40 lines] > > I need it to recognise "G.Shirley" as George Shirley, is there a way? > > Any help would be appreciated!- Hide quoted text -- Show quoted text - |
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.