|
If statements with validation lists
in two columns ay A & B.
In A2 there will be a number from 0-3. I want to show in B2 a dropdown list but only if the value in A2 =0, all others should show n/a. How can i make this work? thanks Tony |
Tony,
You need to make at least 2 named ranges: List0 and List1 List0 should be a single cell, with the value n/a List1 should be as many cells as you want for your dropdown list: enter the allowed values in that range. You can also have List2 and List3 if the numbers in column A are use in determining which list to use. Select cell B2, the choose Data / Validation. Select the Settings Tab, and select List in the "Allow" box, and in the Source box, enter the formula =IF(A2=0,List0,List1) and make sure that you check the "in-cell dropdown" box. If the numbers matter, then use a formula like in the Source box: =IF(A2=0,List0,IF(A1=1,List1, IF(A1=2,List2,List3))) HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... in two columns ay A & B. In A2 there will be a number from 0-3. I want to show in B2 a dropdown list but only if the value in A2 =0, all others should show n/a. How can i make this work? thanks Tony |
Tony,
Of course, I read your problem backwards <vbg Use this formula for data validation on cell B2: =IF(A2=0,List1,List0) and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error alert" on the error tab. Then in Cell B2, enter the formula =IF(A2<0,NA(),"") and copy cell A2 down as far as you need. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... in two columns ay A & B. In A2 there will be a number from 0-3. I want to show in B2 a dropdown list but only if the value in A2 =0, all others should show n/a. How can i make this work? thanks Tony |
Thanks can see what you mean but don't understand how you can have 2 formula
in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read your notes wrong but would appreciate your clarification. Thanks Tony "Bernie Deitrick" wrote: Tony, Of course, I read your problem backwards <vbg Use this formula for data validation on cell B2: =IF(A2=0,List1,List0) and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error alert" on the error tab. Then in Cell B2, enter the formula =IF(A2<0,NA(),"") and copy cell A2 down as far as you need. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... in two columns ay A & B. In A2 there will be a number from 0-3. I want to show in B2 a dropdown list but only if the value in A2 =0, all others should show n/a. How can i make this work? thanks Tony |
=IF(A2<0,NA(),"")
goes into the cell, while =IF(A2=0,List1,List0) is used as the data validation source. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... Thanks can see what you mean but don't understand how you can have 2 formula in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read your notes wrong but would appreciate your clarification. Thanks Tony "Bernie Deitrick" wrote: Tony, Of course, I read your problem backwards <vbg Use this formula for data validation on cell B2: =IF(A2=0,List1,List0) and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error alert" on the error tab. Then in Cell B2, enter the formula =IF(A2<0,NA(),"") and copy cell A2 down as far as you need. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... in two columns ay A & B. In A2 there will be a number from 0-3. I want to show in B2 a dropdown list but only if the value in A2 =0, all others should show n/a. How can i make this work? thanks Tony |
brilliant, thanks, just so i understand could you explain what the first
formula means after a2<2, Thanks again Tony "Bernie Deitrick" wrote: =IF(A2<0,NA(),"") goes into the cell, while =IF(A2=0,List1,List0) is used as the data validation source. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... Thanks can see what you mean but don't understand how you can have 2 formula in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read your notes wrong but would appreciate your clarification. Thanks Tony "Bernie Deitrick" wrote: Tony, Of course, I read your problem backwards <vbg Use this formula for data validation on cell B2: =IF(A2=0,List1,List0) and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error alert" on the error tab. Then in Cell B2, enter the formula =IF(A2<0,NA(),"") and copy cell A2 down as far as you need. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... in two columns ay A & B. In A2 there will be a number from 0-3. I want to show in B2 a dropdown list but only if the value in A2 =0, all others should show n/a. How can i make this work? thanks Tony |
Tony,
The first formula is simply meant to help you fill in the cells in column B with N/A's when the corresponding values in column A are not 0. So, if A2 is 1, 2 or 3, B2 should have NA. Otherwise, it is left blank to appear blank, and will have a pull down list that uses List1. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... brilliant, thanks, just so i understand could you explain what the first formula means after a2<2, Thanks again Tony "Bernie Deitrick" wrote: =IF(A2<0,NA(),"") goes into the cell, while =IF(A2=0,List1,List0) is used as the data validation source. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... Thanks can see what you mean but don't understand how you can have 2 formula in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read your notes wrong but would appreciate your clarification. Thanks Tony "Bernie Deitrick" wrote: Tony, Of course, I read your problem backwards <vbg Use this formula for data validation on cell B2: =IF(A2=0,List1,List0) and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error alert" on the error tab. Then in Cell B2, enter the formula =IF(A2<0,NA(),"") and copy cell A2 down as far as you need. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... in two columns ay A & B. In A2 there will be a number from 0-3. I want to show in B2 a dropdown list but only if the value in A2 =0, all others should show n/a. How can i make this work? thanks Tony |
Thanks again
Tony "Bernie Deitrick" wrote: Tony, The first formula is simply meant to help you fill in the cells in column B with N/A's when the corresponding values in column A are not 0. So, if A2 is 1, 2 or 3, B2 should have NA. Otherwise, it is left blank to appear blank, and will have a pull down list that uses List1. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... brilliant, thanks, just so i understand could you explain what the first formula means after a2<2, Thanks again Tony "Bernie Deitrick" wrote: =IF(A2<0,NA(),"") goes into the cell, while =IF(A2=0,List1,List0) is used as the data validation source. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... Thanks can see what you mean but don't understand how you can have 2 formula in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read your notes wrong but would appreciate your clarification. Thanks Tony "Bernie Deitrick" wrote: Tony, Of course, I read your problem backwards <vbg Use this formula for data validation on cell B2: =IF(A2=0,List1,List0) and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error alert" on the error tab. Then in Cell B2, enter the formula =IF(A2<0,NA(),"") and copy cell A2 down as far as you need. HTH, Bernie MS Excel MVP "Tony Houston" wrote in message ... in two columns ay A & B. In A2 there will be a number from 0-3. I want to show in B2 a dropdown list but only if the value in A2 =0, all others should show n/a. How can i make this work? thanks Tony |
| All times are GMT +1. The time now is 09:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com