+ Reply to Thread
Results 1 to 4 of 4

autonumber (from high to low) and restart after reaching certain number

  1. #1
    Registered User
    Join Date
    01-10-2018
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2

    autonumber (from high to low) and restart after reaching certain number

    doing a project that changes what floor of a building i am on daily. due to weather delays, the original schedule is no longer accurate. I am attempting to put together an interactive schedule that keeps track of which floor i am on and what day.

    I put together a formula [=IF(B2=3,39,IF(ISTEXT(C3),"",MIN($B$2:B2)-1))] that automatically counts down from 39 (the number of floors) as long as no text is in the adjacent cell (see imageSchedule of work ex.jpg). I would like it to repeat this autonumbering system after it reaches the 3rd floor. i've gotten it to go back to 39 after 3 comes up, but it still subtracts from the original 39, which i assume is caused by the MIN function I used, but I do not know an alternate function to use.

    any adjustments to my formula or new simpler ones are much appreciated

  2. #2
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: autonumber (from high to low) and restart after reaching certain number

    Hi!

    And option could be:
    [B3] : =IF(ISTEXT(C3),"",MOD(LOOKUP(9E307,B$2:B2)-4,37)+3)

    Blessings!

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: autonumber (from high to low) and restart after reaching certain number

    Type B=39
    ARRAY formula (pl see note below) in B2, then dra down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    01-10-2018
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2

    Re: autonumber (from high to low) and restart after reaching certain number

    This worked perfectly. Thanks!

+ 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] Autonumber to skip blank lines and then and restart number series
    By mcranda in forum Excel General
    Replies: 6
    Last Post: 09-04-2015, 05:12 PM
  2. [SOLVED] Generate Number from Dept+Date+autonumber in userform
    By onestar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-07-2014, 04:30 AM
  3. [SOLVED] Add an Autonumber to a column, for use as a sequential Purchase Order Number
    By oracle741 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-24-2013, 04:44 PM
  4. [SOLVED] Autonumber Until Certain Value then Restart
    By sperryxx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-05-2013, 04:45 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. Autonumber cell based on max number in column
    By zibbyg11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2013, 06:43 PM
  7. restart counting if a number is under 5
    By scapiro in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-28-2013, 12:25 AM

Tags for this Thread

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