Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation Issue (excel 97)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Issue (excel 97)

    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.

    <pre>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
    </pre>

    Legare Coleman

  3. #3
    Guy Havers
    Guest

    Re: Validation Issue (excel 97)

    If you really don't want to mess with VBA, I attach a rather clunky method using Data Validation. Not as good as Legare's, but it may suffice.

    (My advice would be to use the VBA.)

    Guy

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Issue (excel 97)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Issue (excel 97)

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

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Issue (excel 97)

    Simple oversights have often killed us all!

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Issue (excel 97)

    Try this formula in the validation:

    =OFFSET(INDIRECT("rc",FALSE),-1,0,1,1)<>""

    (starting on row 2!)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Issue (excel 97)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •