+ Reply to Thread
Results 1 to 12 of 12

Help with autonumber while skipping rows.

  1. #1
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    Help with autonumber while skipping rows.

    Hello, so here is my problem, I am trying to get excel to autocount starting in a7 and ending at a600, skipping 3 rows in between, and only gng from 1-10 then repeating and inserting a page break after the 10th line i have put an example in. thank you for your help


    SAMPLE1.xlsx

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Help with autonumber while skipping rows.

    insert 4 rows above your data, and the following formula should work:
    =IF(B5="","",IF(A1=10,1,A1+1))
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Help with autonumber while skipping rows.

    I think that is on the right track, but my list is really much longer and the clients names dont start until a7 so it looks like this

    a b
    7 client name
    8
    9
    10
    11 client name

    and it goes from a7-a495

  4. #4
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Help with autonumber while skipping rows.

    Assuming cell A3 is empty, you should be able to use this formula in A7 and drag down:
    =IF(B7="","",IF(A3=10,1,A3+1))

  5. #5
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Help with autonumber while skipping rows.

    let me put a better example up really quick

  6. #6
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Help with autonumber while skipping rows.

    here is a better example Book1.xlsx

  7. #7
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Help with autonumber while skipping rows.

    The formula I gave appears to work, so was the issue that you wanted the page breaks to automatically insert?
    Would the attached file work?
    The page breaks are all defaults, so If you change the width of a row, everything will be thrown off, but right now the file is exactly what you are looking for, I believe.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-26-2012
    Location
    Kitchener, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    82

    Re: Help with autonumber while skipping rows.

    This a one using a macro
    Attached Files Attached Files
    aelgadi

    > Click Star if I helped. Thanks

  9. #9
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Help with autonumber while skipping rows.

    the macro works great thank you both soooo much, now sometimes we have to take clients from previous weeks, and copy and paste them into the current sheet. will that throw everything off? or, will it automatically adjust? also what is the easiest way to copy that macro into my spreadsheet?

  10. #10
    Registered User
    Join Date
    11-26-2012
    Location
    Kitchener, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    82

    Re: Help with autonumber while skipping rows.

    One way you copy and past the code from a workbook to another.

    Other way you can export the module that containing the macro into a folder then you can import it every time you need it to the new workbook.

    Another way: use a blank workbook containing that macro as template.
    Last edited by aelgadi; 12-21-2012 at 05:01 PM. Reason: Spelling

  11. #11
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Help with autonumber while skipping rows.

    can u maybe copy the macro and paste it on here for me?

  12. #12
    Registered User
    Join Date
    11-26-2012
    Location
    Kitchener, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    82

    Re: Help with autonumber while skipping rows.

    Quote Originally Posted by trisham View Post
    can u maybe copy the macro and paste it on here for me?
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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