Checking contents or two different cells using IF function

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
110
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
110
Yes, but I dont seem to be able to make it work with that either.
 

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
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

Board Regular
Joined
Mar 7, 2014
Messages
110

ADVERTISEMENT

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

Active Member
Joined
Jan 9, 2014
Messages
473
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

Board Regular
Joined
Mar 7, 2014
Messages
110
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

Active Member
Joined
Jan 9, 2014
Messages
473
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,488
Messages
5,529,164
Members
409,852
Latest member
Perry123
Top