Results 1 to 4 of 4

Thread: Auto-filling a Series

  1. #1

    Question Auto-filling a Series



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,


    I am trying to automatically get the following series filled in but have failed thus far:


    1,2,3,4,5,6,7,11,12,13,14,15,16,17,21,22,23....... (after 7, it skips 3 digits)


    Similarly,


    Excel has the "Fill Weekdays" option. However, how do I also remove Friday along with Saturday and Sunday from the dates of a year?


    Any help would be much appreciated!


    Thanks!

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    How far do you need the series to extend? You can achieve the 'skipping dates' thing by using custom lists. See http://office.microsoft.com/en-nz/ex...010222142.aspx

    But i don't think it will work for your number sequence question. Although you can use a formula.
    Put this in row 1 and drag down:
    =INT(ROW()/7-1/7)*10+IF(MOD(ROW(),7)=0,7,MOD(ROW(),7))
    Then you could copy and paste special as values.

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    You could achieve this with macros.

    For the numbers something like this
    Code:
    Sub Skip890()
        Dim rng As Range
        Dim cel As Range
        Dim cNum As Long
        
        'where ever you are putting the numbers
        Set rng = Range("C3:C38")
        
        'put the number you want to start at in cell A2
        cNum = Range("A2").Value
            
        For Each cel In rng
            cel.Value = cNum
            cNum = cNum + 1
            If Right(cNum, 1) = 8 Then
                cNum = cNum + 3
            End If
        Next cel
        
    End Sub
    maybe like this for the days
    Code:
    Sub SkipFriToSun()
        Dim rng As Range
        Dim cel As Range
        Dim cDa As Date
        
        'where ever you are putting the days
        Set rng = Range("D3:D38")
        
        'put the starting date in cell B2
        cDa = Range("B2").Value
        
        For Each cel In rng
            'if the day is Friday add 3 days to get Monday
            If Format(cDa, "ddd") = "Fri" Then
                cDa = cDa + 3
            End If
            
            'format this how ever you want it displayed
            cel.Value = Format(cDa, "Long Date")
            'get ready for next day
            cDa = cDa + 1
        Next cel
        
    End Sub
    I put this code into the sheets module, hope it's of some use, just an idea.
    Last edited by NoS; 2013-08-06 at 01:38 AM.

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Here's a formula that handles any series of repeating numbers on the far right from 1 to 9. Just replace X with the number
    =INT((ROW()-1)/x)*10+MOD(ROW(),-x)+x

Tags for this Thread

Posting Permissions

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