Checking contents or two different cells using IF function

[REUBEN]

Hi There,

I have posted this query in another context on this page. However, if this is possible using the IF function, it would be ideal for my use.

The formula I'm working on is:

=IF(ISBLANK(A1);" ";(IF(TODAY()>A1+13;"Date Due";(IF(B1="Completed Task";"Check Case Progress";" ")))))

The issue is that I am trying to check two different cells references and that's creating too many arguments for the formula. The formula is in a column that shows me alerts for cases on each row. I would actually like to add more checks in this column but I guess the way to do that will be a VBA.

Any suggestions on the above? Is there better way of doing this?

Thank you.

 

[ghrain22]

have you looked into the and function:
AND function - Excel

 

[REUBEN]

Yes, but I dont seem to be able to make it work with that either.

 

[ghrain22]

Your above formula works fine for me. Although, I had to convert everything to commas:
=IF(ISBLANK(A1)," ",(IF(TODAY()>A1+13,"Date Due",(IF(B1="Completed Task","Check Case Progress"," ")))))
What version of excel are you in?

I'm not sure what your end goal is or what additional conditionals you plan on adding to it in the future. Pending on what they are it might be worthwhile to create a list of inputs and outputs and perform a lookup on said list.

 

[REUBEN]

Hi again,

Thanks for helping. I am using Excel 2013. And the goal is based on the output in the cell where this formula is written, I have conditionally formatted the entire column to change colors accordingly. Its like an alert mechanism.

the issue arrives with B as B is a drop down menu and when I change it from various other menu options to "Completed Task", the formula is unbalanced. and works funnily. Because the drop down menu keeps changing as it is a current status dropdown list. So in some cases it works and in some it doesnt.

 

[ghrain22]

Are all (or almost all) of the potential outputs based on the dropdown?

If so my recommendation of a list with a lookup would be a good solution.
Something like:
=If(TODAY()>A1+13,"Date Due", vlookup($B$1, List, 2, False))

 

[REUBEN]

Are all (or almost all) of the potential outputs based on the dropdown?

If so my recommendation of a list with a lookup would be a good solution.
Something like:
=If(TODAY()>A1+13,"Date Due", vlookup($B$1, List, 2, False))

That's a good suggestion.

I've not worked with Vlookup so much but it could be useful.

I do have all or most of my status updates being chosen from the dropdown column. But then there are exceptions in case of the the above alert, which I check from the date column. The alert column was specifically made for following up on a specific alert. But now I am of the opinion that this is not good enough.

 

[ghrain22]

Again, I don't know of all your possible inputs/outputs, so it's difficult to give a definitive solution. If you're using a dropdown the aforementioned look up is a reasonable solution. For the other conditions, you can look into other alerting methods- conditional formatting, data validation, or just having them display in a different cell(than the dropdown alert). Good luck