Home |
Search |
Today's Posts |
#1
|
|||
|
|||
appending data to individual cell throug function
can I do this???
=IF(FIND(B5,C21),B21 & ", ","") & IF(FIND(B5,C22),B22 & ", ","") & IF(FIND(B5,C23),B23 & ", ","") & IF(FIND(B5,C24),B24 & ", ","") & IF(FIND(B5,C25),B25 & ", ","") & IF(FIND(B5,C26),B26 & ", ","") & IF(FIND(B5,C27),B27 & ", ","") & IF(FIND(B5,C28),B28 & ", ","") & IF(FIND(B5,C29),B29 & ", ","") & IF(FIND(B5,C30),B30 & ", ","") & IF(FIND(B5,C31),B31 & ", ","") & IF(FIND(B5,C32),B32 & ", ","") & IF(FIND(B5,C33),B33 & ", ","") & IF(FIND(B5,C34),B34 & ", ","") & IF(FIND(B5,C35),B35 & ", ","") & IF(FIND(B5,C36),B36 & ", ","") & IF(FIND(B5,C37),B37 & ", ","") & IF(FIND(B5,C38),B38 & ", ","") & IF(FIND(B5,C39),B39 & ", ","") & IF(FIND(B5,C40),B40 & ", ","") & IF(FIND(B5,C41),B41 & ", ","") & IF(FIND(B5,C42),B42 & ", ","") & IF(FIND(B5,C43),B43 & ", ","") & IF(FIND(B5,C44),B44 & ", ","") What I want to accomplish is creating a list of groups that any one person belongs to. Spreadsheet looks like this b5 = 'chuck' b6 = 'terry' b7 = 'mike' b8 = 'kathy' b21 = 'groupname1' c21 = 'chuck, terry, mike, kathy' b22 = 'groupname2' c22 = 'chuck, mike, kathy' b23 = 'groupname3' c23 = 'mike, terry, kathy' I would like to use a formula in the cells corresponding to the person - cells c5, c6, and c7 should show c5 = 'groupname1, groupname2' c6 = 'groupname1, groupname3' c7 = 'groupname1, groupname2' c8 = 'groupname1, groupname2, groupname3' I got an error in the formula above. Once it is perfected I could put a left(formula,len(c5)-2) to remove the extra ", ", but I can't seem to be able to get that function to work. Does anyone have a suggestion? |
#2
|
|||
|
|||
You are getting a #VALUE! because the find will fail when the name is not
present in a group ... which could be the majority of cases. You'll need to turn the formula round a little and make the group members cell absolute. For example: =IF(ISERROR(FIND(B5,$C$21)),"",$B$21 & ", ") & IF(ISERROR(FIND(B5,$C$22)),"",$B$22 & ", ") & IF(ISERROR(FIND(B5,$C$23)),"",$B$23 & ", ") ... I'll leave the rest to you Well, seeing as I've started and it's a real pain ... you need to remove any unnecesary spaces too: =IF(ISERROR(FIND(B5,$C$21)),"",$B$21& ", ")&IF(ISERROR(FIND(B5,$C$22)),"",$B$22&", ")& IF(ISERROR(FIND(B5,$C$23)),"",$B$23&", ")&IF(ISERROR(FIND(B5,$C$24)),"",$B$24&", ")& IF(ISERROR(FIND(B5,$C$25)),"",$B$25&", ")&IF(ISERROR(FIND(B5,$C$26)),"",$B$26&", ")& IF(ISERROR(FIND(B5,$C$27)),"",$B$27&", ")&IF(ISERROR(FIND(B5,$C$28)),"",$B$28&", ")& IF(ISERROR(FIND(B5,$C$29)),"",$B$29&", ")&IF(ISERROR(FIND(B5,$C$30)),"",$B$30&", ")& IF(ISERROR(FIND(B5,$C$31)),"",$B$31&", ")&IF(ISERROR(FIND(B5,$C$32)),"",$B$32&", ")& IF(ISERROR(FIND(B5,$C$33)),"",$B$33&", ")&IF(ISERROR(FIND(B5,$C$34)),"",$B$34&", ")& IF(ISERROR(FIND(B5,$C$35)),"",$B$35&", ")&IF(ISERROR(FIND(B5,$C$36)),"",$B$36&", ")& IF(ISERROR(FIND(B5,$C$37)),"",$B$37&", ")&IF(ISERROR(FIND(B5,$C$38)),"",$B$38&", ")& IF(ISERROR(FIND(B5,$C$39)),"",$B$39&", ")&IF(ISERROR(FIND(B5,$C$40)),"",$B$40&", ")& IF(ISERROR(FIND(B5,$C$41)),"",$B$41&", ")&IF(ISERROR(FIND(B5,$C$42)),"",$B$42&", ")& IF(ISERROR(FIND(B5,$C$43)),"",$B$43&", ")&IF(ISERROR(FIND(B5,$C$44)),"",$B$44&", ") Regards Trevor "tw" wrote in message ... can I do this??? =IF(FIND(B5,C21),B21 & ", ","") & IF(FIND(B5,C22),B22 & ", ","") & IF(FIND(B5,C23),B23 & ", ","") & IF(FIND(B5,C24),B24 & ", ","") & IF(FIND(B5,C25),B25 & ", ","") & IF(FIND(B5,C26),B26 & ", ","") & IF(FIND(B5,C27),B27 & ", ","") & IF(FIND(B5,C28),B28 & ", ","") & IF(FIND(B5,C29),B29 & ", ","") & IF(FIND(B5,C30),B30 & ", ","") & IF(FIND(B5,C31),B31 & ", ","") & IF(FIND(B5,C32),B32 & ", ","") & IF(FIND(B5,C33),B33 & ", ","") & IF(FIND(B5,C34),B34 & ", ","") & IF(FIND(B5,C35),B35 & ", ","") & IF(FIND(B5,C36),B36 & ", ","") & IF(FIND(B5,C37),B37 & ", ","") & IF(FIND(B5,C38),B38 & ", ","") & IF(FIND(B5,C39),B39 & ", ","") & IF(FIND(B5,C40),B40 & ", ","") & IF(FIND(B5,C41),B41 & ", ","") & IF(FIND(B5,C42),B42 & ", ","") & IF(FIND(B5,C43),B43 & ", ","") & IF(FIND(B5,C44),B44 & ", ","") What I want to accomplish is creating a list of groups that any one person belongs to. Spreadsheet looks like this b5 = 'chuck' b6 = 'terry' b7 = 'mike' b8 = 'kathy' b21 = 'groupname1' c21 = 'chuck, terry, mike, kathy' b22 = 'groupname2' c22 = 'chuck, mike, kathy' b23 = 'groupname3' c23 = 'mike, terry, kathy' I would like to use a formula in the cells corresponding to the person - cells c5, c6, and c7 should show c5 = 'groupname1, groupname2' c6 = 'groupname1, groupname3' c7 = 'groupname1, groupname2' c8 = 'groupname1, groupname2, groupname3' I got an error in the formula above. Once it is perfected I could put a left(formula,len(c5)-2) to remove the extra ", ", but I can't seem to be able to get that function to work. Does anyone have a suggestion? |
#3
|
|||
|
|||
thanks that did it
"Trevor Shuttleworth" wrote in message ... You are getting a #VALUE! because the find will fail when the name is not present in a group ... which could be the majority of cases. You'll need to turn the formula round a little and make the group members cell absolute. For example: =IF(ISERROR(FIND(B5,$C$21)),"",$B$21 & ", ") & IF(ISERROR(FIND(B5,$C$22)),"",$B$22 & ", ") & IF(ISERROR(FIND(B5,$C$23)),"",$B$23 & ", ") ... I'll leave the rest to you Well, seeing as I've started and it's a real pain ... you need to remove any unnecesary spaces too: =IF(ISERROR(FIND(B5,$C$21)),"",$B$21& ", ")&IF(ISERROR(FIND(B5,$C$22)),"",$B$22&", ")& IF(ISERROR(FIND(B5,$C$23)),"",$B$23&", ")&IF(ISERROR(FIND(B5,$C$24)),"",$B$24&", ")& IF(ISERROR(FIND(B5,$C$25)),"",$B$25&", ")&IF(ISERROR(FIND(B5,$C$26)),"",$B$26&", ")& IF(ISERROR(FIND(B5,$C$27)),"",$B$27&", ")&IF(ISERROR(FIND(B5,$C$28)),"",$B$28&", ")& IF(ISERROR(FIND(B5,$C$29)),"",$B$29&", ")&IF(ISERROR(FIND(B5,$C$30)),"",$B$30&", ")& IF(ISERROR(FIND(B5,$C$31)),"",$B$31&", ")&IF(ISERROR(FIND(B5,$C$32)),"",$B$32&", ")& IF(ISERROR(FIND(B5,$C$33)),"",$B$33&", ")&IF(ISERROR(FIND(B5,$C$34)),"",$B$34&", ")& IF(ISERROR(FIND(B5,$C$35)),"",$B$35&", ")&IF(ISERROR(FIND(B5,$C$36)),"",$B$36&", ")& IF(ISERROR(FIND(B5,$C$37)),"",$B$37&", ")&IF(ISERROR(FIND(B5,$C$38)),"",$B$38&", ")& IF(ISERROR(FIND(B5,$C$39)),"",$B$39&", ")&IF(ISERROR(FIND(B5,$C$40)),"",$B$40&", ")& IF(ISERROR(FIND(B5,$C$41)),"",$B$41&", ")&IF(ISERROR(FIND(B5,$C$42)),"",$B$42&", ")& IF(ISERROR(FIND(B5,$C$43)),"",$B$43&", ")&IF(ISERROR(FIND(B5,$C$44)),"",$B$44&", ") Regards Trevor "tw" wrote in message ... can I do this??? =IF(FIND(B5,C21),B21 & ", ","") & IF(FIND(B5,C22),B22 & ", ","") & IF(FIND(B5,C23),B23 & ", ","") & IF(FIND(B5,C24),B24 & ", ","") & IF(FIND(B5,C25),B25 & ", ","") & IF(FIND(B5,C26),B26 & ", ","") & IF(FIND(B5,C27),B27 & ", ","") & IF(FIND(B5,C28),B28 & ", ","") & IF(FIND(B5,C29),B29 & ", ","") & IF(FIND(B5,C30),B30 & ", ","") & IF(FIND(B5,C31),B31 & ", ","") & IF(FIND(B5,C32),B32 & ", ","") & IF(FIND(B5,C33),B33 & ", ","") & IF(FIND(B5,C34),B34 & ", ","") & IF(FIND(B5,C35),B35 & ", ","") & IF(FIND(B5,C36),B36 & ", ","") & IF(FIND(B5,C37),B37 & ", ","") & IF(FIND(B5,C38),B38 & ", ","") & IF(FIND(B5,C39),B39 & ", ","") & IF(FIND(B5,C40),B40 & ", ","") & IF(FIND(B5,C41),B41 & ", ","") & IF(FIND(B5,C42),B42 & ", ","") & IF(FIND(B5,C43),B43 & ", ","") & IF(FIND(B5,C44),B44 & ", ","") What I want to accomplish is creating a list of groups that any one person belongs to. Spreadsheet looks like this b5 = 'chuck' b6 = 'terry' b7 = 'mike' b8 = 'kathy' b21 = 'groupname1' c21 = 'chuck, terry, mike, kathy' b22 = 'groupname2' c22 = 'chuck, mike, kathy' b23 = 'groupname3' c23 = 'mike, terry, kathy' I would like to use a formula in the cells corresponding to the person - cells c5, c6, and c7 should show c5 = 'groupname1, groupname2' c6 = 'groupname1, groupname3' c7 = 'groupname1, groupname2' c8 = 'groupname1, groupname2, groupname3' I got an error in the formula above. Once it is perfected I could put a left(formula,len(c5)-2) to remove the extra ", ", but I can't seem to be able to get that function to work. Does anyone have a suggestion? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding a function value in a cell until function used | Excel Worksheet Functions | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Repeat Cell Data | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Automatic cell increment with data from sheet 1 to sheet 2 | Excel Worksheet Functions |