IF Function with Named Lists in Data Validation

[Guest]

When setting up data validation (list) in the source box I am trying to input
an IF function to accomplish the following: In cell B1 is where I am doing
the data validation. I want to make it so that if the account entered in
cell A1 appears in a list that I have titled "Accounts_With_Subaccounts"
(Accounts_With_Subaccounts contains 15 accounts) then in cell B1 it returns a
dropdown list that I have named "Subaccounts" for the user to select from.
If the account entered in cell A1 does not appear in the
"Accounts_With_Subaccounts" list, I want it to return a dropdown list titled
"No_Subaccounts" in cell B1. I have been trying to work with this and have
tried =IF(A2=Accounts_With_Subaccounts,Subaccounts,No_Subaccounts), but
nothing seems to work. Any suggestions would be appreciated.

 

[T. Valko]

Are the sources for your lists a range of cells?

H1:H10 = named range = sub
I1:I10 = named range = nosub

Data Validation
Allow: List
Source:

=IF(COUNTIF(sub,A1),sub,nosub)

Biff

 

[Guest]

great, thank you

Are the sources for your lists a range of cells?

H1:H10 = named range = sub
I1:I10 = named range = nosub

Data Validation
Allow: List
Source:

=IF(COUNTIF(sub,A1),sub,nosub)

Biff

 

[Guest]

That worked really well. Perhaps you could help me add on to it a bit. Now
I am trying to add some different outcomes to the formula.

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.
Do you know how I can modify my formula to achieve what I want?

 

[T. Valko]

Your formula should work although you're missing some closing ")" at the end
but Excel would have complained about that when you tried to enter the
formula.

An alternative is to enter that formula (modified) into a worksheet cell and
then refer to that cell as the source. Let's assume you enter this formula
in cell A1:

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

Note the quotes around the named ranges.

Then, as the source for your drop down you can use this formula:

=INDIRECT(A1)

Biff