Complex nest IF function

[kyber]

Hello,
Rule 1 - Scenario 1 - if the payer is any of the of the yellow highlighted ones then the outcome is automatically No Note
Rule 2 - If in Scenario 1, the blue highlighted texts appear then the next step is checking at scenario 2 (unless left blank). If the response is No Show, then the outcome needs to be No Note. If the response is Show then the outcome needs to be Note
Rule 3 - If Scenario 1 and 2 satisfy the conditions then we need to move onto 3 to see if the response is either Yes or No. If no, then the outcome is No Note. If yes then it's Note.

Attached is a flowchart to help explain Untitled.png

[TMS]

This produces the results shown in the sample workbook.

Please Login or Register  to view this content.

You could OR the "No Note" conditions but I suspect the logic would be less easy to follow. As it is, it follows the flow chart.

[Bo_Ry]

Please try

=IF(C2="","",IF(OR(LEFT(C2)<>"A",D2="No Show",E2<>"Yes"),"No ",)&"Note")


=IF(C2="","",IF(AND(LEFT(C2)="A",D2<>"No Show",E2="Yes"),,"No ")&"Note")

[kyber]

I am not able to fully work this out. Though I'm getting close. What is the purpose of the "A"

[kyber]

Is there a way to link a list of approved and nonapproved payer types from a separate sheet that way they need to be individually listed in the formula?

[TMS]

What was wrong with the solution in post #12?


Edit: yes, that should be post #2, not post #12

[kyber]

Do you mean #2?

I was finally able to to get it to work moments ago. However, I was wondering if there's a way to reference a list of approved or not approved list of payers from another tab so that I don't have to manually update the formula as opposed the referenced cells in the separate columns.

[TMS]

Do you mean #2?

That would be the one .

Always best to ask for what you want at the outset, not a month on. I've slept since then and, in all honesty, I’ve forgotten what the formula did and why.

If the formula works, mark the thread solved and start a new one with the new requirement. That way, you might get more input. Probably sensible to provide a link to this thread for background.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[kyber]

My apologies, I was on vacation.