Custom data validation formula

[sangeeta25]

Hi everyone!

I am currently working on a spreadsheet and I am facing some difficulties in regards to custom data validation.

Basically i have a column which contains consecutive numbers, i.e. cell G9 contains 126, G10= 127, G11= 128, G12= 129....etc... so what i would like to do is.. when someone insert a new row, then cell G13 for instance should only allow for the number 130, thus every next G"" cell should only contain the number above the previous G"" cell. Thus i believe that i need a formula to insert for column G in the data validation custom formula box, by which the formula I cannot figure out.

Could anyone please help me with this?

If this is slightly confusing, please do inform.

Sangeeta

 

[Scrappy666]

Hi Sang,

Instead of validation where the user would have to amend the cells each time a row is added, could you use a formula in the column to auto populate the numbers?
My thought would be =row()+115, copied all the way down should give you what you need.

If you do use validation and add a row, it will keep the numbers the same and give no error until the cell is amended.
(Not really helping your situation)

Thanks,
Dan.

 

[sangeeta25]

Hi Dan,

Thanks for your suggestion.

However as this may work, everytime i insert a new row the new G cell for the new row does not automatically contain the next number above the number in the previous cell??

Any suggestions?

Thanks

Sangeeta

 

[Scrappy666]

You would have to drag the formula down to the new row.. unfortunately this is a downside of doing it this way.
Although I can't think of a better one without using VBA. (And i'm no expert at VBA)

 

[sangeeta25]

Thanks Dan, that's what i thought, that VBA may be the only way to automate the row in terms of numbering.

So i'll fish around for some coding.

Thanks again