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
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
Does this persist if you quit and restart Excel?
- Okay, that did work. I closed out of everything and then it worked correctly. However, related to the code, when I enter the correct ranges and change the column to Approved and then enter the date in the other, I get this message:
Run-time error '424':
Object Required
It says end or debug, which I did debug last time and that's when everything shut down. But it's not working currently. Any ideas?I'm afraid I don't understand that. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?