PDA

View Full Version : Use IF or a VBA to check multiple fields and display specific text in a cell?



reubendayal
04-25-2014, 02:34 AM
Hi There,

I'm tracking my case work at different stages of progress. One of the important columns I'm tracking needs to check data in specific fields and then provide me an alert as a text change using a huge IF formula and overlaying a conditional formatting color change in each specific field of that column (every field if for its respective case). As I'm checking multiple references within that IF formula, it doesnt seem to be working. What is the solution to this? do i need to use a VBA code instead? Cos in total, I'd need it to check 3-4 different fields of that case and accordingly change text.

here's the formula for a case in row 4: =IF(ISBLANK(M4);" ";(IF(TODAY()>M4+13;"Task Date Due";(IF(O4="Customer Completed Task";"Check Case Progress with Authorities";" ")))))

To explain this further, In M4 I'm capturing the date I file an application with the authorities. In O4 I update Status of the case using a drop-down list. And all my alerts are provided in the Q4 cell(column for the rest of the cases).

I'm first checking if its been 13 days since the customer's case was filed with the authorities. This I'm doing manually now by entering the date I change the status to 'Case Filed' in the column 'O' (it would be great to have a vba do this automatically, but I dont know VBA coding). But in case the customer has completed the task, I change the status to 'Customer completed task'.

At this time the 13 day tracking is of no significance. As after 14 days of the filing of the case I need to check with the authorities on the case status. This I'm trying to achieve in the second part of my formula. But it clearly doesn't work.

Thirdly, when I contact the authorities and receive a case no. I simply change O4 to 'Case confirmed with authorities'.

But with three different arguments/references the IF command just fails.

Any help that one could provide will be great!

many thanks.
Reuben

SamT
04-25-2014, 07:50 AM
Try this.


=IF(ISBLANK(M4);" ";(IF(TODAY()>M4+13;(IF(O4="Customer Completed Task";"Check Case Progress with Authorities";"Task Date Due"))))

The logic is: If has an entry depends on if M4 has a date; Which entry depends on the entry in O4.

Using the "Which Entry" logic lets you expand the range of possible entries:


IF(O4="Customer Completed Task";"Check Case Progress with Authorities";IF(O4="Customer Turned Green";"Call Decorator";IF(O4="Customer Turned into Dog";"Call animal Control";"Task Date Due"))...