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

 

[Arts]

Does it look something like this?

View attachment 71915
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?

 

[Arts]

Apologies! User Error!! I had the formula the wrong way around (=F2=G2+1)

Thank you so much for this!!

I an not VBA user in the slightest but I look at the code you did which must have taken some time and then see the method through data validation and think crazy how something can be done with a few clicks or write mass amounts of code.

I really hope the code didn't take you too long to write but thank you once again for the alternative easier solution (for me anyhow)

 

[Joe4]

Apologies! User Error!! I had the formula the wrong way around (=F2=G2+1)

Thank you so much for this!!

I an not VBA user in the slightest but I look at the code you did which must have taken some time and then see the method through data validation and think crazy how something can be done with a few clicks or write mass amounts of code.

I really hope the code didn't take you too long to write but thank you once again for the alternative easier solution (for me anyhow)

You are welcome.

Yes, I was unaware initially of how easily it could be done with Data Validation, or else I would have tried that first (I don't use it a ton, and forgot that you could use formulas in it).
Sometimes, things cannot be done without VBA, though (this just isn't one of those times!).

One advantage to VBA is you can make it more robust (I was actually checking a bunch of different things, making sure both columns F and H were numeric, and that column F is populated before column H).

 

[Arts]

You are welcome.

Yes, I was unaware initially of how easily it could be done with Data Validation, or else I would have tried that first (I don't use it a ton, and forgot that you could use formulas in it).
Sometimes, things cannot be done without VBA, though (this just isn't one of those times!).

One advantage to VBA is you can make it more robust (I was actually checking a bunch of different things, making sure both columns F and H were numeric, and that column F is populated before column H).

It would be a dream of mine to use VBA but after having seen sooooo many videos all it has done has confuse me more. Seems there is no one way to write code so I've accepted a life of excel with formulas only. Thank you once again!

 

[Joe4]

It would be a dream of mine to use VBA but after having seen sooooo many videos all it has done has confuse me more.

It is best to start small from the beginning and work your way up.
If you are serious about it, then find yourself a good introductory VBA book (that is how I started).
Once you have a solid foundation, then things start to make sense.

And the Macro Recorder is a great tool!
If you turn on the Macro Recorder and record yourself performing steps manually, you will get the VBA code needed to do that.
This is a handy little trick that even us pros still use from time-to-time.

 

[Arts]

It is best to start small from the beginning and work your way up.
If you are serious about it, then find yourself a good introductory VBA book (that is how I started).
Once you have a solid foundation, then things start to make sense.

And the Macro Recorder is a great tool!
If you turn on the Macro Recorder and record yourself performing steps manually, you will get the VBA code needed to do that.
This is a handy little trick that even us pros still use from time-to-time.

what gets me is the dim stuff. When using the macro recorder you don't see any of that but when going through books and videos they talk about it like its a must??

 

[Joe4]

what gets me is the dim stuff. When using the macro recorder you don't see any of that but when going through books and videos they talk about it like its a must??

Its not a "must", but is highlighly recommend.
Using it just declares your variables before you use them, which can help prevent errors and help in debugging.

For example, you could use variables without declaring them first, like this:

x = "Hello there!"

But say that we want "x" to be a number. Then we could declare it like this first:

Dim x as Integer

Then later in our code, if we try to set x to something that is not a number, like:

x = "Hello there!"

then when we try to run the code, we will get a "Type mismatch" error and highlight that line of code.

So declaring your variables first gives you much more control over what gets put in them, and help prevents unwanted errors and unintended consequences.

There are only a limited number of data types, so this is something that you could probably learn pretty fast.

Also, if you turn on the "Option Explicit" command, which FORCES you to declare all variables, this will help you identify typos, i.e.

Dim myName as String
myNam = "Joe4"

When the code tries to run, it will alert you that you have an undeclared variables ("MyNam"), which I can quickly see is because I made a typo!

Here are details on turning on "Option Explicit": Option Explicit in Excel VBA

 

[Arts]

Its not a "must", but is highlighly recommend.
Using it just declares your variables before you use them, which can help prevent errors and help in debugging.

For example, you could use variables without declaring them first, like this:

x = "Hello there!"

But say that we want "x" to be a number. Then we could declare it like this first:

Dim x as Integer

Then later in our code, if we try to set x to something that is not a number, like:

x = "Hello there!"

then when we try to run the code, we will get a "Type mismatch" error and highlight that line of code.

So declaring your variables first gives you much more control over what gets put in them, and help prevents unwanted errors and unintended consequences.

There are only a limited number of data types, so this is something that you could probably learn pretty fast.

Also, if you turn on the "Option Explicit" command, which FORCES you to declare all variables, this will help you identify typos, i.e.

Dim myName as String
myNam = "Joe4"

When the code tries to run, it will alert you that you have an undeclared variables ("MyNam"), which I can quickly see is because I made a typo!

Here are details on turning on "Option Explicit": Option Explicit in Excel VBA

You have gone over and beyond. I shall dive into this!

 

[Arts]

Does it look something like this?

View attachment 71915
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?

Hi Joe4

Could I ask a question around this or more of how excel works. I was so happy at yourself for providing a solution I didn't get to ask the inner workings as such.

When I attempted this I was first trying (through data validation, custom) I was going with the formula in H2 =F2+1.

Why did this not work ? You seemed to have referenced the cell H2 (where the validation needs to occur and then entered what I did)

Why the need to reference the very cell we are in or is this just a case of how the custom function within data validation works...

 

[Joe4]

So when applying formulas to multi-cell ranges, whether it is using Data Validation or Conditional Formatting, there are a few important things to remember.

The first is the use of absolute/relative range referencing (see: Excel Formulas: Relative and Absolute Cell References).
This is important, as the Excel formula will automatically adjust for the other cells in your selected range. If you want to "lock in" a column or range reference, you need to do that with a "$", as described in that link (for example, if you wanted to Conditional Format en entire row dependent solely on the value in column A).
This really doesn't come into play (at least not directly) with your current question, but is important to understand and keep in mind.

The second is to make sure that you write the formula as it applies to the very first cell in your selected range, and then let Excel adjust it automatically for the other cells.
One of the most common mistakes I see is the following (and let's use your example to demonstrate it).
The want to apply this Data Validation to column H, so they first select the entire column (column H).
However, row 1 is a title/header row, so they don't want to apply the formula to this, so they think they can accomplish it by writing the formula as it applies to row 2, i.e.

H2 =F2+1

This will NOT work the way they think it will.
Since the first cell is our column H selection is H1 (since they selected the ENTIRE column), the formula above will be applied to cell H1!
And then the following formula will be applied to cell H2:

H3 =F3+1

So everything is shifted, and each cell in column H is looking at the row below it!

The important thing to remember is that the formula you write always needs to correspond to the very first cell in the range you have selected.
If you want it to skip over row 1, then you do not want to select the entire column, you want to select some range starting in cell H2 and going down as far as you need.

Does this answer your question?