|
Compare
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,HiddenSt ats!$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! |
Compare
Try this:
=INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)&". "&MID(A24,FIND(" ",A24),50),HiddenStats!$A$20:$A$150,FALSE),7) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "CribbsStyle" wrote in message oups.com... 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,HiddenSt ats!$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! |
Compare
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 "CribbsStyle" wrote in message oups.com... 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,HiddenSt ats!$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! |
Compare
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 On Nov 4, 2:50 pm, "Biff" wrote: 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 "CribbsStyle" wrote in ooglegroups.com... 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,HiddenSt ats!$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!- Hide quoted text -- Show quoted text - |
Compare
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" wrote: 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 "CribbsStyle" wrote in ooglegroups.com... 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,HiddenSt ats!$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!- Hide quoted text -- Show quoted text - |
Compare
Try this:
=IF(A24<"",INDEX(HiddenStats!$A$20:$N$150,MATCH(L EFT(A24)&"."&MID(A24,FIND(" ",A24),50),HiddenStats!$A$20:$A$150,FALSE),7), "") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "CribbsStyle" wrote in message ps.com... 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" wrote: 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 "CribbsStyle" wrote in ooglegroups.com... 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,HiddenSt ats!$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!- Hide quoted text -- Show quoted text - |
Compare
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 "CribbsStyle" wrote in message ps.com... 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" wrote: 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 "CribbsStyle" wrote in ooglegroups.com... 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,HiddenSt ats!$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!- Hide quoted text -- Show quoted text - |
Compare
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 "CribbsStyle" wrote in message ps.com... 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" wrote: 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 "CribbsStyle" wrote in ooglegroups.com... 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,HiddenSt ats!$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!- Hide quoted text -- Show quoted text - |
Compare
Thanks, but I figured it out, I used this...
=IF(ISERROR(INDEX(HiddenStats!$A$20:$N$148,MATCH(L EFT(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 On Nov 4, 5:09 pm, "RagDyeR" wrote: Try this: =IF(A24<"",INDEX(HiddenStats!$A$20:$N$150,MATCH(L EFT(A24)&"."&MID(A24,FIND*(" ",A24),50),HiddenStats!$A$20:$A$150,FALSE),7), "") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "CribbsStyle" wrote in glegroups.com... 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" wrote: 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 "CribbsStyle" wrote in ooglegroups.com... 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,HiddenSt ats!$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!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
| All times are GMT +1. The time now is 04:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com