IF equations in a Dropdown list

[Trevor_O]

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

[#2]

@Trevor_O 

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

[#3]

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).

[#4]

@Trevor_O 

Does this persist if you quit and restart Excel?

[#5]

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?

[#6]

@Trevor_O 

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?

[#7 · Accepted solution]

@Trevor_O 

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

[#8]

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.