Applying data validation to a whole column is not a good practice. It is very unlikely that a manually filled spreadsheet will use over a million rows. If you want to automatically apply data validation to new rows of data, you may want to consider using an Excel Table. Set up formulas, formats and data validation in the first data row. When a new row of data is entered, these formulas, formats and data validation will be applied to the new row. You can start formulas and data validation in the first row of a table, with a little bit of tweaking, if you don't want to have a header row.

If you do need to have the user enter a value in a cell, I think that this Data Validation workaround will work. I don't believe that you can use a Nested IF in Data Validation since the first FALSE it encounters will trigger the Data Validation message, even if the overall IF statement eventually resolves to TRUE. So we need to use an simple IF function in the Data Validation wizard, one that only has a single logical_test.

This workaround will require a Helper Cell, e.g. A1 Let's say the user-entry cell that you want to validate is A9. Put this formula in A1: =IF(AND(A8',A9=A8+1),1, IF(AND(A8=',A7',A9=A7+1),1, IF(AND(A8=',A7=',A9=A6+1),1,0))) This will put a 1 or 0 in A1 if the conditions you asked for are met. Now, select Data Validation.Custom for A9 and use this formula: =A1=1 When the Nested IF in A1 resolves to 1, the Data Validation will be TRUE and the value will be allowed. Let us know if that works for you.

Nov 6, 2018 - Excel drop down lists pull data from a different worksheet. 2013, and 2010; Excel for Mac 2019, Excel for Mac 2016, and Excel for Mac 2011. The Excel drop-down list is created through the Data Validation dialog box. About Us Advertise Privacy Policy Cookie Policy Careers Editorial Guidelines.