Forum Discussion

Hemanth_R's avatar
Hemanth_R
Copper Contributor
Nov 30, 2023

Formula for creating drop down list while using if function

I want to have a drop down if a task is not complete. As in, Column A contains task list, column B should contain reason as drop down and column C should contain completion date. If date is present in C2, B2 should show as complete and if there no date in C3, B3 should have a drop down of list present in cell range K2:K7. Or should I use VBA? Please let know

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Hemanth_R 

    You can achieve this without using VBA by combining the IF function with Data Validation in Excel. Here is a step-by-step guide:

    Assuming your data is in columns A, B, and C, and the drop-down list for incomplete tasks is in the range K2:K7:

    1. Create the Drop-Down List for Incomplete Tasks:

    Select the cell where you want the drop-down list to appear (let's say B2).

    Go to the "Data" tab on the ribbon.

    Click on "Data Validation" in the "Data Tools" group.

    In the Data Validation dialog box:

        1. Allow: Choose "List."
        2. Source: Enter =$K$2:$K$7.
        3. Click "OK."

    2. Use the IF Function for Completion Status:

    In cell B2, enter the following formula:

    =IF(C2<>"Complete", B2, "Complete")

    This formula checks if there is a completion date in C2. If yes, it displays "Complete"; otherwise, it shows the drop-down list from K2:K7.

    Drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to other cells in column B.

    Now, when you select "Complete" from the drop-down list in column B, column C will automatically show the completion date.

    This method allows you to create a dynamic drop-down list based on the completion status without using VBA.

    Note: Ensure that your completion dates in column C are in date format, as Excel might treat empty cells or cells with text differently when using the IF function with dates.

    If you need more dynamic behavior or additional features, VBA might be a suitable option, but the method described above is a simpler and more user-friendly approach for many scenarios.

    The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

  • djclements's avatar
    djclements
    Bronze Contributor

    Hemanth_R VBA is likely the best way to achieve this automatically; however, if you don't want to use VBA, you could at the very least use conditional formatting to highlight cells if a date is entered in column C but the value in column B does not equal "Completed", or if a value in column B equals "Completed" but the date is not yet entered in column C.

     

    The formula to apply Conditional Formatting to column B is:

     

    =AND($B2<>"Completed", $C2<>"")

     

    Conditional Formatting Column B

     

    And the formula to apply Conditional Formatting to column C is:

     

    =AND($B2="Completed", $C2="")

     

    Conditional Formatting Column C

     

    Having said that, if you still want to use VBA to automatically update the value in column B when a date is input in column C, open the Visual Basic Editor (Alt+F11), double-click on the applicable sheet name in the VBA Project pane, then paste the following code in the worksheet module:

     

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Stop macro if more than one cell was changed
        If Target.Cells.Count > 1 Then Exit Sub
    
    'Check if value was changed in Column C
        If Not Intersect(Target, Me.Columns(3)) Is Nothing Then
        ' update the task status in the adjacent cell in Column B
            If Not Target.Value = vbNullString Then
                Target.Offset(, -1).Value = "Completed"
            Else
                Target.Offset(, -1).Value = "In Progress"
            End If
        End If
    
    End Sub

     

    Or, as an alternative approach, to automatically add today's date to column C when the value in column B is changed to "Completed", use the following code:

     

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Stop macro if more than one cell was changed
        If Target.Cells.Count > 1 Then Exit Sub
    
    'Check if value was changed in Column B
        If Not Intersect(Target, Me.Columns(2)) Is Nothing Then
        ' update the completion date in the adjacent cell in Column C
            If Target.Value = "Completed" Then
                Target.Offset(, 1).Value = Date
            Else
                Target.Offset(, 1).ClearContents
            End If
        End If
    
    End Sub

     

    Cheers!

Share

Resources