Formula for creating drop down list while using if function

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!

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.