Help with complicated if function.

rthakur

Board Regular
Joined
Feb 22, 2005
Messages
121
I apologize for creating a little confusion.
Please allow me to rephrase and provide more clarity, here we go:
Current Formula: =IF(Q5="y","Case completed",IF(M5,"Referred to U / W",IF(ISNUMBER(F5),IF(ISNUMBER(G5),IF(TODAY()>=G5+21,"Second Letter",IF(ISNUMBER(I5),IF(TODAY()>=I5+21,"Consult with Manager","No action required"),"Review Initiated")),"Case Logged"),"")))

A case is received to perform medical investigation. As soon as the case is received, it is logged under “Review Start date” ie Column F.
If F5 is filled with date = "Case Logged" but if F5 is empty = *No message should Display*
Above requirement is working fine.

If F5 is filled with date and G5 is also filled with date = "Review Initiated"
If today() >=21 days from G5 = "Second Letter" however if today() is under 21 days from G5 = "No action Required"
Above requirement is working fine.

If I5 is filled with date and today() >= 21 days from I5 = "Consult with Manager" however if today() is under 21 days from I5 = "No Action Required)
Above underlined scenario is not working, the message remains “Second Letter” even when today() is under I5.

Additonal info: There are 2 letter notifications, Initial letter and Second letter. Everytime a letter is sent out, a reviewer have 21 days from the date that letter was sent out to act on that case again.

If after sending out first letter (G5), a reviewer does not get a response in 21 days, he/she sends out a second letter (I5) but if after 21 days from the second letter reviewer still does not get any response, he/she must consult with their manager to take administrative action. I am trying to create a message(s) for each scenario.

I hope I have provided more details on my issue. Please let me know if I missed anything or more info is needed.

Best Regards,

rthakur
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi rthakur:

In your setup, there are a number of dates, with a number of possibilities --you may have to sort them all out ... what happens under what set of conditions -- it may not be that easy to do without real data and realistic constraints. It will need a lot of tracking of what happens with what set of entries, and what constraints need to be built in.

So, I am sorry ... with what you have stated, I am not able to provide any improvement to the formulation I have already provided.

If you can describe fulfilling a specific condtion unambiguously, I will be glad to open this file up again.

Good Luck!
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I would think that you need to swap round the IF conditions so that those related to I5 appear before those related to G5, something like

=IF(Q5="y","Case completed",IF(M5,"Referred to U / W",IF(ISNUMBER(F5),IF(ISNUMBER(I5),IF(TODAY()>=I5+21,"Consult with Manager","No action required"),IF(ISNUMBER(G5),IF(TODAY()>=G5+21,"Second Letter","Review Initiated"),"Case Logged")),"")))
 

rthakur

Board Regular
Joined
Feb 22, 2005
Messages
121
Mr. Anand, thank you for helping me out again. In future, I will try to explain all the conditions with more details in the first try...

Mr. Barry, your formula is just what the doctor ordered! Thank you, it is working!

Best Regards,

rthakur
 

Forum statistics

Threads
1,081,849
Messages
5,361,673
Members
400,644
Latest member
ndroger1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top