Formula in Dropdown menu

[Trikson]

Hi guys, I am not sure whether it is even possible, but where's a better place to ask than on MrExcel?

I have a following spreadsheet:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]input 'W' if it is a work day
input 'R' if it is a rest day[/TD]
[TD][TABLE="width: 300"]
<tbody>[TR]
[TD]Select reason only if necessary[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Column B has a dropdown menu with 6 choices that is applicable only when R is entered into respective cell in column A. I have that part covered. However the requirement is also that if the work ends and rest starts, selecting one particular choice from the dropdown should trigger error message (others should work normally) . What's more important (and troublesome) is that it should only be the case for the first and last rest day.

Below is the example of the situation (column B now shows the choices available for each cell; for the sake of this example, let's assume that it is choice 1 that should not be allowed on first and last day of rest):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]input 'W' if it is a work day
input 'R' if it is a rest day[/TD]
[TD][TABLE="width: 300"]
<tbody>[TR]
[TD]Select reason only if necessary[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]w[/TD]
[TD]no choice[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]w[/TD]
[TD]no choice[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]r[/TD]
[TD]choice 2-6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]r[/TD]
[TD]choice 1-6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]r[/TD]
[TD]choice 1-6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]r[/TD]
[TD]choice 2-6[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]w[/TD]
[TD]no choice[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]w[/TD]
[TD]no choice[/TD]
[/TR]
</tbody>[/TABLE]

Honestly I am stuck without any clue on how to put that part into a formula so any help will be appreciated

Thanks in advance!

 

[IlyaL]

Hi Trikson,

Instead of showing the error message for choice 1 in some cells it's better not to show choice 1.

If I understand right you already use data validation for cells. Try to select range based on conditions. For example in your scenario the formula for data validation in C2 may look like this:

=IF(OR(A1="w";A3="w");$F$3:$F$7;$F$2:$F$7)

assuming that your "choice 1-6" is inside $F$2:$F$7 range and "choice 2-6" is inside $F$3:$F$7 range.
And just copy the cell down to stretch data validation

If you also want data validation to skip rows with "w" make another condition around:

=IF(A2="w";;IF(OR(A1="w";A3="w");$F$3:$F$7;$F$2:$F$7))