IF/THEN drop down list












1














After an extensive search, all I seem to find are cascading drop down list topics. My situation is:



If M173=true then allow drop down list in cell K72, otherwise blank.



For reference, M173 is true or false dependent on whether a check box is checked or not elsewhere on the sheet.



I've tried various if statements and vlookups inside cell K72 and even indirect after opening up the data validation tab.



Is this possible without VBA?










share|improve this question
























  • Can you change True to yes for example, in that case it is possible without VBA
    – yass
    Mar 25 '17 at 13:03










  • I don't see an option to change the value of the checked box to yes or no. When I right click on the box>format control>control tab, my value options are unchecked, checked, mixed. Then the cell link. I guess I could tell another cell if =if(m173=true,"yes","no") but please explain how this would help the total process.
    – Edward
    Mar 25 '17 at 14:35










  • you can define name your drop down list yes and use indirect(cell reference(yes)) in the list of the dropdown
    – yass
    Mar 25 '17 at 14:56
















1














After an extensive search, all I seem to find are cascading drop down list topics. My situation is:



If M173=true then allow drop down list in cell K72, otherwise blank.



For reference, M173 is true or false dependent on whether a check box is checked or not elsewhere on the sheet.



I've tried various if statements and vlookups inside cell K72 and even indirect after opening up the data validation tab.



Is this possible without VBA?










share|improve this question
























  • Can you change True to yes for example, in that case it is possible without VBA
    – yass
    Mar 25 '17 at 13:03










  • I don't see an option to change the value of the checked box to yes or no. When I right click on the box>format control>control tab, my value options are unchecked, checked, mixed. Then the cell link. I guess I could tell another cell if =if(m173=true,"yes","no") but please explain how this would help the total process.
    – Edward
    Mar 25 '17 at 14:35










  • you can define name your drop down list yes and use indirect(cell reference(yes)) in the list of the dropdown
    – yass
    Mar 25 '17 at 14:56














1












1








1







After an extensive search, all I seem to find are cascading drop down list topics. My situation is:



If M173=true then allow drop down list in cell K72, otherwise blank.



For reference, M173 is true or false dependent on whether a check box is checked or not elsewhere on the sheet.



I've tried various if statements and vlookups inside cell K72 and even indirect after opening up the data validation tab.



Is this possible without VBA?










share|improve this question















After an extensive search, all I seem to find are cascading drop down list topics. My situation is:



If M173=true then allow drop down list in cell K72, otherwise blank.



For reference, M173 is true or false dependent on whether a check box is checked or not elsewhere on the sheet.



I've tried various if statements and vlookups inside cell K72 and even indirect after opening up the data validation tab.



Is this possible without VBA?







microsoft-excel data-validation






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Apr 8 '17 at 10:27









DavidPostill

103k25224258




103k25224258










asked Mar 25 '17 at 12:03









Edward

613




613












  • Can you change True to yes for example, in that case it is possible without VBA
    – yass
    Mar 25 '17 at 13:03










  • I don't see an option to change the value of the checked box to yes or no. When I right click on the box>format control>control tab, my value options are unchecked, checked, mixed. Then the cell link. I guess I could tell another cell if =if(m173=true,"yes","no") but please explain how this would help the total process.
    – Edward
    Mar 25 '17 at 14:35










  • you can define name your drop down list yes and use indirect(cell reference(yes)) in the list of the dropdown
    – yass
    Mar 25 '17 at 14:56


















  • Can you change True to yes for example, in that case it is possible without VBA
    – yass
    Mar 25 '17 at 13:03










  • I don't see an option to change the value of the checked box to yes or no. When I right click on the box>format control>control tab, my value options are unchecked, checked, mixed. Then the cell link. I guess I could tell another cell if =if(m173=true,"yes","no") but please explain how this would help the total process.
    – Edward
    Mar 25 '17 at 14:35










  • you can define name your drop down list yes and use indirect(cell reference(yes)) in the list of the dropdown
    – yass
    Mar 25 '17 at 14:56
















Can you change True to yes for example, in that case it is possible without VBA
– yass
Mar 25 '17 at 13:03




Can you change True to yes for example, in that case it is possible without VBA
– yass
Mar 25 '17 at 13:03












I don't see an option to change the value of the checked box to yes or no. When I right click on the box>format control>control tab, my value options are unchecked, checked, mixed. Then the cell link. I guess I could tell another cell if =if(m173=true,"yes","no") but please explain how this would help the total process.
– Edward
Mar 25 '17 at 14:35




