How to have a drop-down in a cell only if certain cells have text
I am trying to put a drop-down in one cell with options "Open" or "Closed." However, I don't want the user to be able to select from the drop-down unless they have entered text in cells A2:G2.
I figure some dependent data validation would work, but I don't know how to get it to search A2:G2 for text first. How can I do this?
Thanks in advance for any assistance.
microsoft-excel data-validation
edited May 30 '14 at 1:16
Excellll
11.2k74164
asked May 29 '14 at 1:55
user327477user327477
612
add a comment |
I am trying to put a drop-down in one cell with options "Open" or "Closed." However, I don't want the user to be able to select from the drop-down unless they have entered text in cells A2:G2.
I figure some dependent data validation would work, but I don't know how to get it to search A2:G2 for text first. How can I do this?
Thanks in advance for any assistance.
microsoft-excel data-validation
edited May 30 '14 at 1:16
Excellll
11.2k74164
asked May 29 '14 at 1:55
user327477user327477
612
add a comment |
I am trying to put a drop-down in one cell with options "Open" or "Closed." However, I don't want the user to be able to select from the drop-down unless they have entered text in cells A2:G2.
I figure some dependent data validation would work, but I don't know how to get it to search A2:G2 for text first. How can I do this?
Thanks in advance for any assistance.
microsoft-excel data-validation
edited May 30 '14 at 1:16
Excellll
11.2k74164
asked May 29 '14 at 1:55
user327477user327477
612
I am trying to put a drop-down in one cell with options "Open" or "Closed." However, I don't want the user to be able to select from the drop-down unless they have entered text in cells A2:G2.
I figure some dependent data validation would work, but I don't know how to get it to search A2:G2 for text first. How can I do this?
Thanks in advance for any assistance.
microsoft-excel data-validation
microsoft-excel data-validation
edited May 30 '14 at 1:16
Excellll
11.2k74164
asked May 29 '14 at 1:55
user327477user327477
612
edited May 30 '14 at 1:16
Excellll
11.2k74164
asked May 29 '14 at 1:55
user327477user327477
612
edited May 30 '14 at 1:16
Excellll
11.2k74164
edited May 30 '14 at 1:16
Excellll
11.2k74164
edited May 30 '14 at 1:16
Excellll
11.2k74164
11.2k74164
asked May 29 '14 at 1:55
user327477user327477
612
asked May 29 '14 at 1:55
user327477user327477
612
asked May 29 '14 at 1:55
user327477user327477
612
612
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Somewhere else in your workbook create a table. Make the first row of that table blank, populate the second with Open and the third with closed. This will be the list your data validation will refer to.
For the purposes of keeping this answer simple I've put this table in column K of the same sheet
I'm also assuming that the cells you want to be completed (A to G) will be truly blank if not completed
Set your data validation to List and enter this formula:
=IF(COUNTA($A2:$G2)<7,$K$2,$K$3:$K$4)
The first step will count all completed cells in the required area and if the result is less than expected the dropdown is referred to the blank cell (you can't just say, "return blank") so the only item in tghe list is... nothing. Then if the cells are completed it populates the dropdown with the required options.
answered May 29 '14 at 8:16
CLockeWorkCLockeWork
1,7771022
add a 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%2f760001%2fhow-to-have-a-drop-down-in-a-cell-only-if-certain-cells-have-text%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
Somewhere else in your workbook create a table. Make the first row of that table blank, populate the second with Open and the third with closed. This will be the list your data validation will refer to.
For the purposes of keeping this answer simple I've put this table in column K of the same sheet
I'm also assuming that the cells you want to be completed (A to G) will be truly blank if not completed
Set your data validation to List and enter this formula:
=IF(COUNTA($A2:$G2)<7,$K$2,$K$3:$K$4)
The first step will count all completed cells in the required area and if the result is less than expected the dropdown is referred to the blank cell (you can't just say, "return blank") so the only item in tghe list is... nothing. Then if the cells are completed it populates the dropdown with the required options.
answered May 29 '14 at 8:16
CLockeWorkCLockeWork
1,7771022
add a comment |
Somewhere else in your workbook create a table. Make the first row of that table blank, populate the second with Open and the third with closed. This will be the list your data validation will refer to.
For the purposes of keeping this answer simple I've put this table in column K of the same sheet
I'm also assuming that the cells you want to be completed (A to G) will be truly blank if not completed
Set your data validation to List and enter this formula:
=IF(COUNTA($A2:$G2)<7,$K$2,$K$3:$K$4)
The first step will count all completed cells in the required area and if the result is less than expected the dropdown is referred to the blank cell (you can't just say, "return blank") so the only item in tghe list is... nothing. Then if the cells are completed it populates the dropdown with the required options.
answered May 29 '14 at 8:16
CLockeWorkCLockeWork
1,7771022
add a comment |
Somewhere else in your workbook create a table. Make the first row of that table blank, populate the second with Open and the third with closed. This will be the list your data validation will refer to.
For the purposes of keeping this answer simple I've put this table in column K of the same sheet
I'm also assuming that the cells you want to be completed (A to G) will be truly blank if not completed
Set your data validation to List and enter this formula:
=IF(COUNTA($A2:$G2)<7,$K$2,$K$3:$K$4)
The first step will count all completed cells in the required area and if the result is less than expected the dropdown is referred to the blank cell (you can't just say, "return blank") so the only item in tghe list is... nothing. Then if the cells are completed it populates the dropdown with the required options.
answered May 29 '14 at 8:16
CLockeWorkCLockeWork
1,7771022
Somewhere else in your workbook create a table. Make the first row of that table blank, populate the second with Open and the third with closed. This will be the list your data validation will refer to.
For the purposes of keeping this answer simple I've put this table in column K of the same sheet
I'm also assuming that the cells you want to be completed (A to G) will be truly blank if not completed
Set your data validation to List and enter this formula:
=IF(COUNTA($A2:$G2)<7,$K$2,$K$3:$K$4)
The first step will count all completed cells in the required area and if the result is less than expected the dropdown is referred to the blank cell (you can't just say, "return blank") so the only item in tghe list is... nothing. Then if the cells are completed it populates the dropdown with the required options.
answered May 29 '14 at 8:16
CLockeWorkCLockeWork
1,7771022
answered May 29 '14 at 8:16
CLockeWorkCLockeWork
1,7771022
answered May 29 '14 at 8:16
CLockeWorkCLockeWork
1,7771022
answered May 29 '14 at 8:16
CLockeWorkCLockeWork
1,7771022
1,7771022
add a comment |
add a 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.
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%2f760001%2fhow-to-have-a-drop-down-in-a-cell-only-if-certain-cells-have-text%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
