how can i exclude a list of date range (public holidays) in data validation?

[kashifziatevta423]

Hi dear, i am new to this forum. please tell me how to disallow user to input specific dates (list created for public holidays)?
waiting for your quick reply.
sheet is attached for reference.
thank you.

 

[JoeMo]

Say the cells you want to apply Data Validation (DV) to are A1:A10. First, name the range containing your Holidays & Sundays "Exclude" using Formula>Define Name. Then select A1:A10 and go to Data>Data Tools> Data Validation. Then select Custom and input this formula (see image below): =$A1<>Exclude and click OK.

 

[kashifziatevta423]

Say the cells you want to apply Data Validation (DV) to are A1:A10. First, name the range containing your Holidays & Sundays "Exclude" using Formula>Define Name. Then select A1:A10 and go to Data>Data Tools> Data Validation. Then select Custom and input this formula (see image below): =$A1<>Exclude and click OK.
View attachment 23160

Thanks a lot for your response dear, but its not working for me. (excel version 2019)
Please help

 

[JoeMo]

Thanks a lot for your response dear, but its not working for me. (excel version 2019)
Please help

You've not defined the named range "Exclude" properly. It not include the first cell with "Exclude" in it.

In the Name Manager select the name Exclude and down at the bottom RefersTo: formula bar change $F$1 to $F$2 and click the check mark to the left, then close the Name Manager dialog box.

If you've entered the correct formula in the DV dialog box, this should be working now. Note it will not react to the "excluded" values you've already placed in the DV range. Delete those and start again.

 

[kashifziatevta423]

Dear Sir, by doing all, only one cell (first cell in DV range) is restricted. Screenshot attached.
if possible please +923017005923 whatsapp/ remote assistance required in this matter. Please help

thank you.

 

[JoeMo]

Sorry, I gave you the wrong DV formula. Try: =AND(A1<>Holidays)

 

[kashifziatevta423]

Dear Sir, its perfectly fine working. Million of thanks.

 

[JoeMo]

Dear Sir, its perfectly fine working. Million of thanks.

You are welcome - thanks for the reply.