Data Validation (I think)

Arts

Well-known Member
Joined
Sep 28, 2007
Messages
615
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Arts

Well-known Member
Joined
Sep 28, 2007
Messages
615
Office Version
  1. 2016
Platform
  1. Windows
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 CodeProject NameCurrent CheckpointSponsor NameCheckpoint being requested
A1Data
2​
Homer SmithThe value in here can only be 3 based on the value of what it says in "Current Checkpoint"
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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?
 
Upvote 0

Arts

Well-known Member
Joined
Sep 28, 2007
Messages
615
Office Version
  1. 2016
Platform
  1. Windows
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.)
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 
Upvote 0

Arts

Well-known Member
Joined
Sep 28, 2007
Messages
615
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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?
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=H2=F2+1
and then add any custom error message you want.

I think that might do what you want.
 
Upvote 0
Solution

Arts

Well-known Member
Joined
Sep 28, 2007
Messages
615
Office Version
  1. 2016
Platform
  1. Windows
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:
Excel 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
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
Does it look something like this?

1660831109419.png

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?
 
Upvote 0

Forum statistics

Threads
1,186,743
Messages
5,959,507
Members
438,429
Latest member
Ekana99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top