| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare Excel Workbooks | Excel Discussion (Misc queries) | |||
| 'Compare side by side' for work sheets | Excel Discussion (Misc queries) | |||
| How can I compare Excel files | Excel Discussion (Misc queries) | |||
| How do I compare FORMULAS in two workbooks | Excel Discussion (Misc queries) | |||
| Is there a way to compare 2 spreadsheets with Excel? | Excel Discussion (Misc queries) | |||
Hybrid Mode
