return text

B

Bobby

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
 
M

Max

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
 
M

Max

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top