Excel Dropdown list with number result (not text)
I am trying to create two dropdown menus in Excel, where one is dependent upon the other. I've found plenty of resources on the internet about this, but none of them seem to work since the dependent list is numbers (1,2,3, etc) rather than text. I was able to make this work with random text, but not with numbers. I also converted the numbers to text, but this didn't work either. Can anyone help me?
here is a screenshot of the table
In this example, a user would select a role in column E (first dropdown) and then the second dropdown would limit users to selecting only levels available to that role.
excel vba drop-down-menu pc
asked Nov 15 '18 at 20:45
VorsordVorsord
63
add a comment |
I am trying to create two dropdown menus in Excel, where one is dependent upon the other. I've found plenty of resources on the internet about this, but none of them seem to work since the dependent list is numbers (1,2,3, etc) rather than text. I was able to make this work with random text, but not with numbers. I also converted the numbers to text, but this didn't work either. Can anyone help me?
here is a screenshot of the table
In this example, a user would select a role in column E (first dropdown) and then the second dropdown would limit users to selecting only levels available to that role.
excel vba drop-down-menu pc
asked Nov 15 '18 at 20:45
VorsordVorsord
63
See if this helps: superuser.com/questions/223945/…
– Rey Juna
Nov 15 '18 at 21:23
add a comment |
I am trying to create two dropdown menus in Excel, where one is dependent upon the other. I've found plenty of resources on the internet about this, but none of them seem to work since the dependent list is numbers (1,2,3, etc) rather than text. I was able to make this work with random text, but not with numbers. I also converted the numbers to text, but this didn't work either. Can anyone help me?
here is a screenshot of the table
In this example, a user would select a role in column E (first dropdown) and then the second dropdown would limit users to selecting only levels available to that role.
excel vba drop-down-menu pc
asked Nov 15 '18 at 20:45
VorsordVorsord
63
I am trying to create two dropdown menus in Excel, where one is dependent upon the other. I've found plenty of resources on the internet about this, but none of them seem to work since the dependent list is numbers (1,2,3, etc) rather than text. I was able to make this work with random text, but not with numbers. I also converted the numbers to text, but this didn't work either. Can anyone help me?
here is a screenshot of the table
In this example, a user would select a role in column E (first dropdown) and then the second dropdown would limit users to selecting only levels available to that role.
excel vba drop-down-menu pc
excel vba drop-down-menu pc
asked Nov 15 '18 at 20:45
VorsordVorsord
63
asked Nov 15 '18 at 20:45
VorsordVorsord
63
edited Nov 15 '18 at 21:20
Vorsord
asked Nov 15 '18 at 20:45
VorsordVorsord
63
asked Nov 15 '18 at 20:45
VorsordVorsord
63
asked Nov 15 '18 at 20:45
VorsordVorsord
63
63
See if this helps: superuser.com/questions/223945/…
– Rey Juna
Nov 15 '18 at 21:23
add a comment |
See if this helps: superuser.com/questions/223945/…
– Rey Juna
Nov 15 '18 at 21:23
See if this helps: superuser.com/questions/223945/…
– Rey Juna
Nov 15 '18 at 21:23
See if this helps: superuser.com/questions/223945/…
– Rey Juna
Nov 15 '18 at 21:23
add a comment |
1 Answer
1
active
oldest
votes
Without using VBA the best way to accomplish nested data validation would be with Named Ranges.
It can also be done with a combination of named ranges, custom formatting, and the INDIRECT() function.
Without knowing what the expected result is of the table it is difficult to provide an exact answer.
Add numbers 1-5 in a column. Highlight the numbers 1 and 2 and create a named range "Analyst". Highlight 1-4 and type do the same for "Engineer". Repeat until all roles are named in ranges.
Then take your roles in a list, name that range "Roles".
In cell A1 type Role
In cell B1 type Level
In cell A2 apply the data validation for list and have Source: =Roles
In cell A2 name the range "Role"
In cell B2 apply the data validation for list and have Source: =INDIRECT(Role)
You will then have a number in the drop down list associated with the role.
Edit: I forgot to mention, a named range cannot be only a number, so there are limitations in respect to numeric names.
answered Nov 15 '18 at 21:47
David SDavid S
2068
Oh, I didn't know that the named ranges needed to match exactly what's in the look up list. Thanks!
– Vorsord
Nov 16 '18 at 15:01
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53327644%2fexcel-dropdown-list-with-number-result-not-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
Without using VBA the best way to accomplish nested data validation would be with Named Ranges.
It can also be done with a combination of named ranges, custom formatting, and the INDIRECT() function.
Without knowing what the expected result is of the table it is difficult to provide an exact answer.
Add numbers 1-5 in a column. Highlight the numbers 1 and 2 and create a named range "Analyst". Highlight 1-4 and type do the same for "Engineer". Repeat until all roles are named in ranges.
Then take your roles in a list, name that range "Roles".
In cell A1 type Role
In cell B1 type Level
In cell A2 apply the data validation for list and have Source: =Roles
In cell A2 name the range "Role"
In cell B2 apply the data validation for list and have Source: =INDIRECT(Role)
You will then have a number in the drop down list associated with the role.
Edit: I forgot to mention, a named range cannot be only a number, so there are limitations in respect to numeric names.
answered Nov 15 '18 at 21:47
David SDavid S
2068
Oh, I didn't know that the named ranges needed to match exactly what's in the look up list. Thanks!
– Vorsord
Nov 16 '18 at 15:01
add a comment |
Without using VBA the best way to accomplish nested data validation would be with Named Ranges.
It can also be done with a combination of named ranges, custom formatting, and the INDIRECT() function.
Without knowing what the expected result is of the table it is difficult to provide an exact answer.
Add numbers 1-5 in a column. Highlight the numbers 1 and 2 and create a named range "Analyst". Highlight 1-4 and type do the same for "Engineer". Repeat until all roles are named in ranges.
Then take your roles in a list, name that range "Roles".
In cell A1 type Role
In cell B1 type Level
In cell A2 apply the data validation for list and have Source: =Roles
In cell A2 name the range "Role"
In cell B2 apply the data validation for list and have Source: =INDIRECT(Role)
You will then have a number in the drop down list associated with the role.
Edit: I forgot to mention, a named range cannot be only a number, so there are limitations in respect to numeric names.
answered Nov 15 '18 at 21:47
David SDavid S
2068
Oh, I didn't know that the named ranges needed to match exactly what's in the look up list. Thanks!
– Vorsord
Nov 16 '18 at 15:01
add a comment |
Without using VBA the best way to accomplish nested data validation would be with Named Ranges.
It can also be done with a combination of named ranges, custom formatting, and the INDIRECT() function.
Without knowing what the expected result is of the table it is difficult to provide an exact answer.
Add numbers 1-5 in a column. Highlight the numbers 1 and 2 and create a named range "Analyst". Highlight 1-4 and type do the same for "Engineer". Repeat until all roles are named in ranges.
Then take your roles in a list, name that range "Roles".
In cell A1 type Role
In cell B1 type Level
In cell A2 apply the data validation for list and have Source: =Roles
In cell A2 name the range "Role"
In cell B2 apply the data validation for list and have Source: =INDIRECT(Role)
You will then have a number in the drop down list associated with the role.
Edit: I forgot to mention, a named range cannot be only a number, so there are limitations in respect to numeric names.
answered Nov 15 '18 at 21:47
David SDavid S
2068
Without using VBA the best way to accomplish nested data validation would be with Named Ranges.
It can also be done with a combination of named ranges, custom formatting, and the INDIRECT() function.
Without knowing what the expected result is of the table it is difficult to provide an exact answer.
Add numbers 1-5 in a column. Highlight the numbers 1 and 2 and create a named range "Analyst". Highlight 1-4 and type do the same for "Engineer". Repeat until all roles are named in ranges.
Then take your roles in a list, name that range "Roles".
In cell A1 type Role
In cell B1 type Level
In cell A2 apply the data validation for list and have Source: =Roles
In cell A2 name the range "Role"
In cell B2 apply the data validation for list and have Source: =INDIRECT(Role)
You will then have a number in the drop down list associated with the role.
Edit: I forgot to mention, a named range cannot be only a number, so there are limitations in respect to numeric names.
answered Nov 15 '18 at 21:47
David SDavid S
2068
answered Nov 15 '18 at 21:47
David SDavid S
2068
answered Nov 15 '18 at 21:47
David SDavid S
2068
answered Nov 15 '18 at 21:47
David SDavid S
2068
2068
Oh, I didn't know that the named ranges needed to match exactly what's in the look up list. Thanks!
– Vorsord
Nov 16 '18 at 15:01
add a comment |
Oh, I didn't know that the named ranges needed to match exactly what's in the look up list. Thanks!
– Vorsord
Nov 16 '18 at 15:01
Oh, I didn't know that the named ranges needed to match exactly what's in the look up list. Thanks!
– Vorsord
Nov 16 '18 at 15:01
Oh, I didn't know that the named ranges needed to match exactly what's in the look up list. Thanks!
– Vorsord
Nov 16 '18 at 15:01
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53327644%2fexcel-dropdown-list-with-number-result-not-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
See if this helps: superuser.com/questions/223945/…
– Rey Juna
Nov 15 '18 at 21:23