Data validation not being updated when I drag down the cells

Hello,

I have a problem. I am using Excel 365 online and have the following data validation formula in one of the cells:
=AND($N$3>$T$3,$N$3<$T$3+60,$N$3<>TEXT($N$3,"MM/DD/YYYY"))

The problem is that when I drag it down to the next cell/row below, it does not update the values. This makes it to stop working. It should update to:
=AND($N$4>$T$4,$N$4<$T$4+60,$N$4<>TEXT($N$4,"MM/DD/YYYY"))

and if I drag it down one more time, it should update to:
=AND($N$5>$T$5,$N$5<$T$5+60,$N$5<>TEXT($N$5,"MM/DD/YYYY"))
etc.
etc.
etc.

I have over 7000 rows and 7 different sheets so doing it manually is out of the question.

Can anyone help?

Hi. I've attached a file that has solved your issue. Essentially you should just be anchoring the column but not the row so that you can drag down.




No 10 25
No 935 920
Yes 391 345

Let's assume these are numbers in your set. We use the following formula where we see the "Yes" and No" column.

=IF(AND($N3>$T3,$N3<$T3+60,$N3<>TEXT($N3,"MM/DD/YYYY")),"Yes","No")

Thanks but this doesn't work. When I try to edit and enter that version of the formula, I get the following error message on the bottom of the data validation popup: This formula currently evaluates to an error. Do you want to continue?

However, there's no way to continue. I can only "Clear All" and "Cancel"

essentially I need 3 things to be validated in each cell in column N:
1. date format (month, day, year) using m-d-y or mm-dd-yy or m/d/y or mm/dd/yy
2. the date entered in column N cannot be before the date in column T (if column T = June 1, 2023, the date entered in column N cannot be before this date)
3. the date entered cannot be more than 60 days after the date in column T (if column T = June 1, 2023, the date entered in column N cannot be 60 days later than this date)

Data validation will ensure that the date entered meets all 3 of the conditions above
The formula I am using works, but when I drag it down it doesn't copy the cell value from the row below and hence, it stops working

Originally Posted by UnderLeveraged

Hi. I've attached a file that has solved your issue. Essentially you should just be anchoring the column but not the row so that you can drag down.




No 10 25
No 935 920
Yes 391 345

Let's assume these are numbers in your set. We use the following formula where we see the "Yes" and No" column.

=IF(AND($N3>$T3,$N3<$T3+60,$N3<>TEXT($N3,"MM/DD/YYYY")),"Yes","No")

Are you suggesting that the data validation is then dependant on the Yes, No status of another column? If so, this workaround could work. How do I create the data validation though so that if it detects a "No" cell, it will trigger the warning.

For example, we have this:
Column N, ................ Column T ................ Column X (yes.no status)
May 1, 2023.............. May 2, 2023 ................ No (because it's before date in column T, status will be "no". This would prompt the data validation to trigger the warning. This solution could work but how do I set the data validation for column X and how do I set up the 3 rules in column X so that it takes all of these 3 rules into account:
1. date format (month, day, year) using m-d-y or mm-dd-yy or m/d/y or mm/dd/yy
2. the date entered in column N cannot be before the date in column T (if column T = June 1, 2023, the date entered in column N cannot be before this date)
3. the date entered cannot be more than 60 days after the date in column T (if column T = June 1, 2023, the date entered in column N cannot be 60 days later than this date)

Astral - I suggest you send over the file because the most logical answer has been provided twice based on the information provided.

Send over the file, and then I'll walk you through it.

Originally Posted by TMS

Who's that addressing? Me? I have nothing to test any potential solutions … and not enough information to simulate your setup/requirement.

When I try to edit and enter that version of the formula without the $ as you suggested, I get the following error message on the bottom of the data validation popup: This formula currently evaluates to an error. Do you want to continue?

However, there's no way to continue. I can only "Clear All" and "Cancel"