Forum Discussion

resham1985's avatar
resham1985
Brass Contributor
May 28, 2020
Solved

Formulas

We have a range where each categories has notes.   Please find the below condition where 1, 2 are the volumes. CM- Account Credit (contl) category should be 1-Credit or 2-hold or 1-rejected and...
Macros and VBA
  • SergeiBaklan's avatar
    SergeiBaklan
    May 29, 2020

    resham1985 

    I'd also recommend to re-design right table a bit as

    with Helper column calculated as

    =TRIM(IFERROR(
     RIGHT(
       $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)),
       LEN($H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))) -
       FIND("-",$H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)))),
 $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))))

     

    Status could be calculated as

    by formula

    =IF(COUNTIFS(
    $G$3:INDEX($G$3:$G$100,COUNTA($G$3:$G$100)),
    $A2,
    $I$3:$I$9,
    IFERROR(RIGHT($B2,LEN($B2)-FIND("-",$B2)),$B2)
),"No Error", "Error")
resham1985's avatar
resham1985
Brass Contributor
May 29, 2020
Thank you mtarler for the response.

But the problem is notes or comments will not be same as it changes as per the volume

Notes for CM- Account Credit (contl) will be
volume processed-Credit
volume processed-rejected
volume processed-hold

Formula should be number-notes

mtarler's avatar
mtarler
Silver Contributor
May 29, 2020

resham1985  So if I understand correctly the note should have a number followed by "-Credit" or "-rejected" etc...  

I would recommend that you split those into 2 separate columns to make your life easier and the sheet more functional.  If you need a column with that specific format you can "build" it.  For example you have 1 col that is # and another column that is action (e.g. credit vs rejected etc...).  You can have specific controls on each of those columns.  The a 3rd col that would TEXTJOIN those 2 columns with an "-" or in the last case just where there are no numbers just use the later col.

 

That said, if you still want to limit the entry to only that format you can make the validation formula more complicated.  Here is an example where I changed the Data Validation formula above using the search function to make sure at least the non-numbered part is correct:

 

=OR(AND(B2="category 1",OR(search("comment 1",C2),search("comment 2",C2),search("comment 3",C2))), AND(B2="category 2",OR(search("comment 4",C2),search("comment 5",C2))), AND(B2="category 3",OR(search("comment 6",C2),search("comment 7",C2))))

 

 

Resources