Forum Discussion
IF equations in a Dropdown list
Admittedly, there's much that can be done with VBA. But if you do not want to deal with the issues that come from allowing users to edit workbooks that contain VBA code, perhaps you could just capture enough data such that you would not need a dropdown list at all, and can calculate the Application Status...
(Note: I don't know your organization's requirements for status changes, so some of this is made up.) E.g., let's say you use column C to contain the date a grant request was received (making it In Progress), column E to contain the date the request was acknowledged (making it Pending), column F to indicate the date a decision was made and column G to indicate the approval/denial decision, and column K to indicate the date of funding, you could calculate the value in the Application Status column with the formula (shown here for row 5) :
=IF( ISNUMBER(K5), "Funded", IF(G5<>"", G5, IF(ISNUMBER(E5), "Pending", IF(ISNUMBER(C5), "In Progress", "??"))))Edit: Oops, corrected the row number
Admittedly, there's much that can be done with VBA. But if you do not want to deal with the issues that come from allowing users to edit workbooks that contain VBA code, perhaps you could just capture enough data such that you would not need a dropdown list at all, and can calculate the Application Status...
(Note: I don't know your organization's requirements for status changes, so some of this is made up.) E.g., let's say you use column C to contain the date a grant request was received (making it In Progress), column E to contain the date the request was acknowledged (making it Pending), column F to indicate the date a decision was made and column G to indicate the approval/denial decision, and column K to indicate the date of funding, you could calculate the value in the Application Status column with the formula (shown here for row 5) :
=IF( ISNUMBER(K5), "Funded", IF(G5<>"", G5, IF(ISNUMBER(E5), "Pending", IF(ISNUMBER(C5), "In Progress", "??"))))
Edit: Oops, corrected the row number
