Forum Discussion
Formula for creating drop down list while using if function
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.
- 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
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:
- 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."
- 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.
hi,
for example we have two different dtopdown cells.
B8 is two text : marcous , smith
i wana to make automatic dropcell for this:
if B8:marcous then F12 shown 40
if B8: smith then F12 shown 45
how can i make it in excel?
- https://github.com/Azure/iotedge-eflow/issues/141 Is a good starting point for quistions in executing the tasks
