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

1. ## 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)

2. ## Re: to contraint a number in a cell and restart when it reaches a certain number

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)

3. ## Re: to contraint a number in a cell and restart when it reaches a certain number

How would it change from 500 to 501, 502...etc?
Is that a manual entry or result of a formula?
If you can attach a sample file with your layout / format, it might help.

4. ## Re: to contraint a number in a cell and restart when it reaches a certain number

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

6. ## Re: to contraint a number in a cell and restart when it reaches a certain number

or just this:
``Please Login or Register  to view this content.``

7. ## Re: to contraint a number in a cell and restart when it reaches a certain number

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

8. ## Re: to contraint a number in a cell and restart when it reaches a certain number

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, !!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1