Adding data validation to existing table - not replcating on adding new row

[henryg]

I am having problems adding data validation to an existing transaction table (some 200+ rows), as the custom formula validation does not get replicated when a new line is added. In this case it is to give a warning if the transaction date is not +/-30 days from the current date (=AND(A222>(TODAY()-30),A222<(TODAY()+30)).

I created a new (empty) table with the same data validation and it replicated when a new row was added.

Can anyone suggest what might be going wrong? I do not want to start from scratch, even allowing that I could paste the data across, as it is linked to a number of other sheets and tables, and shared. Something would be bound to go wrong!

 

[Toadstool]

Hi Henryg,

I'm not sure I understand but let me give it a try. A Data Validation Custom formula should use the cell address which is the first row being checked and the Data Validation should be applied to all cells where required, even if they do not yet contain data.

For this example I selected cells B2:C2000, Data Validation, Custom with a Formula of
=AND($A2>(TODAY()-30),$A2<(TODAY()+30))
and for Error Alert I selected Warning.

Column B or C changes for rows 5,6 and 7 are allowed, others give warning if column A has a date.

	A	B	C
1	Transaction Date	Name	Number
2	07-Jul-19	Bert	3
3	17-Jul-19	Fred	6
4	27-Jul-19	Sue	5
5	05-Sep-19	Fred	3
6	15-Sep-19	Sue	1
7	25-Sep-19	Sara	3
8	05-Oct-19	Bert	1
9	15-Oct-19	Fred	1
10
11
12

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

 

[henryg]

Thanks for your help Toadstool! When I tried to enter the validation formula for the whole column I found I already had different data validation set that I had forgotten; Excel allowed me to change the validation in the last cell but subsequent rows then had no validation, presumably because I had confused it.

 

[Toadstool]

You're welcome!