Results 1 to 5 of 5

Thread: If Formula Function

  1. #1
    Seeker dreinisch's Avatar
    Join Date
    Sep 2011
    Location
    Troy, Missouri, United States
    Posts
    9
    Articles
    0

    If Formula Function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good Afternoon Gurus,

    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?

  2. #2
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    33
    Articles
    0
    I am really curious about the answer. Unfortunately still nobody answered it (

  3. #3
    I know this isn't doing exactly what you explained, but it is an option. This solution does use a helper column. Don't know why, but seems like alot of people have an aversion to using helper columns. You did not mention that you had to enter the letters in one cell , you said Column C. This solution will use Cell C2-C6 to enter letters. I also hid Column D that is used as the helper Column. You can always move the helper column to like AA if you need to due to the way your sheet will be layed out.


    i used your If formula in D2-D6

    in A2-A6

    =D2&" "&D3&" "&D4&" "&D5&" "&D6&" "
    Attached Files Attached Files

  4. #4
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    33
    Articles
    0
    I think I solved your problem.
    =IF(ISERROR(VLOOKUP(INDEX(MID(D2;ROW(INDIRECT("1:"&LEN(D2)));1);ROW(INDIRECT("1:"&LEN(D2))));F2:G6;2;0));"";VLOOKUP(INDEX(MID(D2;ROW(INDIRECT("1:"&LEN(D2)));1);ROW(INDIRECT("1:"&LEN(D2))));F2:G6;2;0)) (array function)
    The results of the query will be shown in 5 different cells but you can enter your letters into the same cell (ex dfe). I made a table to take the info from. (F2:F6=d;e;f;g;h) and (g2:g6 ; their coments). d2 is the cell where u will enter your values. To enter the formula select five cells, paste them and enter control+shift+enter.
    There is only one drawback.When you will enter single letter those five cells will show the same results. It can be done but formula will be longer.
    Attached Files Attached Files
    Last edited by eisayev; 2012-10-06 at 09:55 PM.

  5. #5
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    33
    Articles
    0
    My final answer. All in one cell
    =IFERROR(VLOOKUP(INDEX(MID(C2;ROW(INDIRECT("1:"&LEN(C2)));1);1);E2:F6;2;0);"")&CHAR(10)&IFERROR(VLOOKUP(INDEX(MID(C2;ROW(INDIRECT("1:"&LEN(C2)));1);2);E2:F6;2;0);"")&CHAR(10)&IFERROR(VLOOKUP(INDEX(MID(C2;ROW(INDIRECT("1:"&LEN(C2)));1);3);E2:F6;2;0);"")&CHAR(10)&IFERROR(VLOOKUP(INDEX(MID(C2;ROW(INDIRECT("1:"&LEN(C2)));1);4);E2:F6;2;0);"")&CHAR(10)&IFERROR(VLOOKUP(INDEX(MID(C2;ROW(INDIRECT("1:"&LEN(C2)));1);5);E2:F6;2;0);"")
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •