IF Function with Named Lists in Data Validation
 

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_Su baccounts), but
nothing seems to work. Any suggestions would be appreciated.

IF Function with Named Lists in Data Validation
 

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

"ads1983" wrote in message
...
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_Su baccounts), but
nothing seems to work. Any suggestions would be appreciated.

IF Function with Named Lists in Data Validation
 

great, thank you

"T. Valko" wrote:

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

"ads1983" wrote in message
...
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_Su baccounts), but
nothing seems to work. Any suggestions would be appreciated.

IF Function with Named Lists in Data Validation
 

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(BSAcct s,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" wrote:

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

"ads1983" wrote in message
...
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_Su baccounts), but
nothing seems to work. Any suggestions would be appreciated.

IF Function with Named Lists in Data Validation
 

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(COUNTI F(NoDetailAccts,C16),"NoDetailList",IF(COUNTIF(BSA ccts,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

"ads1983" wrote in message
...
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(BSAcct s,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" wrote:

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

"ads1983" wrote in message
...
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_Su baccounts), but
nothing seems to work. Any suggestions would be appreciated.