Forum Discussion
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
- NikolinoDEGold Contributor
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:
- 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", 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.
- Hemanth_RCopper ContributorThank 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
- InViCtUs360Copper Contributorhttps://github.com/Azure/iotedge-eflow/issues/141 Is a good starting point for quistions in executing the tasks
- djclementsBronze 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<>"")
And the formula to apply Conditional Formatting to column C is:
=AND($B2="Completed", $C2="")
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!