data validation non contiguous cells

[hajiali]

Hello I have non contiguous range ($U$2:$AS$1000 and $AW$2:$BF$1000) I need a data validation to alert users when the same date is entered in both range combine more than 12 time.


Thanks

 

[hajiali]

For some reason that looks like its going to work but does not. It also take a long time to calculate the sheet. The first code did work but still took a time to calculate then the MSG appeared. This last code posted does still take a long time to calculate and no message comes up.

 

[DanteAmor]

Can you try it in a new book?

 

[Peter_SSs]

Thanks Peter. I really need this to work for Dates as that is the only data that will be inputted in these ranges.

If dates are the only thing being entered, the Data Validation I provided will work.

I want it just to be a warning and allow user if they want to continue Yes or No, if Yes then apply date if no than end

In that case, in the Data validation dialog, go to the 'Error Alert' tab, change the Style to 'Warning' and (if you want) enter some Error message text like "This exceeds the 12 limit". The user will then get a warning if they enter a date more than 12 times but will have the option of using that date anyway.

 

[hajiali]

here is what happened in the new Workbook. Still takes a long time to Process the calculation. The first time I exceeded the 12 of the same date it gave me the Msg after that i was able to delete the date and retype it in and no message on the 13th time and the 14th time

 

[hajiali]

If dates are the only thing being entered, the Data Validation I provided will work.

In that case, in the Data validation dialog, go to the 'Error Alert' tab, change the Style to 'Warning' and (if you want) enter some Error message text like "This exceeds the 12 limit". The user will then get a warning if they enter a date more than 12 times but will have the option of using that date anyway.

Tell me if im doing this wrong Peter. I select the 2 ranges and click on Data Validation/ allow= Custom / formula =COUNTIF($U$2:$AS$1000,U2)+COUNTIF($AW$2:$BF$1000,U2)<=12 / changes error style to warning.

When i hit save it changes the formula to

=COUNTIF($U$2:$AS$1000,[COLOR=#ff0000]M1048562[/COLOR])+COUNTIF($AW$2:$BF$1000,[COLOR=#ff0000]M1048562[/COLOR])<=12

after i go back every cell give a different large cell

 

[DanteAmor]

here is what happened in the new Workbook. Still takes a long time to Process the calculation. The first time I exceeded the 12 of the same date it gave me the Msg after that i was able to delete the date and retype it in and no message on the 13th time and the 14th time

I don't understand what data you are putting or what you have on the sheet. But in my tests the answer is immediate.
Try my file.

https://www.dropbox.com/s/a7ilswwiiknbd0t/validations.xlsm?dl=0

 

[hajiali]

thanks for your help DanteAmor I was able to use the Data validation formula that Peter_SSs gave and just use the copy paste special use validation paste. it does everything i need with out the VBA which is exactly what i needed

 

[Peter_SSs]

Tell me if im doing this wrong Peter. I select the 2 ranges and click on Data Validation/ allow= Custom / formula =COUNTIF($U$2:$AS$1000,U2)+COUNTIF($AW$2:$BF$1000,U2)<=12

Yes, you have implemented in a different way to what I described. My description was to apply the given DV to U2 then copy to the rest of the range.

If you want to select the whole of both ranges first & apply all the DV at once, that is fine, but when you enter the formula those red U2s I have highlighted above must be the address of the active cell. So if you selected U2:AS1000 held Ctrl & then selected AW2:BF1000 then the actice cell will be AW2 so that would need to go in the DV formula where the red U2s are.

(In fact, what I did when testing was to select AW2:BF1000 then Ctrl and select U2:AS1000, so U2 was my active cell )

Try deleting all the DV from the 2 ranges and implement again.

Edit: Actually, I hadn't seen your previous post so I see you worked out my original implementation suggestion.

 

[DanteAmor]

Glad to know you found the solution to your request.

Just comment if you have 12 cells with the same date and copies one of those cells within the range, you will not see the warning.

 

[Peter_SSs]

Just comment if you have 12 cells with the same date and copies one of those cells within the range, you will not see the warning.

Further to Dant'e comment...

Both DV and macro solutions to your issue can be defeated to some extent.

DV can be defeated, not only by copying from within the region, but copying from outside the region can be worse. If the copied cell(s) originally copied contained no DV, or different DV to your target range, when pasted into your range will over-write the current DV.

Macro solutions are defeated if the user does not enable macros (or the IT department policy prohibits them)

You just need to choose the method that you think is best given who will be using the workbook and in what circumstances.