I don't see an option to change the value of the checked box to yes or no. When I right click on the box>format control>control tab, my value options are unchecked, checked, mixed. Then the cell link. I guess I could tell another cell if =if(m173=true,"yes","no") but please explain how this would help the total process.
– Edward
Mar 25 '17 at 14:35












you can define name your drop down list yes and use indirect(cell reference(yes)) in the list of the dropdown
– yass
Mar 25 '17 at 14:56




you can define name your drop down list yes and use indirect(cell reference(yes)) in the list of the dropdown
– yass
Mar 25 '17 at 14:56










1 Answer
1






active

oldest

votes


















0














Like you said you can use another cell M1 for example :
=If(M173=True,"Fine","No")

I used Fine just in case you need Yes in another place.

Select the options of your Dropdown List and define Name Fine

In K72 create Data Validation, choose List

In Source write:
=Indirect(M1)

In that case when M1 is different from Fine the dropdown is empty, when you have Fine in M1 K72 will show the values that you want to choose from the list



Update for Drop Down List become blank

Define name a blank cell No make it in a special place so you don't use it for any value (near the Fine list or yes list)

Keep the data validation Source like before =Indirect(M1)

If M1 = "No" the List will become blank (you have to choose the blank to clear the cell if it has any value)

If M1="Yes" or "Fine" whatever you choose the List will have the options you need

Just use normal word for Define Name do not use TRUE, False...



To Clear it automatically you will need VBA






share|improve this answer























  • Yass, thank you for getting me this far. It's definitely in the right direction. I'm trying to add one more component though. After your above suggestion, if M1=no, how do I make the drop down list appear blank? I have been trying the following formula inside the data validation window in the "source" section: =IF(INDIRECT(M1)="no","",INDIRECT(M1))
    – Edward
    Mar 25 '17 at 17:06












  • You want the drop down list to be blank or the cell of the dropdown validation automatically become blank?
    – yass
    Mar 25 '17 at 17:49










  • I will update for drop down list appear blank (not automatically the cell become blank)
    – yass
    Mar 25 '17 at 18:03










  • just the cell of the drop down validation (2nd option). in other words, the drop down list to appear only when M1=fine, otherwise blank. Right now, when M1=no, K72 still shows a value. You can't do anything, can't drop down the list, but there's a value in K72.
    – Edward
    Mar 25 '17 at 18:04










  • @Edward,, If you want the Drop Down blank when Cell M173 is False then do this. Select any blank cell & Name it NO. Then whenever M173 will be False Excel will replace DD value with Blank.
    – Rajesh S
    Sep 17 '18 at 6:45













Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1192265%2fif-then-drop-down-list%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














Like you said you can use another cell M1 for example :
=If(M173=True,"Fine","No")

I used Fine just in case you need Yes in another place.

Select the options of your Dropdown List and define Name Fine

In K72 create Data Validation, choose List

In Source write:
=Indirect(M1)

In that case when M1 is different from Fine the dropdown is empty, when you have Fine in M1 K72 will show the values that you want to choose from the list



Update for Drop Down List become blank

Define name a blank cell No make it in a special place so you don't use it for any value (near the Fine list or yes list)

Keep the data validation Source like before =Indirect(M1)

If M1 = "No" the List will become blank (you have to choose the blank to clear the cell if it has any value)

If M1="Yes" or "Fine" whatever you choose the List will have the options you need

Just use normal word for Define Name do not use TRUE, False...



To Clear it automatically you will need VBA






share|improve this answer























  • Yass, thank you for getting me this far. It's definitely in the right direction. I'm trying to add one more component though. After your above suggestion, if M1=no, how do I make the drop down list appear blank? I have been trying the following formula inside the data validation window in the "source" section: =IF(INDIRECT(M1)="no","",INDIRECT(M1))
    – Edward
    Mar 25 '17 at 17:06












  • You want the drop down list to be blank or the cell of the dropdown validation automatically become blank?
    – yass
    Mar 25 '17 at 17:49










  • I will update for drop down list appear blank (not automatically the cell become blank)
    – yass
    Mar 25 '17 at 18:03










  • just the cell of the drop down validation (2nd option). in other words, the drop down list to appear only when M1=fine, otherwise blank. Right now, when M1=no, K72 still shows a value. You can't do anything, can't drop down the list, but there's a value in K72.
    – Edward
    Mar 25 '17 at 18:04










  • @Edward,, If you want the Drop Down blank when Cell M173 is False then do this. Select any blank cell & Name it NO. Then whenever M173 will be False Excel will replace DD value with Blank.
    – Rajesh S
    Sep 17 '18 at 6:45


















