to contraint a number in a cell and restart when it reaches a certain number

Hi, I am not sure if this is possible or if I need a macro. I am trying to start a cell at number '500' and start until '600' so an invoice number (for example) start from 500, then 501, then 502, then 503.. etc.. and once it reaches 600, it will jump back to 500. So lets say, cell A1, has 500, and changes to 501, each time an invoice , and it will loop back to 500 once 600 has been reach, so the range of numbers will always be in between 500 to 600. I wonder if this would be possible in a formula or would I need a macro. (I am doing this as a count)

How about something like this? Just drag down as desired

Formula:

Please Login or Register  to view this content.

(This would be in cell "L2", it must reference the cell directly above it)

I think this will work:
=IF(AND(500<=L1, L1<=600),IF(L1=600,500,L1+1),500)

the number is changed each time a macro is ran, so once it reaches 600, it will automatically go back to 500. Thank you modytrane and danielexcelvba!

No problem

Originally Posted by popipipo

or just this:

Please Login or Register  to view this content.

I was just worried they may paste the formula into a row below a header or something and then the formula wouldn't work. Otherwise that's much simpler yes

this formula "=IF(L1=600,500,L1+1)" will start me from 0, instead of 500. This formula starts me from 500 which " =IF(AND(500<=L1, L1<=600),IF(L1=600,500,L1+1),500)" is what I wanted.
Thank you all, !!