Formula for creating drop down list while using if function

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.