Excel Dependent List Push back
I have created a dependent list drop down for a range of cells. However when I change the cell it is dependent one while the drop down changes the options i can choose it doesn't push back an error that the data validation is still incorrect.
As an example
I chose High and that lets me select 80%, 90% or 100%. So i chose 90%
But when I change it to Medium (which should only be 40%-70%) it still shows that the 90% is valid.
Thanks,
excel
asked Nov 20 at 16:33
Caleb Hill
62
|
show 2 more comments
I have created a dependent list drop down for a range of cells. However when I change the cell it is dependent one while the drop down changes the options i can choose it doesn't push back an error that the data validation is still incorrect.
As an example
I chose High and that lets me select 80%, 90% or 100%. So i chose 90%
But when I change it to Medium (which should only be 40%-70%) it still shows that the 90% is valid.
Thanks,
excel
asked Nov 20 at 16:33
Caleb Hill
62
So when you select Medium, are you looking for Excel to bring you back to that drop-down and force you to change it from 90% to something else?
– dwirony
Nov 20 at 16:44
Exactly. I just want to make sure that it forces me to make the change so that I don't accidentally add a invalid value.
– Caleb Hill
Nov 20 at 16:46
I think you'd need aWorksheet_Changeevent to force you to make that change then. Are you using VBA now or some kind ofINDIRECTformula?
– dwirony
Nov 20 at 16:50
Indirect Formula.
– Caleb Hill
Nov 20 at 16:51
Yeah I don't think there's any kind of built in functionality to force you to change the value of that cell, as theINDIRECTformula drop-down list is already kind of a workaround. You would need aWorksheet_Change()event to constantly be checking the values. I can post a small example for you.
– dwirony
Nov 20 at 16:58
|
show 2 more comments
I have created a dependent list drop down for a range of cells. However when I change the cell it is dependent one while the drop down changes the options i can choose it doesn't push back an error that the data validation is still incorrect.
As an example
I chose High and that lets me select 80%, 90% or 100%. So i chose 90%
But when I change it to Medium (which should only be 40%-70%) it still shows that the 90% is valid.
Thanks,
excel
asked Nov 20 at 16:33
Caleb Hill
62
I have created a dependent list drop down for a range of cells. However when I change the cell it is dependent one while the drop down changes the options i can choose it doesn't push back an error that the data validation is still incorrect.
As an example
I chose High and that lets me select 80%, 90% or 100%. So i chose 90%
But when I change it to Medium (which should only be 40%-70%) it still shows that the 90% is valid.
Thanks,
excel
excel
asked Nov 20 at 16:33
Caleb Hill
62
asked Nov 20 at 16:33
Caleb Hill
62
asked Nov 20 at 16:33
Caleb Hill
62
asked Nov 20 at 16:33
Caleb Hill
62
asked Nov 20 at 16:33
Caleb Hill
62
62
So when you select Medium, are you looking for Excel to bring you back to that drop-down and force you to change it from 90% to something else?
– dwirony
Nov 20 at 16:44
Exactly. I just want to make sure that it forces me to make the change so that I don't accidentally add a invalid value.
– Caleb Hill
Nov 20 at 16:46
I think you'd need aWorksheet_Changeevent to force you to make that change then. Are you using VBA now or some kind ofINDIRECTformula?
– dwirony
Nov 20 at 16:50
Indirect Formula.
– Caleb Hill
Nov 20 at 16:51
Yeah I don't think there's any kind of built in functionality to force you to change the value of that cell, as theINDIRECTformula drop-down list is already kind of a workaround. You would need aWorksheet_Change()event to constantly be checking the values. I can post a small example for you.
– dwirony
Nov 20 at 16:58
|
show 2 more comments
So when you select Medium, are you looking for Excel to bring you back to that drop-down and force you to change it from 90% to something else?
– dwirony
Nov 20 at 16:44
Exactly. I just want to make sure that it forces me to make the change so that I don't accidentally add a invalid value.
– Caleb Hill
Nov 20 at 16:46
I think you'd need aWorksheet_Changeevent to force you to make that change then. Are you using VBA now or some kind ofINDIRECTformula?
– dwirony
Nov 20 at 16:50
Indirect Formula.
– Caleb Hill
Nov 20 at 16:51
Yeah I don't think there's any kind of built in functionality to force you to change the value of that cell, as theINDIRECTformula drop-down list is already kind of a workaround. You would need aWorksheet_Change()event to constantly be checking the values. I can post a small example for you.
– dwirony
Nov 20 at 16:58
So when you select Medium, are you looking for Excel to bring you back to that drop-down and force you to change it from 90% to something else?
– dwirony
Nov 20 at 16:44
So when you select Medium, are you looking for Excel to bring you back to that drop-down and force you to change it from 90% to something else?
– dwirony
Nov 20 at 16:44
Exactly. I just want to make sure that it forces me to make the change so that I don't accidentally add a invalid value.
– Caleb Hill
Nov 20 at 16:46
Exactly. I just want to make sure that it forces me to make the change so that I don't accidentally add a invalid value.
– Caleb Hill
Nov 20 at 16:46
I think you'd need a
Worksheet_Change event to force you to make that change then. Are you using VBA now or some kind of INDIRECT formula?– dwirony
Nov 20 at 16:50
I think you'd need a
Worksheet_Change event to force you to make that change then. Are you using VBA now or some kind of INDIRECT formula?– dwirony
Nov 20 at 16:50
Indirect Formula.
– Caleb Hill
Nov 20 at 16:51
Indirect Formula.
– Caleb Hill
Nov 20 at 16:51
Yeah I don't think there's any kind of built in functionality to force you to change the value of that cell, as the
INDIRECT formula drop-down list is already kind of a workaround. You would need a Worksheet_Change() event to constantly be checking the values. I can post a small example for you.– dwirony
Nov 20 at 16:58
Yeah I don't think there's any kind of built in functionality to force you to change the value of that cell, as the
INDIRECT formula drop-down list is already kind of a workaround. You would need a Worksheet_Change() event to constantly be checking the values. I can post a small example for you.– dwirony
Nov 20 at 16:58
|
show 2 more comments
1 Answer
1
active
oldest
votes
Here's an example where if the value in B1 is set to something greater than 70 and the value in A1 is switched to "Medium", then the focus is brought to B1 and the dropdown list is opened:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Range("A1").Value = "Medium" And Range("B1").Value > 70 Then
Range("B1").Activate
SendKeys "%{down}", True
DoEvents
SendKeys "{SCROLLLOCK}"
End If
End If
End Sub
Forces open the list like this:
answered Nov 20 at 17:00
dwirony
3,64431233
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%2f53397485%2fexcel-dependent-list-push-back%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
Here's an example where if the value in B1 is set to something greater than 70 and the value in A1 is switched to "Medium", then the focus is brought to B1 and the dropdown list is opened:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Range("A1").Value = "Medium" And Range("B1").Value > 70 Then
Range("B1").Activate
SendKeys "%{down}", True
DoEvents
SendKeys "{SCROLLLOCK}"
End If
End If
End Sub
Forces open the list like this:
answered Nov 20 at 17:00
dwirony
3,64431233
add a comment |
Here's an example where if the value in B1 is set to something greater than 70 and the value in A1 is switched to "Medium", then the focus is brought to B1 and the dropdown list is opened:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Range("A1").Value = "Medium" And Range("B1").Value > 70 Then
Range("B1").Activate
SendKeys "%{down}", True
DoEvents
SendKeys "{SCROLLLOCK}"
End If
End If
End Sub
Forces open the list like this:
answered Nov 20 at 17:00
dwirony
3,64431233
add a comment |
Here's an example where if the value in B1 is set to something greater than 70 and the value in A1 is switched to "Medium", then the focus is brought to B1 and the dropdown list is opened:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Range("A1").Value = "Medium" And Range("B1").Value > 70 Then
Range("B1").Activate
SendKeys "%{down}", True
DoEvents
SendKeys "{SCROLLLOCK}"
End If
End If
End Sub
Forces open the list like this:
answered Nov 20 at 17:00
dwirony
3,64431233
Here's an example where if the value in B1 is set to something greater than 70 and the value in A1 is switched to "Medium", then the focus is brought to B1 and the dropdown list is opened:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Range("A1").Value = "Medium" And Range("B1").Value > 70 Then
Range("B1").Activate
SendKeys "%{down}", True
DoEvents
SendKeys "{SCROLLLOCK}"
End If
End If
End Sub
Forces open the list like this:
answered Nov 20 at 17:00
dwirony
3,64431233
answered Nov 20 at 17:00
dwirony
3,64431233
answered Nov 20 at 17:00
dwirony
3,64431233
answered Nov 20 at 17:00
dwirony
3,64431233
3,64431233
add a comment |
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.
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%2fstackoverflow.com%2fquestions%2f53397485%2fexcel-dependent-list-push-back%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
So when you select Medium, are you looking for Excel to bring you back to that drop-down and force you to change it from 90% to something else?
– dwirony
Nov 20 at 16:44
Exactly. I just want to make sure that it forces me to make the change so that I don't accidentally add a invalid value.
– Caleb Hill
Nov 20 at 16:46
I think you'd need a
Worksheet_Changeevent to force you to make that change then. Are you using VBA now or some kind ofINDIRECTformula?– dwirony
Nov 20 at 16:50
Indirect Formula.
– Caleb Hill
Nov 20 at 16:51
Yeah I don't think there's any kind of built in functionality to force you to change the value of that cell, as the
INDIRECTformula drop-down list is already kind of a workaround. You would need aWorksheet_Change()event to constantly be checking the values. I can post a small example for you.– dwirony
Nov 20 at 16:58