Hi!

To do EXACTLY what you want will require VBA code. You can get ALMOST the
same thing using conditional formatting to hide the invalid days in B1 and
then a simple formula in C1 that says "Select value" (or whatever you want
it to say).

Here's how:

Make a list of the weekdays somewhere. I'll use H1:H7 as an example:

H1 = Mon
H2 = Tue
...
...
H7 = Sun

Select cell B1.
Goto FormatConditional Formatting.
Condition 1
Formula Is: =AND(A1="Weekday",OR(B1=H6:H7))
Click the format button
Set the font color to be the same as the background color.
OK
Click Add
Condition 2
Formula Is: =AND(A1="Weekend",OR(B1=H1:H5))
Click the format button
Set the font color to be the same as the background color.
OK your way out.

Enter this formula in cell C1:

=IF(AND(A1="Weekday",OR(B1={"sat","sun"})),"< Select Value
",IF(AND(A1="Weekend",OR(B1={"mon","tue","wed","t hur","fri"})),"< Select
Value ",""))

Biff

"Bullman" wrote in message
ps.com...
Hello

I have been successful combining IF statements with Validation rules so
that the value in one cell determines what the Validation list is in
another cell.

eg. If cell A1="Weekday", the validation list for B1 is (Mon, Tue,
Wed, Thr, Fri)
If cell A1="Weekend", the validaion list for B1 is (Sat, Sun)

But changes can be made to cell A1 that make the value in B1 now
invalid.
eg. If I select A1="Weekend", then B1="Sat", if I now change
A1="Weekday", B1 will still stay as an invalid entry "Sat".

I know I can use the Audting toolbar to "Show Invalid Entries" to
circle all invalid entries, but it too is NOT dynamic. eg. It will not
automatically remove the red circle from cells when you go ahead and
choose a valid entry. You need to press the "Circle Invalid Data"
button each time you make a correction to check if the data is now
valid.

In the case where cell A1 is changed making the value in cell B1
invalid (like in the example above), I want cell B1 to
automatically/dynamically change from being an invalid entry to a value
such as "<select value", prompting the user to now select a valid
entry from the pull down list.

Can this be done?

Regards

Bullman