Try this. In this example, my list is in N1:N3, and the dependent cell is
H1.

First make sure that H1 has any value except Ongoing.

Then in the new DV cell, select a type of List, uncheck the Ignore blank
checkbox, and add a formula of =IF(H1<"Ongoing",N1:N3,"")

The DV arrow shows even if entry is barred, but some see that as a positive
feature.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Anne Spencer" wrote in message
...
I have set up data validation where I have a main list and a dependant
List.

Whilst I can get the dependant List to work using INDIRECT what I really
need is that if one option is chosen from the main list eg Ongoing, then I
am
able to choose from the Dependant List, but if any other option (other
than
Ongoing) is chosen I do not want to be able to select from the dependant
list
in this column. eg I want that cell to remain blank.

In the next column I want to be able to choose from the dependant list
only
if I select anything BUT Ongoing from the main list and if I choose
Ongoing
from the main list I want the cells in this column to remain blank.

I hope this makes some sense - ???

I have tried incorporating the IF function in my data validation but I do
not seem to be able to get it right.
--
Anne