Data Validation (I think)

[Arts]

Hi all

This seems to have me stumped, but simple I'm sure for the excel guru's on here

If I have a value in F2 say 2 (Check point 2) the next checkpoint can only be 3. How would I have it that the only value the person can enter in the next check point request be 3 (So a header saying check point request in H2). And then when that check point is complete the next time they can only enter is 4?

I have looked at data validation but am not sure how to tailor it as such based on another cells value and to only increment in 1's with an error message saying why they cant enter anything more than 1.
Hope that makes sense

As usual your help is always appreciated

Arts

 

[Joe4]

Can you show us what your data strucutre looks like, being sure to show us exactly where the last check point appears on the sheet, and where they will be entering in the next one?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Arts]

Can you show us what your data strucutre looks like, being sure to show us exactly where the last check point appears on the sheet, and where they will be entering in the next one?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Joe4

Thanks for your response, please see information pasted below, so in "checkpoint being requested" this can only be 3 as it has 2 in "current checkpoint".
And then when a new request comes in for that and other projects the process is repeated until completion.

I was hoping for a non macro way

Project Code	Project Name	Current Checkpoint	Sponsor Name	Checkpoint being requested
A1	Data	2​	Homer Smith	The value in here can only be 3 based on the value of what it says in "Current Checkpoint"

 

[Joe4]

So, are you saying that the value in column H should always be limited by the existing value in column F of the same row?
Is column F always being populated before column H?

 

[Arts]

So, are you saying that the value in column H should always be limited by the existing value in column F of the same row?
Is column F always being populated before column H?

That is correct Joe4, spot on.

Column H can only be populated based on the value of Column F which will always be by an increase 1. (Impossible to jump checkpoints ie cant go from 1 to 3.)

 

[Joe4]

That is correct Joe4, spot on.

Column H can only be populated based on the value of Column F which will always be by an increase 1. (Impossible to jump checkpoints ie cant go from 1 to 3.)

OK, I would handle this a bit differently, with VBA, that checks the values that are entering into column H as they are entered and validates them.
The following code will only allow them to enter a number in column H if there is a number already in column F, and the number in column H is exactly one more than the value in column F.

To place this code in the correct location, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Check for updates to column H
    Set rng = Intersect(Target, Range("H:H"))
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
'   Check entries in column H
    For Each cell In rng
'       Check to see if column H is a valid number
        If Not IsNumeric(cell) Then
            MsgBox "You cannot enter a non-numeric value in column H"
            cell.ClearContents
        Else
'           Check to see if column H has an entry by column F does not
            If cell <> "" And cell.Offset(0, -2) = "" Then
                MsgBox "You cannot enter a value in column H until column F is populated."
                cell.ClearContents
            Else
'               Check to see if column F is a number
                If IsNumeric(cell.Offset(0, -2)) Then
'                   Check to see if column H is exactly one more than column F
                    If Round(cell - cell.Offset(0, -2), 2) <> 1 Then
                        MsgBox "You can only enter a value of " & Round(cell.Offset(0, -2) + 1, 0) & " into column H of that row."
                        cell.ClearContents
                    End If
                Else
                    MsgBox "You cannot enter a value in column H until column F is populated with a valid number."
                    cell.ClearContents
                End If
            End If
        End If
    Next cell

    Application.EnableEvents = True
    
End Sub

 

[Arts]

OK, I would handle this a bit differently, with VBA, that checks the values that are entering into column H as they are entered and validates them.
The following code will only allow them to enter a number in column H if there is a number already in column F, and the number in column H is exactly one more than the value in column F.

To place this code in the correct location, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   Check for updates to column H
    Set rng = Intersect(Target, Range("H:H"))
    If rng Is Nothing Then Exit Sub
   
    Application.EnableEvents = False
   
'   Check entries in column H
    For Each cell In rng
'       Check to see if column H is a valid number
        If Not IsNumeric(cell) Then
            MsgBox "You cannot enter a non-numeric value in column H"
            cell.ClearContents
        Else
'           Check to see if column H has an entry by column F does not
            If cell <> "" And cell.Offset(0, -2) = "" Then
                MsgBox "You cannot enter a value in column H until column F is populated."
                cell.ClearContents
            Else
'               Check to see if column F is a number
                If IsNumeric(cell.Offset(0, -2)) Then
'                   Check to see if column H is exactly one more than column F
                    If Round(cell - cell.Offset(0, -2), 2) <> 1 Then
                        MsgBox "You can only enter a value of " & Round(cell.Offset(0, -2) + 1, 0) & " into column H of that row."
                        cell.ClearContents
                    End If
                Else
                    MsgBox "You cannot enter a value in column H until column F is populated with a valid number."
                    cell.ClearContents
                End If
            End If
        End If
    Next cell

    Application.EnableEvents = True
   
End Sub

Thank you for this, and the time spent on this. I try and stay clear of anything VBA for experiences of macros failing and then no one around to "fix" them

Is VBA the only way to handle this?

 

[Joe4]

Thank you for this, and the time spent on this. I try and stay clear of anything VBA for experiences of macros failing and then no one around to "fix" them

Is VBA the only way to handle this?

OK, I was playing around with Data Validation, and I think I see a way you can do it.
Select all the rows in column H you want to apply this to (let's say H2:H100 for this example).
Then, in Data Validation, select the "Custom" option, and enter this formula:

=H2=F2+1

and then add any custom error message you want.

I think that might do what you want.

 

[Arts]

OK, I was playing around with Data Validation, and I think I see a way you can do it.
Select all the rows in column H you want to apply this to (let's say H2:H100 for this example).
Then, in Data Validation, select the "Custom" option, and enter this formula:

=H2=F2+1

and then add any custom error message you want.

I think that might do what you want.

hmmm, That doesn't seem to work for me. Doesn't seem to allow me to enter any number without the error coming up

 

[Joe4]

Does it look something like this?

What range did you select?
What formula did you enter?
Are your numbers are entered as numbers or text?
Can you post a small sample of your data?