| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delimited Lists for Data Validation
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. Does anyone know how I can modify my formula to achieve what I want? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
think all you have to do is add double Quotes around the whole statement
"=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
With the quotes around the equation I am only getting
"=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
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))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
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? "Joel" wrote: 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))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
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 -- Regards, Peo Sjoblom "ads1983" wrote in message ... 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? "Joel" wrote: 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))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
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. "ads1983" wrote: 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? "Joel" wrote: 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))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
In excel, FALSE is defined as 0. All other values will return TRUE.
"Peo Sjoblom" wrote: 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 -- Regards, Peo Sjoblom "ads1983" wrote in message ... 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? "Joel" wrote: 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))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
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(A21,1,0) when one can write =A21 -- Regards, Peo Sjoblom "Joel" wrote in message ... In excel, FALSE is defined as 0. All other values will return TRUE. "Peo Sjoblom" wrote: 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 -- Regards, Peo Sjoblom "ads1983" wrote in message ... 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? "Joel" wrote: 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))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
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))) ads1983 wrote: 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? "Joel" wrote: You need to have three closing parethesis at end of formula. sorry about previous response. IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COU NTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BS Accts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(CO UNTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(B SAccts,C16),BSList)" I am inputing the following formula in the source box for data validation: =IF(COUNTIF(DetailAccts,C16),DetailList,IF(CO UNTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(B SAccts,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? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
Sorry Joel, I assumes the OP wanted a dependant list, you are correct about
using IF function in this example, apologies -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... 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(A21,1,0) when one can write =A21 -- Regards, Peo Sjoblom "Joel" wrote in message ... In excel, FALSE is defined as 0. All other values will return TRUE. "Peo Sjoblom" wrote: 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 -- Regards, Peo Sjoblom "ads1983" wrote in message ... 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? "Joel" wrote: 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))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList)" 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. Does anyone know how I can modify my formula to achieve what I want? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delimited Lists for Data Validation
It works!!!! Thank you everyone for your help
"Debra Dalgleish" wrote: 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))) ads1983 wrote: 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? "Joel" wrote: You need to have three closing parethesis at end of formula. sorry about previous response. IF(COUNTIF(DetailAccts,C16),DetailList,IF(COUNTI F(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BSAcc ts,C16),BSList))) "ads1983" wrote: With the quotes around the equation I am only getting "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(COU NTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(BS Accts,C16),BSList)" in my dropdown list "Joel" wrote: think all you have to do is add double Quotes around the whole statement "=IF(COUNTIF(DetailAccts,C16),DetailList,IF(CO UNTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(B SAccts,C16),BSList)" I am inputing the following formula in the source box for data validation: =IF(COUNTIF(DetailAccts,C16),DetailList,IF(CO UNTIF(NoDetailAccts,C16),NoDetailList,IF(COUNTIF(B SAccts,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? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation - Using 2 Lists | Excel Discussion (Misc queries) | |||
| Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
| Data Validation Lists | Excel Worksheet Functions | |||
| Data Validation Lists | Excel Discussion (Misc queries) | |||
| Data Validation Lists | Links and Linking in Excel | |||
Linear Mode
