+ Reply to Thread
Results 1 to 5 of 5

Insert number sequence in cells

  1. #1
    Registered User
    Join Date
    06-15-2016
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    9

    Insert number sequence in cells

    Hello,

    I'm trying to create a tournamnet schedule with a different value in amount of lanes.
    I would like to create a Private Sub for a worksheet which creates a number sequence based on a cell value from another worksheet.

    If value in cell D2 on Sheet "Teams" is 4 , this will create a number sequence on sheet "12" which will be 1,2,3,4,1,2,3,4,1,2.... in cells H1-M1 and continues in H4-M4 , H7-M7 and keeps on running to H28-M28
    If the value is 3 the sequence shall be 1,2,3,1,2,3,1,2.... in the same cells as above.
    I hope the picture can shed some light over my problem.
    Attached Images Attached Images

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Insert number sequence in cells

    One way:


    H1: =1
    I1: =IF(MOD(H1,Teams!$D$2)=0,1,H1+1)

    Copy I1 across through N1,

    Now copy I1:M1 down and paste at I4:M4

    Change the H4 formula to:
    =IF(MOD(M1,Teams!$D$2)=0,1,M1+1)

    Now you can copy H4:M4 down as many times as you wish.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-15-2016
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    9

    Re: Insert number sequence in cells

    This worked perfect! Thanks!

    Now to my other dilemma which could be little bit harder. I will now need to be able to fill in Start Lane and End Lane numbers in cell F2 and G2 on sheet "Teams"
    So if I type start lane 3 and end lane 6 it should be producing the same number sequence as in the first Post, but with the difference on starting number in cell H1

    Example JPG shows the result I want.
    Attached Images Attached Images

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Insert number sequence in cells

    I'm thinking:

    H1: =Teams!$F$2
    I1: =IF(MOD(H1, Teams!$G$2)=0, Teams!$F$2, H1+1)

    Copy I1 across through M1,

    Now copy I1:M1 down and paste at I4:M4

    Change the H4 formula to:
    =IF(MOD(M1, Teams!$G$2)=0, Teams!$F$2, M1+1)

    Now you can copy H4:M4 down as many times as you wish.

  5. #5
    Registered User
    Join Date
    06-15-2016
    Location
    Sweden
    MS-Off Ver
    2010
    Posts
    9

    Re: Insert number sequence in cells

    Worked like a charm! Great

    Now to my last conundrum. I've transposed the Lanes too rows instead and now I would want to get the value in Rounds to be able to pop up automatically.
    So for each time Lane 3 is located in column "B" a new round should show in column "A".
    See example in JPG.
    Attached Images Attached Images

+ 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] Insert New Row & Next Number in Sequence (Revised)
    By Gazsim in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2019, 12:45 PM
  2. insert sequence number with uneven pattern?
    By cat3appr in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 05-04-2019, 07:32 PM
  3. Need to break a text/number sequence and insert :
    By FDibbins in forum Excel General
    Replies: 43
    Last Post: 02-16-2018, 04:46 AM
  4. How to insert rows in sheet1 based on sequence number.
    By aleanboy in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 10-16-2014, 12:35 PM
  5. Insert a blank row if number sequence is not complete.
    By califorlina in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-11-2013, 08:59 AM
  6. [SOLVED] Insert missing rows into repeating number sequence
    By budgie300 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-12-2012, 06:17 PM
  7. Sequence number cells >0
    By b.johnson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2007, 03:42 PM

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