Hard Stop - Completion of cells before moving on

July 25, 2016 at 18:04:03
Specs: Windows 64
Hi,

I've researched this topic and came up empty. I need a formula (not a macro) to do the following:

If cells A1 or A2 or A3 or A4 is blank then the User gets an error message and cannot proceed until one of the cells contains data. Is this possible?

Thank you in advance!


See More: Hard Stop - Completion of cells before moving on

Reply ↓  Report •


#1
July 25, 2016 at 19:15:16
I can think of no way this can be done using a formula,
I believe you are going to have to use a Macro.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
July 25, 2016 at 20:17:56
Is it at all possible to use a IF(OR(ISBLANK formula using custom Data Validation?

Reply ↓  Report •

#3
July 25, 2016 at 20:20:20
Proceed with what?

Please explain your process in a little more detail so that we can see if we can help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
July 25, 2016 at 20:52:49
There is no way to enforce it.
The user can simply use his mouse to click someplace else on the sheet.

You can give a warning that the cell or cells are blank,
but preventing the user from proceeding someplace else can not be accomplished with just a formula.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#5
July 26, 2016 at 05:25:33
OK, can you help me with a FORMULA that I can add in Data Validation thatl trigger the warning based on the following condition?

If cells A1 or A2 or A3 or A4 is blank then the User gets an error message

Thanks Mike


Reply ↓  Report •

#6
July 26, 2016 at 06:02:02
I'll try again.

re: "then the User gets an error message"

You want the user to get an error message when they do what?

Try to enter a value in another cell?
Try to select another cell?
Try to save the workbook?
Try to select another sheet?
Try to answer the phone?

What exactly are you trying to prevent the user from doing if those cells are blank? The solution is dependent on what you are trying to prevent.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#7
July 26, 2016 at 06:47:12
DerbyDad03,
it's my understanding, that the OP wants a formula
that will force the user to enter data into A1 or A2 or A3 or A4,
and that they can not proceed further, until data has been
entered into the specific cells.

I could do this with a Database, but unless the OP uses a macro,
I can not think of any way a formula can do it.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#8
July 26, 2016 at 06:59:54
re: "I can not think of any way a formula can do it."

I chose not to think about it at all until he tells us what it is he is trying to prevent the users from doing.

"cannot proceed" and "trigger the warning" is just not enough for me to work with.

As we have all seen quite often in this forum, getting the details of the requirements often leads to a deeper discussion, alternative processes, etc. It will probably turn out that you are 100% correct, but I'm not going to make that call just yet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#9
July 26, 2016 at 07:08:07
HarrisLyfe2016:

See this page, about half way down, the section:
Create a Message That Appears When Incorrect Data Is Entered

https://support.microsoft.com/en-us...

DerbyDad03:

Understand your point completely.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#10
July 26, 2016 at 07:44:25
My apologies - been in meetings since my last post.

DerbyDad03:

What exactly are you trying to prevent the user from doing if those cells are blank? The solution is dependent on what you are trying to prevent.
I am trying to prevent rework. If an end user fail to input data in cells A1 or A2 or A3 or A4 then I am looking for a 'Error Message' to warn the user that there is missing info.

You want the user to get an error message when they do what?

Try to enter a value in another cell? YES
Try to select another cell?
Try to save the workbook?
Try to select another sheet?
Try to answer the phone? Funny lol


mmcconaghy - thank you!

message edited by HarrisLyfe2016


Reply ↓  Report •

#11
July 26, 2016 at 08:29:43
Thanks for the additional info, but your requirements are still a bit fuzzy.

In your OP you said:

"If cells A1 or A2 or A3 or A4 is blank then the User gets an error message and cannot proceed until one of the cells contains data. Is this possible?"

If only one of the cells contains data then the others could be blank. Is that good or bad?

In other words:

If your requirement is that all 4 cells contain data, that fits your use of OR, meaning if any of them are blank, the warning should be issued.

On the other hand, if only one cell needs to contain data, then don't you mean "if cells A1 and A2 and A3 and A4 are blank" then present the warning?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#12
July 26, 2016 at 09:00:06
Let's see if I can get it right this time...

If only one of the cells contains data then the others could be blank. Is that good or bad? GOOD Thats what I want and need. At least ONE must have data.

To more specific, what I really need is for the End user to add data to cells B2,C2,D2 (at least one or all) but if the User completes E2 then there would be no need to have data in B2:D2. These cells are meant to track Error classification (if any error at all). The User inputs the error severitiy (as follows)

Major     Minor         Admin       No Error    

I think the following will help you better understand my issue/need:

  A              B              C             D              E      

1 Cust   Error Severity     Err. Sev.     Err. Sev.    No Error

2 ABC Jeans    Major      Minor         Admin            Blank 

3 Lemon Slice                                         No Error

I hope this makes more sense?

message edited by HarrisLyfe2016


Reply ↓  Report •

#13
July 26, 2016 at 09:49:28
I don't have time right now to consider your latest requirements. What I can offer is a solution to your original question, since that is what I was working on.

This Data Validation technique will prevent entry into other cells if A1:A4 are all empty. Hopefully you can modify this to fit your latest needs. If not, I'll try to take a look at this later. Let me know if you can make this work so I don't waste any more time on this.

1 - Select the range of cells that you are trying to prevent entry into if A1:A4 are all empty.
2 - Choose Data Validation
3 - Uncheck the Ignore Blank box
4 - Choose Custom from the Drop Down
5 - Enter this formula:

=NOT(AND(ISBLANK($A$1),ISBLANK($A$2),ISBLANK($A$3),ISBLANK($A$4)))

6 - Click the Error Alert tab and enter your warning message.
7 - Click OK

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#14
July 26, 2016 at 10:04:07
I thank you & my intention was not to waste your time!

message edited by HarrisLyfe2016


Reply ↓  Report •

#15
July 26, 2016 at 12:25:25
Sorry, I did not mean that to sound like you were wasting my time, but I see how my comment could be taken that way.

What I meant was that I didn't want to "waste" time going over your latest post and working on a different solution if the Data Validation suggestion I made earlier could be modified to meet to your needs, either by you or me.

I merely would like to wait until you've try it and let me know if it can made to work before I started working on a different solution if is not really needed. We're here to help, so let us know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#16
July 26, 2016 at 13:44:02
Thank you for clarifying that :) & I understand. I tried your suggestion a few times and couldnt get it to work but I plan to have at it again when I get home tonight. I appreciate all your help!

Reply ↓  Report •


Ask Question