0














Like you said you can use another cell M1 for example :
=If(M173=True,"Fine","No")

I used Fine just in case you need Yes in another place.

Select the options of your Dropdown List and define Name Fine

In K72 create Data Validation, choose List

In Source write:
=Indirect(M1)

In that case when M1 is different from Fine the dropdown is empty, when you have Fine in M1 K72 will show the values that you want to choose from the list



Update for Drop Down List become blank

Define name a blank cell No make it in a special place so you don't use it for any value (near the Fine list or yes list)

Keep the data validation Source like before =Indirect(M1)

If M1 = "No" the List will become blank (you have to choose the blank to clear the cell if it has any value)

If M1="Yes" or "Fine" whatever you choose the List will have the options you need

Just use normal word for Define Name do not use TRUE, False...



To Clear it automatically you will need VBA






share|improve this answer























  • Yass, thank you for getting me this far. It's definitely in the right direction. I'm trying to add one more component though. After your above suggestion, if M1=no, how do I make the drop down list appear blank? I have been trying the following formula inside the data validation window in the "source" section: =IF(INDIRECT(M1)="no","",INDIRECT(M1))
    – Edward
    Mar 25 '17 at 17:06












  • You want the drop down list to be blank or the cell of the dropdown validation automatically become blank?
    – yass
    Mar 25 '17 at 17:49










  • I will update for drop down list appear blank (not automatically the cell become blank)
    – yass
    Mar 25 '17 at 18:03










  • just the cell of the drop down validation (2nd option). in other words, the drop down list to appear only when M1=fine, otherwise blank. Right now, when M1=no, K72 still shows a value. You can't do anything, can't drop down the list, but there's a value in K72.
    – Edward
    Mar 25 '17 at 18:04










  • @Edward,, If you want the Drop Down blank when Cell M173 is False then do this. Select any blank cell & Name it NO. Then whenever M173 will be False Excel will replace DD value with Blank.
    – Rajesh S
    Sep 17 '18 at 6:45
















0












0








0






Like you said you can use another cell M1 for example :
=If(M173=True,"Fine","No")

I used Fine just in case you need Yes in another place.

Select the options of your Dropdown List and define Name Fine

In K72 create Data Validation, choose List

In Source write:
=Indirect(M1)

In that case when M1 is different from Fine the dropdown is empty, when you have Fine in M1 K72 will show the values that you want to choose from the list



Update for Drop Down List become blank

Define name a blank cell No make it in a special place so you don't use it for any value (near the Fine list or yes list)

Keep the data validation Source like before =Indirect(M1)

If M1 = "No" the List will become blank (you have to choose the blank to clear the cell if it has any value)

If M1="Yes" or "Fine" whatever you choose the List will have the options you need

Just use normal word for Define Name do not use TRUE, False...



To Clear it automatically you will need VBA






share|improve this answer














Like you said you can use another cell M1 for example :
=If(M173=True,"Fine","No")

I used Fine just in case you need Yes in another place.

Select the options of your Dropdown List and define Name Fine

In K72 create Data Validation, choose List

In Source write:
=Indirect(M1)

In that case when M1 is different from Fine the dropdown is empty, when you have Fine in M1 K72 will show the values that you want to choose from the list



Update for Drop Down List become blank

Define name a blank cell No make it in a special place so you don't use it for any value (near the Fine list or yes list)

Keep the data validation Source like before =Indirect(M1)

If M1 = "No" the List will become blank (you have to choose the blank to clear the cell if it has any value)

If M1="Yes" or "Fine" whatever you choose the List will have the options you need

Just use normal word for Define Name do not use TRUE, False...



To Clear it automatically you will need VBA







share|improve this answer














share|improve this answer



share|improve this answer








edited Mar 25 '17 at 18:09

























answered Mar 25 '17 at 15:30









yass

2,4153618




