Delimited Lists for Data Validation

[Guest]

I am inputing the following formula in the source box for data validation:
=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAccts,C16),BSList).
I am inputing this formula in cell D16 and what I am basically trying to
make happen is if the account entered in cell C16 is located in a list I have
call "DetailAccts", then cell D16 is populated with a dropdown with a list I
have titled "DetailList". Or if the account entered in cell C16 is found in
a list I have named "NoDetailAccts" you get a dropdown list of
"NoDetailList". Or if the C16 account entered is found in "BSAccts" you get
a dropdown list named "BSList". I have tried this formula and I get the
error message saying I must use a delimited list using a single reference.
Does anyone know how I can modify my formula to achieve what I want?

 

[Guest]

think all you have to do is add double Quotes around the whole statement

"=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAccts,C16),BSList)"

 

[Guest]

With the quotes around the equation I am only getting
"=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAccts,C16),BSList)" in my dropdown list

 

[Guest]

You need to have three closing parethesis at end of formula. sorry about
previous response.

IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAccts,C16),BSList)))

 

[Guest]

That seems like it would be the correct formula, however, it gives me an
error message saying "The list source must be a delimited list, or reference
to single row or column". Are you not able to input different IF criteria in
data validation?

 

[Peo Sjoblom]

Check this page for everything that has to do with validation, you cannot
have an IF function that select between different lists. Validation works on
Boolean values (TRUE or FALSE)

http://www.contextures.com/xlDataVal01.html

scroll down to the end to see other validation options, what you should
probably do is to create dependant lists

http://www.contextures.com/xlDataVal02.html

 

[Guest]

I put the formula in a normal cell (with = in front) to get the typos out of
the functtionion. Then I went to Data Validattion and selected custom.
Pasted the formula into the box. I'm using XP 2003. I also havve the addin
analysis toolpack.

 

[Guest]

In excel, FALSE is defined as 0. All other values will return TRUE.

 

[Peo Sjoblom]

I know that, regardless you cannot get multiple validation lists using an IF
function as a custom validation under allow, you would need to use INDIRECT
(see link I provided) besides why write

=IF(A2>1,1,0)

when one can write

=A2>1

 

[Debra Dalgleish]

Yes, you can use IF criteria, but the lists that you're using must be
valid for use with data validation. For example, you can't use a list
that is in separate ranges in the worksheet, such as a list defined as:
=Sheet1!$A$1:$A$5,Sheet1!$C$1:$C$5
Also, make sure the formula starts with an equal sign, and your formula
needs 3 closing brackets, e.g.:

=IF(COUNTIF(DetailAccts,C16),DetailList,
IF(COUNTIF(NoDetailAccts,C16),NoDetailList,
IF(COUNTIF(BSAccts,C16),BSList)))

 

[Peo Sjoblom]

Sorry Joel, I assumes the OP wanted a dependant list, you are correct about
using IF function in this example, apologies

 

[Guest]

It works!!!! Thank you everyone for your help

Yes, you can use IF criteria, but the lists that you're using must be
valid for use with data validation. For example, you can't use a list
that is in separate ranges in the worksheet, such as a list defined as:
=Sheet1!$A$1:$A$5,Sheet1!$C$1:$C$5
Also, make sure the formula starts with an equal sign, and your formula
needs 3 closing brackets, e.g.:

=IF(COUNTIF(DetailAccts,C16),DetailList,
IF(COUNTIF(NoDetailAccts,C16),NoDetailList,
IF(COUNTIF(BSAccts,C16),BSList)))