I need an excel function that won't allow people to leave a cell blank. I tried data validation...any others?

Question

I tried doign data validation but it still allows you to leave it blank I don't want to be able to move on to the next cell until there has been a value put in there

Answer 1

Great question.

Data Validation out of the box is pretty difficult to rely on because it doesn't catch all the errors the author might expect it catch. To find out more just search "Data Validation" within MS Excel Help.

Here's how I would handle it. I would put an "if" statement somewhere in cell near where the user will be looking at after entering the data. For instance, if the user is supposed to enter data into cell B4 and you want to make sure they put an entry there then the "if" statement should be:

=if(B4 = "", "Hey screwball, enter some data!", "Calculated results below.")

If the cell is blank, then the screwball message will appear. Else the successful message will appear."

Of course, the messages should be specific to the situation. Also, if you want to further enforce the data entry you can try something like this. Let's use a simple example where users have to enter their name in cell B4 and birthday in cell B5 and you will return their year of birth. You want to make sure they enter a name.

In the cell where year of birth will be displayed use the statement:

=if(B4 = "","You must enter your name.", Year(B5))

In this case I would use the data validation to make sure the value in cell B4 is a date format. However, to catch the null value I would also use the "if" statement above.

Short of this, I agree with the previous people who suggested using VB to control the position of the cursor based on a validation rule that you write. However, this solution is not a simple Excel formula that resides in a cell. Look in Tools -> Macros... -> Visual Basic Editor to get started.

Answer 2

Just set the focus to the cell that they are trying to leave if the value does not fall within your validation rules. Easier said then done.

How are you doing the validation? Thru VBA code? I am not too familiar with all the ins and outs of Excel and I am not sure how you are accomplishing your data validation.

Maybe someone else will have a better idea.

Answer 3

Assuming that your form needs to be filled out in Column "C" starting from the top

Right Click the sheet tab and select "View Code"
From the first drop down box select "Worksheet"
The following macro will be displayed
Private Sub Worksheet_SelectionChange......

End Sub

Copy the following code and paste it in the space between the lines Private Sub…… & End Sub

For Count = 1 To 1000
If Cells(Count, 3) = "" Then GoTo Line1
If Cells(Count, 3) = " " Then GoTo Line1
Next Count
Line1:
Cells(Count, 3).Select

One will not be able to leave a cell blank

Answer 4

Set an initial value which is unacceptable as a result to the cell and then check to see if that initial value is still the same as the current value when they are at the place you don't want them to be at ergo it would move the input back to the cell where you want the data entered if they bypass it.

Answer 5

More info needed-
If it is just that you need something in the cells, you can put a zero in all the cells and set your options so that the zero does not appear, making the cell look blank.

You could also write a visual basic macro to where they fill in a user screen there that posts to the spread sheet, coding it so they have to fill out all choices.

Answer 6

I like BigRez's process, but his formulation honestly yields the opposite of what you need. This is a system that does work, and at the least for me, is somewhat less difficult to recognize: =IF(AND(ISBLANK(D1), now not(ISBLANK(A1))),1,zero) it is cut up on two line to display competently here. Which you can copy and paste it into cell E1, then backspace to do away with the road break.

Answer 7

I like BigRez's procedure, however his system truthfully yields the reverse of what you desire. Here's a system that does paintings, and a minimum of for me, is a bit simpler to recognize: =IF(AND(ISBLANK(D1), NOT(ISBLANK(A1))),a million,zero) It's cut up on 2 line to show accurately right here. You can reproduction and paste it into mobilephone E1, then backspace to get rid of the road holiday.

Answer 8

i think i answer you earlier

Use a VBA function fully written, nothing too complicated, just check your input is valid then link them together and drop it into ta third 'new' location

any further help contact me bellow