Alerts and or Validation in Excel
I'm looking to create an alert for when Cell B is Blank and text is being typed into Cell C, I also need then, for Cell C to be able to have free text typed in once there are figures in Cell B.
any help greatly appreciated. thanks
microsoft-excel
asked Dec 17 '18 at 13:30
Kelly Moore
6
add a comment |
I'm looking to create an alert for when Cell B is Blank and text is being typed into Cell C, I also need then, for Cell C to be able to have free text typed in once there are figures in Cell B.
any help greatly appreciated. thanks
microsoft-excel
asked Dec 17 '18 at 13:30
Kelly Moore
6
1
Hi Kelly, What have you tried so far? With Excel formulas alone I don't you would be abble to lock/unlock cells based on the precense of data in another cell.
– dmb
Dec 17 '18 at 14:21
Hello, I worked on this a little more and I actually used an IF statement in Data validation =IF(ISBLANK(B2),"req number required",(ISTEXT(C2))) and added an Error alert. Seems to work ok and allow me to enter Text once B has been populated, but only Text, I can't input just numbers.... but this works for ma as I need to only enter text in the cells. thank you
– Kelly Moore
Dec 19 '18 at 13:51
add a comment |
I'm looking to create an alert for when Cell B is Blank and text is being typed into Cell C, I also need then, for Cell C to be able to have free text typed in once there are figures in Cell B.
any help greatly appreciated. thanks
microsoft-excel
asked Dec 17 '18 at 13:30
Kelly Moore
6
I'm looking to create an alert for when Cell B is Blank and text is being typed into Cell C, I also need then, for Cell C to be able to have free text typed in once there are figures in Cell B.
any help greatly appreciated. thanks
microsoft-excel
microsoft-excel
asked Dec 17 '18 at 13:30
Kelly Moore
6
asked Dec 17 '18 at 13:30
Kelly Moore
6
asked Dec 17 '18 at 13:30
Kelly Moore
6
asked Dec 17 '18 at 13:30
Kelly Moore
6
asked Dec 17 '18 at 13:30
Kelly Moore
6
6
1
Hi Kelly, What have you tried so far? With Excel formulas alone I don't you would be abble to lock/unlock cells based on the precense of data in another cell.
– dmb
Dec 17 '18 at 14:21
Hello, I worked on this a little more and I actually used an IF statement in Data validation =IF(ISBLANK(B2),"req number required",(ISTEXT(C2))) and added an Error alert. Seems to work ok and allow me to enter Text once B has been populated, but only Text, I can't input just numbers.... but this works for ma as I need to only enter text in the cells. thank you
– Kelly Moore
Dec 19 '18 at 13:51
add a comment |
1
Hi Kelly, What have you tried so far? With Excel formulas alone I don't you would be abble to lock/unlock cells based on the precense of data in another cell.
– dmb
Dec 17 '18 at 14:21
Hello, I worked on this a little more and I actually used an IF statement in Data validation =IF(ISBLANK(B2),"req number required",(ISTEXT(C2))) and added an Error alert. Seems to work ok and allow me to enter Text once B has been populated, but only Text, I can't input just numbers.... but this works for ma as I need to only enter text in the cells. thank you
– Kelly Moore
Dec 19 '18 at 13:51
1
1
Hi Kelly, What have you tried so far? With Excel formulas alone I don't you would be abble to lock/unlock cells based on the precense of data in another cell.
– dmb
Dec 17 '18 at 14:21
Hi Kelly, What have you tried so far? With Excel formulas alone I don't you would be abble to lock/unlock cells based on the precense of data in another cell.
– dmb
Dec 17 '18 at 14:21
Hello, I worked on this a little more and I actually used an IF statement in Data validation =IF(ISBLANK(B2),"req number required",(ISTEXT(C2))) and added an Error alert. Seems to work ok and allow me to enter Text once B has been populated, but only Text, I can't input just numbers.... but this works for ma as I need to only enter text in the cells. thank you
– Kelly Moore
Dec 19 '18 at 13:51
Hello, I worked on this a little more and I actually used an IF statement in Data validation =IF(ISBLANK(B2),"req number required",(ISTEXT(C2))) and added an Error alert. Seems to work ok and allow me to enter Text once B has been populated, but only Text, I can't input just numbers.... but this works for ma as I need to only enter text in the cells. thank you
– Kelly Moore
Dec 19 '18 at 13:51
add a comment |
2 Answers
2
active
oldest
votes
You can use data validation to prevent entering any values into cell C while cell B is empty.
Go to Data group in the ribbon - data validation
- for "allow" select
custom
- unselect "ignore blank"
- for formula enter:
=OR(AND(A2="",B2=""),A2<>"")
Now trying to enter anything to B2 will give an error if A2 is empty. Note that there is no way to prevent typing in that cell, message will appear only when typing is finished (Enter pressed or trying to move to another cell).
Also note that message will appear only when you try to type to B2 while A2 is empty, if you delete content of A2 while B2 contains anything that will not trigger it, it would require another rule.
answered Dec 18 '18 at 9:43
Máté Juhász
14.1k63150
add a comment |
You can use Excel's data validation features to display an alert message before the user enters text in column C. This will display an alert message, but it will not prevent the user from entering text.
Steps:
- Select cells in column C you want to display the alert.
- On the Data ribbon, choose
Data Validation>Data Validation....
Choose the
Input Message tabin the dialog. Here you can type a warning message that will display as tool-tip text when the user selects one the cells in column C.
Click OK. You will now see the warning message whenever one of the cells is selected.
This may not be as robust a solution as you wanted, but it may work well enough and is very easy to set up.
answered Dec 17 '18 at 16:07
Excellll
11k74162
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%2f1385256%2falerts-and-or-validation-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use data validation to prevent entering any values into cell C while cell B is empty.
Go to Data group in the ribbon - data validation
- for "allow" select
custom
- unselect "ignore blank"
- for formula enter:
=OR(AND(A2="",B2=""),A2<>"")
Now trying to enter anything to B2 will give an error if A2 is empty. Note that there is no way to prevent typing in that cell, message will appear only when typing is finished (Enter pressed or trying to move to another cell).
Also note that message will appear only when you try to type to B2 while A2 is empty, if you delete content of A2 while B2 contains anything that will not trigger it, it would require another rule.
answered Dec 18 '18 at 9:43
Máté Juhász
14.1k63150
add a comment |
You can use data validation to prevent entering any values into cell C while cell B is empty.
Go to Data group in the ribbon - data validation
- for "allow" select
custom
- unselect "ignore blank"
- for formula enter:
=OR(AND(A2="",B2=""),A2<>"")
Now trying to enter anything to B2 will give an error if A2 is empty. Note that there is no way to prevent typing in that cell, message will appear only when typing is finished (Enter pressed or trying to move to another cell).
Also note that message will appear only when you try to type to B2 while A2 is empty, if you delete content of A2 while B2 contains anything that will not trigger it, it would require another rule.
answered Dec 18 '18 at 9:43
Máté Juhász
14.1k63150
add a comment |
You can use data validation to prevent entering any values into cell C while cell B is empty.
Go to Data group in the ribbon - data validation
- for "allow" select
custom
- unselect "ignore blank"
- for formula enter:
=OR(AND(A2="",B2=""),A2<>"")
Now trying to enter anything to B2 will give an error if A2 is empty. Note that there is no way to prevent typing in that cell, message will appear only when typing is finished (Enter pressed or trying to move to another cell).
Also note that message will appear only when you try to type to B2 while A2 is empty, if you delete content of A2 while B2 contains anything that will not trigger it, it would require another rule.
answered Dec 18 '18 at 9:43
Máté Juhász
14.1k63150
You can use data validation to prevent entering any values into cell C while cell B is empty.
Go to Data group in the ribbon - data validation
- for "allow" select
custom
- unselect "ignore blank"
- for formula enter:
=OR(AND(A2="",B2=""),A2<>"")
Now trying to enter anything to B2 will give an error if A2 is empty. Note that there is no way to prevent typing in that cell, message will appear only when typing is finished (Enter pressed or trying to move to another cell).
Also note that message will appear only when you try to type to B2 while A2 is empty, if you delete content of A2 while B2 contains anything that will not trigger it, it would require another rule.
answered Dec 18 '18 at 9:43
Máté Juhász
14.1k63150
answered Dec 18 '18 at 9:43
Máté Juhász
14.1k63150
answered Dec 18 '18 at 9:43
Máté Juhász
14.1k63150
answered Dec 18 '18 at 9:43
Máté Juhász
14.1k63150
14.1k63150
add a comment |
add a comment |
You can use Excel's data validation features to display an alert message before the user enters text in column C. This will display an alert message, but it will not prevent the user from entering text.
Steps:
- Select cells in column C you want to display the alert.
- On the Data ribbon, choose
Data Validation>Data Validation....
Choose the
Input Message tabin the dialog. Here you can type a warning message that will display as tool-tip text when the user selects one the cells in column C.
Click OK. You will now see the warning message whenever one of the cells is selected.
This may not be as robust a solution as you wanted, but it may work well enough and is very easy to set up.
answered Dec 17 '18 at 16:07
Excellll
11k74162
add a comment |
You can use Excel's data validation features to display an alert message before the user enters text in column C. This will display an alert message, but it will not prevent the user from entering text.
Steps:
- Select cells in column C you want to display the alert.
- On the Data ribbon, choose
Data Validation>Data Validation....
Choose the
Input Message tabin the dialog. Here you can type a warning message that will display as tool-tip text when the user selects one the cells in column C.
Click OK. You will now see the warning message whenever one of the cells is selected.
This may not be as robust a solution as you wanted, but it may work well enough and is very easy to set up.
answered Dec 17 '18 at 16:07
Excellll
11k74162
add a comment |
You can use Excel's data validation features to display an alert message before the user enters text in column C. This will display an alert message, but it will not prevent the user from entering text.
Steps:
- Select cells in column C you want to display the alert.
- On the Data ribbon, choose
Data Validation>Data Validation....
Choose the
Input Message tabin the dialog. Here you can type a warning message that will display as tool-tip text when the user selects one the cells in column C.
Click OK. You will now see the warning message whenever one of the cells is selected.
This may not be as robust a solution as you wanted, but it may work well enough and is very easy to set up.
answered Dec 17 '18 at 16:07
Excellll
11k74162
You can use Excel's data validation features to display an alert message before the user enters text in column C. This will display an alert message, but it will not prevent the user from entering text.
Steps:
- Select cells in column C you want to display the alert.
- On the Data ribbon, choose
Data Validation>Data Validation....
Choose the
Input Message tabin the dialog. Here you can type a warning message that will display as tool-tip text when the user selects one the cells in column C.
Click OK. You will now see the warning message whenever one of the cells is selected.
This may not be as robust a solution as you wanted, but it may work well enough and is very easy to set up.
answered Dec 17 '18 at 16:07
Excellll
11k74162
answered Dec 17 '18 at 16:07
Excellll
11k74162
answered Dec 17 '18 at 16:07
Excellll
11k74162
answered Dec 17 '18 at 16:07
Excellll
11k74162
11k74162
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.
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%2f1385256%2falerts-and-or-validation-in-excel%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
1
Hi Kelly, What have you tried so far? With Excel formulas alone I don't you would be abble to lock/unlock cells based on the precense of data in another cell.
– dmb
Dec 17 '18 at 14:21
Hello, I worked on this a little more and I actually used an IF statement in Data validation =IF(ISBLANK(B2),"req number required",(ISTEXT(C2))) and added an Error alert. Seems to work ok and allow me to enter Text once B has been populated, but only Text, I can't input just numbers.... but this works for ma as I need to only enter text in the cells. thank you
– Kelly Moore
Dec 19 '18 at 13:51