Forum Discussion
Solved
Cell Populating Based of Other Cell Conditions
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.
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.
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.
- 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. 😞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.
