Advanced cell validation help needed pls

[Bullman]

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

 

[Biff]

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 Format>Conditional 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","thur","fri"})),"< Select

Value >",""))

Biff

 

[Ardus Petus]

Validation lists for week/wekkend should be named range.

In order to automatically change B1 to "<select value>" if validation fails,
you must use a VBA event procedure.

See example: http://cjoint.com/?gbjouMhmaj

HTH

 

[Bullman]

Awesome solutions guys!

Biff, I think your solution is probably the best that can be done
without any VBA assistance. I was toying with a similar concept but
your use of a Conditional Format to make the cell appear to be blank
(and hence act as a kind of flag/prompt for the user to then go and
fill it in with data) is a very clever work around I hadn't considered.

Ardus, your use of VBA certainly provides a 100% solution to the
problem. I have had a look at the code for that script that
automatically resets cell B1 to <select vale> if changes to A1 make the
current value in B1 invalid. Unfortunately my lack of VBA code
knowledge prevents me from fully understanding the code used. I wish
to at least obtain a better undertanding of the VBA code used in the
script so I can modify and adapt the one you wrote and apply it to
other cells (with different conditions) on the same worksheet and on
other worksheets.

Can you perhaps suggest a good starting point?

Sincerest thanks!

Bullman

 

[Biff]

Hi!

You can use a similar event procedure on OTHER sheets but you can't have
more than 1 per sheet.

OTOH, you can use similar conditional formatting techniques to your hearts
content!

Biff

 

[Bullman]

Hello Biff,

Ahh, I see. So the VBA solution really is only good for one case
(cell) on the spreadsheet. Bugger. I really need it to be applied to
numerous cells/instances and cases on the same worksheet.

No problems, I will then use your crafty solution instead.

Thanks again

Bullman

 

[Biff]

So the VBA solution really is only good for one case

(cell) on the spreadsheet.

No, it can be used for a range of cells like A1:10. That would be 1 event
procedure that covers that range. But you can't have another event procedure
that does something else on some other range.

Hopefully, Ardus will respond. I'm not too good with VBA but I'm pretty
strong with formulas!

Biff

 

[Ardus Petus]

Here is a fully commented version with multiple cells :
http://cjoint.com/?gcj21u5GWc
You can modify the constant (eg Const sWatch = "A1:A10,E1:E10") to suit your
needs.

HTH

 

[Bullman]

Thank you both again!

Ardus, being able to apply it to the same cell numerous instances like
you have shown is certainly what I asked for, as I thought that there
would be no limitations on having a similar VBA script to do the same
thing but for a different range of cells/conditions on the same sheet.

eg. If I now asked for another cell (or column of cells) (eg. E1) on
the same sheet to have the validation list that is "Autumn, Winter,
Spring, Summer" and next to it a cell (eg F1) whose validation list is
dependant on E1 such that:

if E1=Autumn, validation list is "Mar, Apr, May"
if E1=Winter, validation list is "Jun, Jul, Aug"
if E1=Spring, validation list is "Sep, Oct, Nov"
if E1=Summer, validation list is "Dec, Jan, Feb"

will a similar VBA code work to "self correct" F1 (like you have done
with the days of the week example) if cell E1 is ever changed hence
making F1 invalid? Or with many other cases?

I ultimately am working on a spreadsheet where several field columns
need to be filled out (from left to right) for each row record. The
choices being made in cells to the left of each cell dictate the
options of choices for the cells on the right of that cell. However, I
am trying to work in the "self correcting" smarts so that if ever a
particular column field in a particular row record is changed, any now
invalid entries in that row and to the right of that column revert back
to a <select value> entry rather than remain invalid without appearing
so to a user.

I have been using very advanced versions of Biffs suggestions, where
very complex Conditional Formatting formulas give visual cues to the
user (making the cell appear to be "blank", without actually changing
the value of course) that indicate that the current data entry in that
cell is invalid based on the various/numerous combinations of column
enrties to the left of it, prompting them to re-select a valid entry
from the pull down list. I have got it all working but it has been
tedious.

I will be happy to share my work with you if you are at all interested
in what I have been able to achieve and how I achieved it.

Bullman