Validation Issue (excel 97)

Topic

New Reply

Hello, I am using excel and I have a spreadsheet that has only 1 column where data should be entered. The problem is that a user should not be able to enter data in row 2 of that column without data first being entered in row 1 of that column. The data will be text and probaly not numeric, if that matters. It is very important that the user be restricted to enter data in the columns in a sequential manner only. I don’t want to have to use any VBA because I’ll mess it up. Any help is greatly appreciated, thanks.

-Needy in NY

Reply | Quote

Replies

I at first thought that this could be done using data validation. However, I was not able to get that to work. If you will use VBA, then a routing like the following in the Worksheet Change event procedure will do what you want.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strCell As String
    If Not Intersect(Target, Worksheets("Sheet1").Range("A2:A65536")) Is Nothing Then
        If Target.Offset(-1, 0) = "" Then
            If Worksheets("Sheet1").Range("A1").Value = "" Then
                strCell = "$A$1"
            Else
                strCell = Target.End(xlUp).Offset(1, 0).Address
            End If
            MsgBox "Data must be entered in " & strCell & " first."
            Application.EnableEvents = False
            Target.Value = ""
            Application.EnableEvents = True
            If Worksheets("Sheet1").Range("A1").Value = "" Then
                Worksheets("Sheet1").Range("A1").Select
            Else
                Target.End(xlUp).Offset(1, 0).Select
            End If
        End If
    End If
End Sub

Reply | Quote

CAN be done with data validation. Select A2:A65536 (leave A1 blank). Use Data/Validation. Select Custom from dropdown. Deselect “Ignore Blank” checkbox. Enter formula =LEN(A1)>0. Click on Error Alert tab and enter appropriate message.

Reply | Quote

Bob: Thanks. Not turning off Ignore Blank is what was killing what I was trying. I should have seen that.

Reply | Quote

Simple oversights have often killed us all!

Reply | Quote

Try this formula in the validation:

=OFFSET(INDIRECT(“rc”,FALSE),-1,0,1,1)””

(starting on row 2!)

Reply | Quote

WOW you guys and gals are amazing. Love the prompt replies and all the help. Worked like a charm. Thanks again!!!

Reply | Quote