Forum Discussion
Solved
IF equations in a Dropdown list
Hey everyone,
I'm running into a feature I would like to implement but I'm not sure how to go about setting this up, or if it's even possible. My organization has a grant document that we created in Excel. We created an Application Status column in our table that has a dropdown menu to select the status of the grant. For example, currently you can select from the options In Progress, Pending, Approved, Denied (all have specific color coding in the conditional formatting).
What I am envisioning is when we get a grant approved, we can select the approved option. Then I would like to have another option that is "Funded" to let us know once we have actually received the payment. However, instead of going in and selecting Funding once that happens, there is another column that we fill in with the date when funds were received. My thought process would be that if the Date Funding Received column is completed and has a value/date in it, then the status of the grant column will automatically switch from "Approved" to "Funded". Would it be possible to do this?
Sincerely,
Trevor
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
- Hey Snowman, now thinking about it I think this method might work a bit better. Thank you, I was able to get this setup with our current layout.
This would require VBA, so users would have to allow macros when they open the workbook.
Right-click the sheet tab and select 'View Code' from the context menu.
Copy the following code into the worksheet module.
Switch back to Excel and save the workbook as a macro-enabled workbook(*.xlsm).
Let's say Application Status can be entered in F2:F1000, and Date Funding Received in L2:L1000.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Not Intersect(Range("L2:L1000"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each rng In Intersect(Range("L2:L1000"), Target) If rng.Value <> "" Then Range("F" & rng.Row).Value = "Funded" End If Next rng Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub- So I tried this and there was some type of bug that wouldn't allow it to work, but now all my Excel documents are essentially grayed out (I can't see data).
Does this persist if you quit and restart Excel?
