return text

[Guest]

Sheet1 has CB for names, CC for amounts, CD for comments that are all blank.
S2 has CC with a list of names, CN where the amounts from S1CC using the
SUMIF formula go and CV where I want the comments from S1 CD to go. The
SUMIF works fine but I am having a problem moving the text. When I type a
name into S1CB and a corresponding comment in S1CD I want it to move to S2

Thanks
Bobby

 

[Guest]

You can't use SUMIF to return text from the comments col in Sheet1, if that's
what you tried to do.

Assuming the comments in Sheet1's col CD are entered uniquely, ie only one
instance per name in col CB, then in Sheet2, you could try INDEX/MATCH

In Sheet2,

In CV2:
=INDEX(Sheet1!CD:CD,MATCH(CC2,Sheet1!CB:CB,0))
Copy down

 

[Guest]

In CV2:

=INDEX(Sheet1!CD:CD,MATCH(CC2,Sheet1!CB:CB,0))

There are 2 possible extensions to the above core expression:

1. With an error trap to return "blanks" for unmatched cases, use instead in
CV2, copied down:
=IF(ISNA(MATCH(CC2,Sheet1!CB:CB,0)),"",INDEX(Sheet1!CD:CD,MATCH(CC2,Sheet1!CB:CB,0)))

2. With 2 sequential error traps to return "blanks" for (a) any unmatched
cases, and (b) where there's no comments in Sheet1's col CD (ie where the
INDEX returns zeros), use instead in CV2, copy down:
=IF(ISNA(MATCH(CC2,Sheet1!CB:CB,0)),"",IF(INDEX(Sheet1!CD:CD,MATCH(CC2,Sheet1!CB:CB,0))=0,"",INDEX(Sheet1!CD:CD,MATCH(CC2,Sheet1!CB:CB,0))))


An alternative to get a neat clutter-free look on the sheet is simply to
switch off/suppress zeros display on the sheet. Click Tools > Options > View
tab. Uncheck "Zero values" > OK.


---