I have a worksheet which allows me to see the status of subscriptions exported from our CMS and to show data input errors made by colleagues. This has been working well until someone decided to add another class of subscriber called 'Trial'.
I now need to show this on the error sheet which is currently calculated with the following:
=IF(ISBLANK(A142),,IF(AND(L142-TODAY()>1,OR(I142="Subscriber RENEWAL",I142="Subscriber NEW")),"",$J$5&I142))
This checks that column A contains data (client reference number) then looks for 2 subscriber types (column I) and ignores them if they are present and not overdue (column L) or concatenates an error message in J5 with the subscriber type in column I and send the results to an error sheet.
I have now been asked to show if they are a 'Trial' subscriber. Column L may not have a date in so I need to show all occurrences of 'Trial' (in column I) so if they are a Trial client, the cell returns TRIAL.
I am sure it can't be that hard but inspiration has left me today! Any help gratefully received.