Formula to clear Data Validation in a cell

[Nandakumar]

Hi All,

Request your kind support on the below.

I need a formula to make the cells blank (where tasks entered as "Completed" & "Pending"),when i select "Clear All" in L2.

Please advise.

 

[vletm]

Nandakumar
Where is even one 'Data Validation'?
Which 'the cells' should make blank with formula?
How do You select 'Clear All' in L2? .. by selecting that cell or what do You mean?

 

[bosco_yip]

This can be done by using Conditional Formatting.

1] Create a validation list in L2, with source : "Clear All" and "Show All"

2] Select D4:I15 >> "Conditional Formatting" >> "New rule" >> Rule box enter formula :

=((D4="completed")+(D4="Pending"))*($L$2="Clear All")

>> then, click "Format" >> choose "Font" color to white color >> OK >> Finish

 

[Nandakumar]

Thanks for formula.

Let me explain purpose of this.

This is monthly task list.Every month end we have to update tasks whether completed or still pending and to make sure all are compleled before monthy closes.From next month onwards,it should be cleared and blank when i select "Clear All" instead of manually deleting pending,completed.

THis is the main idea of ths sheet.
Hope it clarifies

This can be done by using Conditional Formatting.

1] Create a validation list in L2, with source : "Clear All" and "Show All"

2] Select D4:I15 >> "Conditional Formatting" >> "New rule" >> Rule box enter formula :

=((D4="completed")+(D4="Pending"))*($L$2="Clear All")

>> then, click "Format" >> choose "Font" color to white color >> OK >> Finish

View attachment 68739

 

[bosco_yip]

Thanks for formula.

Let me explain purpose of this.

This is monthly task list.Every month end we have to update tasks whether completed or still pending and to make sure all are compleled before monthy closes.From next month onwards,it should be cleared and blank when i select "Clear All" instead of manually deleting pending,completed.

THis is the main idea of ths sheet.
Hope it clarifies

If you wish the cells actual cleared and blank, that required VBA to do the job.

This post will transfer to "VBA forum"

Rgards

 

[Nandakumar]

OK.

If you wish the cells actual cleared and blank, that required VBA to do the job.

This post will transfer to "VBA forum"

Rgards