2,4153618












  • Yass, thank you for getting me this far. It's definitely in the right direction. I'm trying to add one more component though. After your above suggestion, if M1=no, how do I make the drop down list appear blank? I have been trying the following formula inside the data validation window in the "source" section: =IF(INDIRECT(M1)="no","",INDIRECT(M1))
    – Edward
    Mar 25 '17 at 17:06












  • You want the drop down list to be blank or the cell of the dropdown validation automatically become blank?
    – yass
    Mar 25 '17 at 17:49










  • I will update for drop down list appear blank (not automatically the cell become blank)
    – yass
    Mar 25 '17 at 18:03










  • just the cell of the drop down validation (2nd option). in other words, the drop down list to appear only when M1=fine, otherwise blank. Right now, when M1=no, K72 still shows a value. You can't do anything, can't drop down the list, but there's a value in K72.
    – Edward
    Mar 25 '17 at 18:04










  • @Edward,, If you want the Drop Down blank when Cell M173 is False then do this. Select any blank cell & Name it NO. Then whenever M173 will be False Excel will replace DD value with Blank.
    – Rajesh S
    Sep 17 '18 at 6:45




















  • Yass, thank you for getting me this far. It's definitely in the right direction. I'm trying to add one more component though. After your above suggestion, if M1=no, how do I make the drop down list appear blank? I have been trying the following formula inside the data validation window in the "source" section: =IF(INDIRECT(M1)="no","",INDIRECT(M1))
    – Edward
    Mar 25 '17 at 17:06












  • You want the drop down list to be blank or the cell of the dropdown validation automatically become blank?
    – yass
    Mar 25 '17 at 17:49










  • I will update for drop down list appear blank (not automatically the cell become blank)
    – yass
    Mar 25 '17 at 18:03










  • just the cell of the drop down validation (2nd option). in other words, the drop down list to appear only when M1=fine, otherwise blank. Right now, when M1=no, K72 still shows a value. You can't do anything, can't drop down the list, but there's a value in K72.
    – Edward
    Mar 25 '17 at 18:04










  • @Edward,, If you want the Drop Down blank when Cell M173 is False then do this. Select any blank cell & Name it NO. Then whenever M173 will be False Excel will replace DD value with Blank.
    – Rajesh S
    Sep 17 '18 at 6:45


















Yass, thank you for getting me this far. It's definitely in the right direction. I'm trying to add one more component though. After your above suggestion, if M1=no, how do I make the drop down list appear blank? I have been trying the following formula inside the data validation window in the "source" section: =IF(INDIRECT(M1)="no","",INDIRECT(M1))
– Edward
Mar 25 '17 at 17:06






Yass, thank you for getting me this far. It's definitely in the right direction. I'm trying to add one more component though. After your above suggestion, if M1=no, how do I make the drop down list appear blank? I have been trying the following formula inside the data validation window in the "source" section: =IF(INDIRECT(M1)="no","",INDIRECT(M1))
– Edward
Mar 25 '17 at 17:06














You want the drop down list to be blank or the cell of the dropdown validation automatically become blank?
– yass
Mar 25 '17 at 17:49




You want the drop down list to be blank or the cell of the dropdown validation automatically become blank?
– yass
Mar 25 '17 at 17:49












I will update for drop down list appear blank (not automatically the cell become blank)
– yass
Mar 25 '17 at 18:03




I will update for drop down list appear blank (not automatically the cell become blank)
– yass
Mar 25 '17 at 18:03












just the cell of the drop down validation (2nd option). in other words, the drop down list to appear only when M1=fine, otherwise blank. Right now, when M1=no, K72 still shows a value. You can't do anything, can't drop down the list, but there's a value in K72.
– Edward
Mar 25 '17 at 18:04




just the cell of the drop down validation (2nd option). in other words, the drop down list to appear only when M1=fine, otherwise blank. Right now, when M1=no, K72 still shows a value. You can't do anything, can't drop down the list, but there's a value in K72.
– Edward
Mar 25 '17 at 18:04












@Edward,, If you want the Drop Down blank when Cell M173 is False then do this. Select any blank cell & Name it NO. Then whenever M173 will be False Excel will replace DD value with Blank.
– Rajesh S
Sep 17 '18 at 6:45






@Edward,, If you want the Drop Down blank when Cell M173 is False then do this. Select any blank cell & Name it NO. Then whenever M173 will be False Excel will replace DD value with Blank.
– Rajesh S
Sep 17 '18 at 6:45




















draft saved

draft discarded




















































Thanks for contributing an answer to Super User!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1192265%2fif-then-drop-down-list%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Plaza Victoria

Puebla de Zaragoza

Musa