IF/THEN drop down list
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
edited Apr 8 '17 at 10:27
DavidPostill♦
103k25224258
asked Mar 25 '17 at 12:03
Edward
613
add a comment |
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
edited Apr 8 '17 at 10:27
DavidPostill♦
103k25224258
asked Mar 25 '17 at 12:03
Edward
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 listyesand use indirect(cell reference(yes)) in the list of the dropdown
– yass
Mar 25 '17 at 14:56
add a comment |
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
edited Apr 8 '17 at 10:27
DavidPostill♦
103k25224258
asked Mar 25 '17 at 12:03
Edward
613
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
microsoft-excel data-validation
edited Apr 8 '17 at 10:27
DavidPostill♦
103k25224258
asked Mar 25 '17 at 12:03
Edward
613
edited Apr 8 '17 at 10:27
DavidPostill♦
103k25224258
asked Mar 25 '17 at 12:03
Edward
613
edited Apr 8 '17 at 10:27
DavidPostill♦
103k25224258
edited Apr 8 '17 at 10:27
DavidPostill♦
103k25224258
edited Apr 8 '17 at 10:27
DavidPostill♦
103k25224258
103k25224258
asked Mar 25 '17 at 12:03
Edward
613
asked Mar 25 '17 at 12:03
Edward
613
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 listyesand use indirect(cell reference(yes)) in the list of the dropdown
– yass
Mar 25 '17 at 14:56
add a comment |
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 listyesand 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
add a comment |
1 Answer
1
active
oldest
votes
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
answered Mar 25 '17 at 15:30
yass
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 CellM173isFalsethen do this. Select any blank cell & Name itNO. Then wheneverM173will beFalseExcel will replace DD value with Blank.
– Rajesh S
Sep 17 '18 at 6:45
|
show 1 more comment
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
answered Mar 25 '17 at 15:30
yass
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 CellM173isFalsethen do this. Select any blank cell & Name itNO. Then wheneverM173will beFalseExcel will replace DD value with Blank.
– Rajesh S
Sep 17 '18 at 6:45
|
show 1 more comment
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
answered Mar 25 '17 at 15:30
yass
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 CellM173isFalsethen do this. Select any blank cell & Name itNO. Then wheneverM173will beFalseExcel will replace DD value with Blank.
– Rajesh S
Sep 17 '18 at 6:45
|
show 1 more comment
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
answered Mar 25 '17 at 15:30
yass
2,4153618
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
answered Mar 25 '17 at 15:30
yass
2,4153618
edited Mar 25 '17 at 18:09
answered Mar 25 '17 at 15:30
yass
2,4153618
answered Mar 25 '17 at 15:30
yass
2,4153618
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 CellM173isFalsethen do this. Select any blank cell & Name itNO. Then wheneverM173will beFalseExcel will replace DD value with Blank.
– Rajesh S
Sep 17 '18 at 6:45
|
show 1 more comment
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 CellM173isFalsethen do this. Select any blank cell & Name itNO. Then wheneverM173will beFalseExcel 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
|
show 1 more comment
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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
yesand use indirect(cell reference(yes)) in the list of the dropdown– yass
Mar 25 '17 at 14:56