Custom Data Validation or Conditional Formatting

[sangeeta25]

I have a drop down list for a whole column by which the options that you can select from are "yes" "tbd" or "n/a" however i would like a custom rule by which you can only select the option "n/a" ONLY if a previous column says "abandoned" Also please note i would like to keep the drop down list, hence why i am slightly confused on how to keep a drop down LIST if i need to use a custom data validation source but then again to keep the drop down list may be a vba code is needed? not sure.... can anyone assist?

 

[jorismoerings]

Hi,

Sounds like you're looking at dependent data validation.
Try the explanation at http://www.contextures.com/xlDataVal02.html

 

[sangeeta25]

Great, thank you!

 

[jorismoerings]

Hi,

If this was not what you're looking for let me know because there's also the opportunity of creating a fixed dropdown with an error check on input of previous fields.

 

[sangeeta25]

Yes what you have just said sounds more like what i am looking for.. can you help?

 

[konew1]

If you make a named range 'shortlist' with "yes" and "TBD" and range 'longlist' overlapping the short list with N/a on the next line down.
Then in data validation use =IF(I10="abandoned",longlist,shortlist)

Excel Workbook
	G	H	I	J
8	yes	*	*	*
9	tbd	*	*	*
10	n/a	*	abandoned	tbd
11	*	*	other	tbd
Sheet5

#VALUE!

Shortlist is G8:G9 and longlist is G8:G10. If the prior entry is "abandoned" then all three options are valid.

 

[jorismoerings]

Hi Konew,

This solution will limit the possibilities in the list based on the result of the IF statement and it was my understanding that's not the expected result.

From my understanding the expected result is an fixed list with 3 choices where only the appropriate choice out of the list will be accepted based on the previous cell input.
I was thinking to solve it using data validation combined with error checking using VBA.

Maybe you have a better suggestion.

 

[sangeeta25]

Hi, yes your correct i need a solution which combines most probably a vba code with data validation as the data validation has a fixed list thus error checking in the form of a vba code is needed, i think... any suggestions ?

 

[konew1]

Hi Konew,

This solution will limit the possibilities in the list based on the result of the IF statement and it was my understanding that's not the expected result.

From my understanding the expected result is an fixed list with 3 choices where only the appropriate choice out of the list will be accepted based on the previous cell input.
I was thinking to solve it using data validation combined with error checking using VBA.

Maybe you have a better suggestion.

I read it to be that if the prior data us "abandoned" then any of the 3 options ("yes","TBD", and "N/A") were permitted.
If the requirement is that when the prior data is "abandoned" then the only permitted entry is "N/a" then all I would do us redefine my longlist range so it only refers to a single cell containing "N/A", or modify the formula to =IF(I11="abandoned",$G$10,shortlist)