+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    675

    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. #2
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    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: copy to clipboard
    Please Login or Register  to view this content.

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

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,673

    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.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  4. #4
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    675

    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!

  5. #5
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

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

    No problem

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,843

    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.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

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

    Quote Originally Posted by popipipo View Post
    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. #8
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    675

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How do I have a cell shift over once it reaches a certain number?
    By MichelleFisherTX in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-16-2018, 01:02 PM
  2. Replies: 3
    Last Post: 05-24-2017, 07:23 AM
  3. automatically restart formulas when col reaches positve
    By gilnic in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-23-2015, 07:56 AM
  4. [SOLVED] Can you Play sound when a cell reaches a number like 12
    By gdclyde in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-26-2014, 03:04 PM
  5. Create a number sequence and restart when a cell value equals a criteria
    By manian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-15-2013, 02:46 AM
  6. changing a value of a cell when it reaches a certain number.
    By intotec in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2013, 03:04 PM
  7. Replies: 6
    Last Post: 03-30-2010, 11:11 AM

Bookmarks

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