+ Reply to Thread
Results 1 to 4 of 4

Automatic serial number

  1. #1
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Automatic serial number

    I have a sheet with Sl. No. (Col. A) Description(Col. B) and Rate(Col. C). I have included a “flag” field where I will enter “m” for main item and “s” for sub-item. The Sl. No. field is automatically populated based on entry in flag field. (see the attached image)

    I have used the formula for Col. A

    =IF(D3="s", INDEX($A:$A, ROW()-1)+0.01, 1+ROUNDDOWN(INDEX($A:$A, ROW()-1), 0))

    The issue I am facing is that if there is a blank cell above a certain Sl. No., the formula the Sl. No. For the next item is not automatically returned. e.g. If A5 = 1.02 and A6 is blank, then A7 does not automatically populate the serial number based on the flag “s” or “m” in the flag field.
    How is the formula to be tweaked to achieve that the blank cells above a certain Sl. No. is ignored and the first instance of the number above Sl. No. field is considered and incremented as per the flag.


    Attachment 559646
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,624

    Re: Automatic serial number

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to Quick Post button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,921

    Re: Automatic serial number

    Try this in A3:

    =IF(D3="","",IF(D3="m",COUNTIF($D$3:D3,"m"),MAX($A$2:A2)+0.01))
    Last edited by Phuocam; 02-05-2018 at 01:08 AM.

  4. #4
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: Automatic serial number

    Thanks,
    This works as desired.

+ 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] count number in serial and break up serial count
    By gondal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2016, 08:44 AM
  2. Automatic Serial Numbering in Excel
    By mackan7695 in forum Excel General
    Replies: 2
    Last Post: 01-25-2016, 05:35 AM
  3. Automatic Ruining Serial Number Against Multiple Reference in Excel
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-15-2015, 04:26 AM
  4. Overwrite the advanced filtered data serial number column with unique number
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-15-2012, 12:38 AM
  5. Finding a serial number in a list of serial numbers
    By zocoloco in forum Excel General
    Replies: 2
    Last Post: 02-04-2009, 05:20 AM
  6. Generating Automatic Serial Number in Excel
    By vajeehu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-15-2007, 04:11 PM
  7. [SOLVED] automatic new serial number for each new sheet within one file
    By ahmed in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-26-2005, 10:06 AM

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