Formula for creating drop down list while using if function

Copper Contributor

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

6 Replies

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

Thank you, but it did not solve the problem. The issue/error is that if i select a content from drop down and then the task is complete, the formula does not run
https://github.com/Azure/iotedge-eflow/issues/141 Is a good starting point for quistions in executing the tasks

@Hemanth_R 

To address the issue where the formula does not run after selecting a content from the drop-down list and then marking the task as complete, you can modify the formula in column B to handle both scenarios. Here is an updated formula:

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:
      • Allow: Choose "List."
      • Source: Enter =$K$2:$K$7.
      • Click "OK."
  2. Use the IF Function for Completion Status:
    • In cell B2, enter the following formula:
    • =IF(C2<>"", "Complete", IFERROR(VLOOKUP(B2, $K$2:$K$7, 1, FALSE), ""))
    • This formula checks if there is a completion date in C2. If yes, it displays "Complete." If not, it uses VLOOKUP to retrieve the value from the drop-down list in column K.
    • Drag the fill handle down to apply the formula to other cells in column B.

Now, when you select a content from the drop-down list and then mark the task as complete, the formula should still work and display "Complete" in column B.

This modification uses VLOOKUP to retrieve the value from the drop-down list even after a selection has been made. If an error occurs (e.g., if a new item is added to the drop-down list), IFERROR ensures that the formula doesn't return an error but instead returns an empty string ("").

Please try this updated formula and let me know if it resolves the issue. If this not help please provide more information about your Excel version, operating system, storage medium, etc.

@NikolinoDE Just a friendly heads-up, both of your recommendations result in circular reference errors, with the formula in cell B2 referencing cell B2. ;)

@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 BConditional Formatting Column B

 

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

 

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

 

Conditional Formatting Column CConditional 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!