+ Reply to Thread
Results 1 to 9 of 9

auto numbering rows that skip blanks and update accurately when inserting a copied row

  1. #1
    Registered User
    Join Date
    06-11-2014
    Posts
    6

    auto numbering rows that skip blanks and update accurately when inserting a copied row

    I am trying to make a formula that automatically correctly numbers rows, but skips certain blank rows. I am often copying and inserting rows, and want the formula to be correct then as well. I am thinking some combination of countif and offset may work. I essentially want the number to count all the rows above itself to a fixed cell ( in this case, $A$5) and add one, without counting rows=0. Copying and pasting =OFFSET(A6,-1,0)+1 works, where A5=1, but resets to one after a blank row. =COUNTIF($A$5:A6,">0")+1 works for the blank/zero values but does not update correctly when I insert a copied row, and A5=1 and A6=2. Any ideas?

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: auto numbering rows that skip blanks and update accurately when inserting a copied row

    In A6 try:

    Please Login or Register  to view this content.
    Copy down.

    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Registered User
    Join Date
    06-11-2014
    Posts
    6

    Re: auto numbering rows that skip blanks and update accurately when inserting a copied row

    Unfortunately, I still run into the problem that when I copy a row and insert it, the row below it will not update automatically. I think I need some kind of formula that is self referential without breaking, which is why I was considering OFFSET.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: auto numbering rows that skip blanks and update accurately when inserting a copied row

    After you insert a row, you need to copy the formula to the cells below.

    e.g. copy cell B2 to B3:B25

    Then the solution of ConneXionLost will work for you.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    06-11-2014
    Posts
    6

    Re: auto numbering rows that skip blanks and update accurately when inserting a copied row

    Although that is true, lots of solutions work if I recopy the formulas all the time. I want something where that isn't necessary. I am constantly copying and inserting, and a few lines are blank so I would have to go through and delete these formulas.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: auto numbering rows that skip blanks and update accurately when inserting a copied row

    Please Login or Register  to view this content.
    Why is that?

  7. #7
    Registered User
    Join Date
    06-11-2014
    Posts
    6

    Re: auto numbering rows that skip blanks and update accurately when inserting a copied row

    I have a variety of formulas in each row that need be in any new row. Often many of the comments and values are similar as well so doing it this way saves time. The only formula that breaks using this method is the row counting formula, which is the least important. Inserting a row and using "paste special" and picking "formulas only" adds too many steps and would break the counting formula anyway.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: auto numbering rows that skip blanks and update accurately when inserting a copied row

    Maybe it is possible to change the format of your file, so you don't have to copy and paste.

  9. #9
    Registered User
    Join Date
    06-11-2014
    Posts
    6

    Re: auto numbering rows that skip blanks and update accurately when inserting a copied row

    That wouldn't solve the problem of inserting line items that are extremely similar, which save me a lot of steps. It still seems like something could be done with OFFSET or some other function that can be self referential in terms of position.

+ 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. Replies: 7
    Last Post: 01-02-2013, 09:49 PM
  2. Auto numbering - Inserting rows without changing numbers
    By Kayaness in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2012, 03:43 AM
  3. Auto Inserting Copied Cells
    By whatsmyname in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-26-2010, 10:50 AM
  4. Auto update my numbering system in excel
    By gdallas in forum Excel General
    Replies: 6
    Last Post: 11-27-2009, 09:59 AM
  5. drag formula and skip blanks rows
    By patsureway in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-14-2008, 09:41 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