Auto-population Troubles

Good Morning Excel Experts,

Hope this thread finds you well. I am trying to form an Excel Worksheet that will automate comments in one Column based upon what letter is entered into Column C. The formula I have so far is:

=IF(C2="D","Account Requiring Assist Code Adjustment",IF(C2="E","Adjust Source Code",IF(C2="F","Adjust LM Counselor",IF(C2="G","Adjust Plan Type",IF(C2="H","Email Assigned Rep","")))))

However, I am looking for a non-VBA way to have it where the user can enter multiple letters into Column C and have multiple comments appear.

For Example, if Column C had D, E & H entered. The comments would appear as, “Account Requiring Assist Code Adjustment,” “Adjust Source Code,” and “Email Assigned Rep.”

Would the entry method in Column C to obtain multiple comments need to be entered with a comma and no space? A semicolon and no space? A comma with a space?

That would be very hard to do without VBA.

Please Login or Register  to view this content.

The formula in D11 and copied down is

=Catif(ISNUMBER(MATCH($A$2:$A$7, MySplit(A11), 0)), $B$2:$B$7)

It MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

Please Login or Register  to view this content.

You can put the list anywhere you like.

Maybe I can help. In your spreadsheet, Where is your list of comments and what are they linked to (Letter item)? Might as well modify shg's UDF to match what you expect to use.

Here is your sheet back with formula's set up for your sheet. There was no need to modify the code itself. As you said, you can hide the list if you want
Questions?

Excel does not recognize the functions "CatIf" and "MySplit". These are called UDF (User Defined Functions) which shg created with the code in Post #4. Here's what you need to do.

In your workbook, hit ALT + F11 to open the VBA Editor. In that window, Insert > Module
There will be a white text box on the right side of the window. Copy and paste shg's code from post #4 into that box
Close the VBA Editor. (No need to save as the VBA will now be part of your workbook and will save with your workbook)

Now Excel should recognize the names (Hit F9 to refresh if you need to). Did that help?

Okay, we're getting there. Your formula is correct EXCEPT this is an Arrayed Function. That means that when you enter the formula, you need to hit CNTRL SHFT ENTER rather than a simple ENTER. If you do this properly you'll see brackets {} appear around your formula. You can then drag it down.

I also note that shg's code doesn't like blank spaces between your comma's and entries so, rather than play with his code, I suggest we remove the blanks using SUBSTITUTE when we split it.
your formula becomes

=Catif(ISNUMBER(MATCH($A$2:$A$10, MySplit(SUBSTITUTE(C12," ","")), 0)), $B$2:$B$10)