Forum Discussion
Data Validation multiple
I have sheet that has a drop-down to select "Annual" or "Semi-annual". The following row contains a data validation drop-down to select the month for "Annual", and the row below that contains the same.
| Frequency | Semi-annual | |
| Month Assessed | December | |
| 2nd Month Assessed | June |
I want to not only conceal the 2nd Month assessed, which I have done with conditional formatting, but I want to prevent entry into the cell with the month IF "Annual" is selected. The worksheet is protected. Is there a way to do this without VBA? Is there a way to use both a Validation list and a custome Validation as well?
3 Replies
Mariegb Assuming the cell with the drop-down is in C1, try the following DV rule in C3
=C1="Semi-annual"It should only allow entry of data in C3 if the value in C1 equals "Semi-annual".
Riny_van_Eekelen Thanks for the suggestion, but the problem is that the cell contains a drop down DV list. It will let me do one or the other, but not both.
Mariegb Then you can perhaps use a solution like in the attached file. The DV rule for the 2nd month refers to a list that starts in E2 (=$E$2#). In turn, E2 contains a formula that creates an empty list if C1 contains "Semi-annual" or a full list of months if C1 contains "Semi.annual".
Alternatively, if the 2nd month is always 6 months after the first in a semi-annual cycle, you could consider to use a calculation that adds 6 months to the first month if the frequency equals "Semi-annual". Just an idea. Have given an example of that as well in the attached workbook.
