Results 1 to 8 of 8

automatically number every other row

Threaded View

  1. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: automatically number every other row

    Welcome to the forum Vitalite.

    It's great that you want to learn more, but please also take a moment to read the Forum Rules, particularly Rule 2: Do not post a question in the thread of another member -- start your own thread. If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
    You noted yourself that this is an old thread. Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    Don't worry this once. I'll answer you to save Ron the time (he is still on here - he's one of the moderators).

    Formula: copy to clipboard
    =IF(ISNUMBER(OFFSET(A2,-1,0)),"",MAX($A$1:OFFSET(A2,-1,0))+1)

    OFFSET looks above/right/below/left of the cell address specified, depending on what's input. In this case, OFFSET(A2,-1,0) means 'look one row up (that's the -1) in the same column (that's the zero). So from A2, it's looking at A1. As it's dragged down, it keeps looking one cell up, so from A10 it'll look at A9, for example.
    ISNUMBER checks if the cell being looked at is a number.
    IF checks if the first statement is true and does one action if it is, another if it isn't: IF(this, do this, otherwise do this). So IF(ISNUMBER(OFFSET(A2,-1,0)),"", will return blank ("") if the cell above (A1 in this case) is a number. If it's not a number, the IF statement goes on to the 'otherwise do this' part.
    MAX finds the highest number in a specified range. The range in this case is from $A$1 (the $ signs hard-code this to A1, so it won't change as the formula is dragged down) to the Offset cell, as discussed above. So, for example, once this is dragged down to A10, this becomes MAX(A1:A9).
    The +1 at the end of the formula is probably self-explanatory: it adds one to the previous highest number (from the MAX).

    So, in plain English (I hope), the formula is:
    If the cell above this one is a number, make this cell blank, otherwise add one to the highest number above this cell.

    Hope that helps.
    Last edited by Aardigspook; 05-22-2017 at 02:51 PM.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

Thread Information

Users Browsing this Thread

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

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