Validating Days of the Week

[jmadux2003]

I'm trying to force all cells for a particular column to only allow any combination of the following characters: M, T, W, R, F, S, U.

So all of the following would be valid:
MTW
RTS
UWM
MTWRF

But the following would not:
M T W R F
WQRF

What kind of custom formula do I need in order to allow this kind of character restriction?

 

[Rick Rothstein]

Give the Validation formula a try...

=ISNUMBER((SUMPRODUCT(0+FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"MTWRFSU"))))

 

[jmadux2003]

Give the Validation formula a try...

=ISNUMBER((SUMPRODUCT(0+FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"MTWRFSU"))))

Couple of questions:
1) How can I change "A1" so that the formula works on every cell? I.e., A1, A2, A3, ..., AN
2) Is there a way to write the formula so the column doesn't have to be explicitly hardcoded? Meaning, instead of "A1", is there a dynamic variable like $CELL that can be used instead.

 

[Rick Rothstein]

Couple of questions:
1) How can I change "A1" so that the formula works on every cell? I.e., A1, A2, A3, ..., AN
2) Is there a way to write the formula so the column doesn't have to be explicitly hardcoded? Meaning, instead of "A1", is there a dynamic variable like $CELL that can be used instead.

Select all of the cells in column A that you want it to apply to starting with cell A1, then use the formula I posted in the Data Validation dialog as a Custom Formula... it will automatically apply to all of the selected cells.