Forum Discussion
Excel multiple data validation
I want to create two lots of data validation in one column based on another column. If i enter 'Calendar' in to column 'E' i want to only be able to enter a date in to the same row in column 'J'. But...
You can use data validation in Excel to restrict data entry based on another column.
You can create two lots of data validation in one column based on another column by using a custom formula.
Here’s how you can do it:
- Select the cells you want to apply data validation to.
- Go to the Data tab on the ribbon and click on Data Validation.
- In the Data Validation dialog box, select Custom from the Allow drop-down list.
- In the Formula box, enter this formula: =IF(E1=“Calendar”,ISDATE(J1),AND(E1=“Hours”,TIMEVALUE(J1)))
- Click OK.
This formula will allow you to enter a date in column J if column E contains “Calendar”
and will allow you to enter a time value in column J if column E contains “Hours”.
I hope this helps!
- Thanks, i think it was the 'ISDATE' and 'TIMEVALUE' bits that i was struggling with.
- Nice to hear that you made progress with your project.
I wish you continued success with Excel.Ignore that no i get a 'named range cannot be found' error. The 'Calendar' and 'Hours' i put are just drop down options in a list. Will this mean that the formula cannot see them? Although i thought that is what the quotation marks were for.
