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]

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?

Hey Joe4

Again what you have sent me is very useful! so thank you once again,

My question was more about the formula used and why I needed to reference the cell where the formula is going into

Maybe if I break it down in this way. You gave me the formula:

=H2=F2+1 (which went into cell H2)

I thought the below would work

=F2+1 (which would go into cell H2)

Why is it I needed to reference cell H2 within cell H2. The below I thought would have done the trick

Hope that's clearer of what I'm asking

 

[Joe4]

OK, I see.

I think you need to create a Boolean result (True/False), as it is looking for a TRUE/FALSE result so it can evaluate it.
So you either need to use a function that returns a boolean value (i.e. the "IS" functions, like "ISTEXT", "ISERROR", etc), or you need to create a formula that is comparing two things, i.e.
=A=B
=A>B
=A<B
etc.

 

[Arts]

OK, I see.

I think you need to create a Boolean result (True/False), as it is looking for a TRUE/FALSE result so it can evaluate it.
So you either need to use a function that returns a boolean value (i.e. the "IS" functions, like "ISTEXT", "ISERROR", etc), or you need to create a formula that is comparing two things, i.e.
=A=B
=A>B
=A<B
etc.

Ok I see, this is exactly what I was trying to ask, figure out as usually when you do a formula and reference a cell its usually = and cell reference so referencing the cell the formula was going in usually results in a circular reference but what you're saying makes sense,

Thank you so much once again for taking the time to explain.

 

[Joe4]

It was my pleasure.
I always like it when people just don't want an answer, but also want to understand the logic behind it.

 

[Arts]

It was my pleasure.
I always like it when people just don't want an answer, but also want to understand the logic behind it.