Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation and named lists | Excel Worksheet Functions | |||
Data Validation - Using 2 Lists | Excel Discussion (Misc queries) | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Data validation named range update | Excel Discussion (Misc queries) | |||
Excel2000: Custom data validation and named ranges | Excel Discussion (Misc queries) |