Locking cells in Excel

I was wondering if one had a row of say 10 cells. Is it possible to set it up so that the person filling out the row would not be able to write into a cell if there were empty cells previously.

So say, they typed the person's name in cell A2, then their phone number in cell B2. Then the person filling out the form tried to enter the street name in D2 before entering the cell phone in C2.

I would really like it if it shocked them with 50 volts, but I am rather sure that is a violation of the Federal Employee Regulations.

As you can probably figure out, I have an employee who doesn't know how or won't fill out an Excel sheet correctly. I suppose this is the last chance before firing them.
LVL 1
Bert2005Asked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
To implement Data Validation follow these few steps:

1) Select the required cells except for the first and last as these will need slightly different rule
2) Select the Data Ribbon Tab and select the Data Validation button
3) A small popup window will appear. On the settings tab there are two dropdowns only one of which is enabled by Default. From that Dropdown select "Custom" and a formula entry line will appear.
4) Enter the formula =AND(A1<>"",C1="") on the assumption that cell #1 is A1 and cell #3 is C1. Assuming this is B1, this will only allow an entry in B1 when A1 is already completed and C1 is still blank.

If you want to put a message that shows when the cell is selected by the user put this on the Input Message tab, if you want a message displayed when the user does something wrong (again!!) put this on the Error Alert tab, I don't think "Do it right or you're fired!" will go down too well though!!

5) Click OK

For the first and last cells, it is the same process but the formulas will be different:

Cell #1  =B1=""  (checks that the next cell is blank)
Cell #12 =K1<>"" (checks that the previous cell is not blank)

This all assumes that the 12 cells to be completed are cells A1:L1

Hope this helps.

Thanks
Rob H
0
 
MacroShadowConnect With a Mentor Commented:
Put this in the Worksheet module:
Dim blnReset As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not blnReset Then
        If Target.Offset(, -1).Value = "" Then
            MsgBox "Darn"
            blnReset = True
            Target.Value = ""
        End If
    End If

End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Does it really matter what order the cells are completed so long as all 10 get completed?

Alternatively, set the sheet cursor movement so that when they press enter the cursor goes to the next cell to the right. When it gets to column 11 it checks if all cells are completed and if so it goes down a row and back to the beginning ready for next input; otherwise it goes back along the row to find the empty cell.

Thanks
Rob H
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bert2005Author Commented:
Thanks Macroshadow, I will give that a try.

Thanks Rob Henson  Yes, it does matter. The information needs to be entered in a particular sequence. I have tried to work with her for weeks. Cell #3 HAS to be used prior to Cell #4, but try as I might, Cells four, five, six, etc. are filled in first. Maybe if cells four, five, etc. can't be used, she will get the message.

I know. Should just let her go, but I am trying to give her every chance possible.
0
 
MacroShadowCommented:
I know. Should just let her go, but I am trying to give her every chance possible.
Good for you :-)
0
 
Bert2005Author Commented:
Thanks
0
 
Rob HensonFinance AnalystCommented:
You could set Data Validation on cell 2 to validate that cell 1 is not empty and cell 3 is empty cell 1 is A1 and validation on B1 would be:
 
=AND(A1<>"",C1="")

Thanks
Rob
0
 
Rob HensonFinance AnalystCommented:
Copied across or down would move cell references accordingly.
0
 
Bert2005Author Commented:
Thanks Rob,

OK, so as you have noticed, I am very bad with Excel. Do you put the above in each cell or do you start with cell one and go to, say, cell 12. Maybe you could upload a picture?
0
 
Bert2005Author Commented:
So with this, it assures that basically the user must go through one cell at a time A1 through L1.

Generally, she will enter:

Consultant (cell 1) | Patient (cell 2) |       ****      (cell 3) | sent records (cell 4)

**** where (cell 3) is who called

The referral MUST be done in a certain order for optimal efficiency. She will skip cell 3 EVERY time even though it is there. She expects that faxing records to a consultant without any other communication with that office will result in a consultation. Obviously, without your being in medicine, you can probably still think of reasons to always call the consultant first:

Patient may be too old
Consultant may not accept the insurance
They may get the patient in sooner if they know how sick she is
Some will say, "we have an opening at 4 pm if they can get here."

One would think that having a numbered group of steps would be enough. I have said at least 10 times, when I message you the consult, you do three things. If the consultant is Dr. Jones and the patient is Steve Smith, you type Dr. Jones | Steve Smith | Spoke with Karen on phone |

So, maybe when she goes to type that she sent records, and she can't type it in, she will come get me.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.