Input must be last entry + 1?

[Ed]

I would like to set some sort of validation to ensure the number entered in
a column is the last number + 1. The hitch is that the last number in that
column may be several rows back, so I can't use "previous cell+1". And I
tried MAX, but couldn't get it to work (which may just be my ignorance).

Any help is appreciated.

Ed

 

[David McRitchie]

Hi Ed,
Since you did try MAX perhaps you can use this formula

Select cell B3 then select the entire column Ctrl+Spacebar
( I have active words which uses that shortcut)

Then Data, Validation
=MAX(B$2:OFFSET(B3,-1,0))+1=B3

This may be rather frustrating if you try to change anything
you did earlier. Copy and paste wipes out validation and it
wipes out conditional formatting.

 

[Guest]

Thanks, David. I'll give it a shot

Ed

 

[Earl Kiosterud]

Ed,

Try this Data - Validation formula. It's for column I where the active
(white) cell in your selection is I1:

=I1=MAX(OFFSET($I$1,0,0,ROW(I1)-1,1))+1

It will tell you the formula evaluates to an error. Ignore that. You'll
have to have put your label for the column in I1 first. It starts with I2.