+ Reply to Thread
Results 1 to 3 of 3

Insert row numbers into column A, reflect correct row numbers when I add or delete rows

  1. #1
    Registered User
    Join Date
    07-27-2015
    Location
    San Diego, CA
    MS-Off Ver
    Windows 7 Professional
    Posts
    20

    Insert row numbers into column A, reflect correct row numbers when I add or delete rows

    I want to insert row numbers into column A of a worksheet. I want the column to reflect the correct number of the row, even when I add or delete rows. The Step Value is 10 and every third cell gets numbered. This sheet is a work order and cells are deleted that aren't being used. So, if cells A4-A6 are deleted and so forth accordingly, then I want to keep the same numbering sequencing. The adjacent column B has work instructions, or sometimes says "DELETE OPERATION" if those instructions aren't required. If column B says "DELETE OPERATION" I will delete the required cells. If I do that I want the rows in column A to reflect the correct number of the row.

    Example:

    10 DELETE OPERATION


    20 DELETE OPERATION


    30 Assemble Bellows to Weld End
    Weld per Drawing

    40 Visually Inspect Welds


    50 Air Hose Test per SWI-P-02


    Formulas I have tried but do not work: =IF(TRIM(B1)<>"",COUNTA($B$1:B1)&".","") and =IF(TRIM(B1)<>"",ROW()&".","")

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Insert row numbers into column A, reflect correct row numbers when I add or delete row

    To number the rows just

    =ROW()

    to number every third row
    =IF(MOD(ROW(),3)=0, ROW(),"")

    If neither of these are correct then I am not sure what you are asking for?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    07-27-2015
    Location
    San Diego, CA
    MS-Off Ver
    Windows 7 Professional
    Posts
    20

    Re: Insert row numbers into column A, reflect correct row numbers when I add or delete row

    I found a formula that works perfect:

    in A1 enter
    =IF(B1<>"",10,"")

    in A2 enter
    =IF(B2<>"",MAX($A$1:OFFSET(A2,-1,0))+10,"")
    and copy this formula down for as many rows as required. Now every time
    someone enters data in column B column A shows a ID value.

+ 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. Insert/Delete rows based on the same numbers of rows with data in other sheet
    By Blokeman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2015, 08:28 PM
  2. Can you subtract numbers then reflect in a total Amt.
    By Un excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2015, 12:24 AM
  3. Replies: 8
    Last Post: 06-28-2014, 03:26 PM
  4. Replies: 4
    Last Post: 08-03-2013, 04:08 AM
  5. Replies: 12
    Last Post: 04-24-2011, 05:45 PM
  6. [SOLVED] delete numbers and replace with numbers from adjacent column
    By troysie in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-13-2010, 05:31 AM
  7. Deleting rows matching numbers from numbers in column
    By cyki in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-11-2008, 09:16 AM

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