Data Validation - lists if certain column is certain value

[Guest]

Hi,

I've got a simple register/task allocation spreadsheet and I want Data
Validation on the "task" cell to operate in the following manner:

If "present" cell is "NO" then only allow certain values (Sick, AWOL, etc)
If "present" cell is "YES" then allow any value

Any ideas? I've played around with custom functions to try and achieve this
without any luck :-(

George

 

[Guest]

Insert Name Define

Name: List1
Refersto: =IF(Sheet1!$A$1="NO",Sheet1!$J$1:$J$3,Sheet1!$I$1)

where I1 is truly blank and J1:J3 contains the choices for an abscence.

then in data validation after selecting the list option, in the textbox put

=List1

 

[Guest]

Thanks Tom - I haven't tried this out although I can see the reasoning behind
this and reckon it would work a treat.

What happens if I have multiple tabs with the "present" column on them? In
the List1 definition I can see "Sheet1" - what do I need to change if I want
to be able to apply this list to multiple sheets (the present column is in
the same place on each of these sheets)...

George