Cell Populating Based of Other Cell Conditions

[travisrussell]

Good Day All!

I have created a database that has a Conditional Formatting List in cell (N3) containing a dropdown list of "Open", "Past Due", and "Closed".

I am wanting to eliminate the dropdown list and base the status off of a few cell inputs.

I am aiming to have cell (N3) display "Open" if a value is entered into the 'Date Submitted' cell (F3),

or if my 'Days Remaining' cell (K3)=<0 , display "Past Due" in (N3),

or if the 'Date Closed' cell (O3) contains a value, (N3) displays "Closed"

I hope I explained that ok. If someone could point me in the right direction, I would appreciate it.

As always, thank you.

Regards,

Travis R.

[#2]

@travisrussell 

If someone could point me in the right direction, I would appreciate it.

I'm going to take you literally on that, and just point you in the right direction, assuming that you mean once so pointed you'd actually appreciate, as well, doing some figuring the rest of it out on your own.

Here's that pointer: Look at the IFS function. 

If you do want more help, feel free to come back and ask for it. It would help us help you further if you accompanied that request by posting of your database on OneDrive or GoogleDrive with a link granting us edit access to that spreadsheet.

[#3]

@mathetes , Thank you. I do very much like to try and figure it out. Unfortunately, with my corporate restrictions, I am not able to utilize OneDrive or GoogleDrive.

This is what I came up with,
=IFS(F3="","",F3>O3,"Open",J3>90,"Past Due",O3>F3,"Closed")

Now it will let me put the formula in, but When (J3)>90 It wont display "Past Due" until a date is put into "Date Closed" (O3) and won't display "Closed" when a date is put into (O3).

Another hint please? Thank you so much. Sorry for not being able to share the workbook. :(

[#4 · Accepted solution]

@travisrussell 

Here you go. I've created what I think is your desired formula. I had to do a little reading between the lines of your description to come up with what I think are the several column headings. All you need to do is enter dates in the two yellow columns.

The formula in column J calculates the days since open

Column K figures how many days left until the boundary of "Past Due" is reached

Column N contains the formula you were trying to create. I suspect your difficulty was that IFS starts calculating from the left and STOPS once it reaches a condition that is met. That's why the new formula, 

=IFS(F3="","",O3>F3,"Closed",AND(F3>0,K3>=0),"Open",J3>90,"Past Due")

gives as the first meaningful condition the entry of a date into the "Closed" column, where that date is greater than the date "Opened" and then, if it's not Closed, proceeds to the other conditions.

Rather than hard-code the 90day limit, I made it a separate field (B5), so that you could change your policy to, say, 60 days simply by changing that one number; the formulas would adjust automatically because they refer to that B5 field.

[#5]

@mathetes Thank you for your help. I see I was in the "ball park", but needed that AND function as well. Makes sense, and it works!

Reading it aloud helps too, I am learning. Thank you so much for taking the time to help and teaching as well.

All the Best,

Travis

[#6]

@travisrussell 

You were definitely in the ballpark. That website where the IFS function was described for you is a good one for your continued learning. There are also many very helpful YouTube videos that I'd commend to you.

Reading it aloud helps too, I am learning.

Good insight on your part. Yes. And describing out loud in plain English when trying to develop the formula in the first place.