Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Sequential Number Formula

  1. #1

    Sequential Number Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Ok, I'll start with a question;

    Does anyone have a nice way to generate a sequence of numbers (1,2,3,4..) with a formula?

    I need this to be used in a Named Range.

    So I don't want to use a helper column and enter =A1+1. Neither do I want to use the =ROW() formula with a cell reference.

    The best I've come up with so far is to define a name,
    Increment =ROW(OFFSET(INDIRECT("A1"),0,0,1000000,1))

    That works, but looks a little clumsy to me. aNYONE HAVE A BETTER WAY OF DOING THIS?

  2. #2
    It's kind of the same thing as using the Row function:

    =CELL("row",A1)

    Jesse

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    Hi eferrero. Any reason why you don't want to use ROW()?
    Here's a couple of suggestions from the Excel Hero forum (a closed forum to people doing Daniel's Excel Hero course). Both are non-volatile (meaning they won't slow your spreadsheet down) and are not effected by insert delete of rows anywhere :
    1. From Sam
    Define a named range as follows:
    Num = Evaluate("1:10")
    In any cell type = row(Num)


    2. From Ari Hamalainen
    =ROW(INDEX(A:A,1):INDEX($A:$A,10))

  4. #4
    Seeker Bob Umlas's Avatar
    Join Date
    Mar 2011
    Location
    New York
    Posts
    10
    Articles
    0
    Define Next as
    =MAX(!A$1:A1)+1
    then simply enter =Next
    Don't enter it in row 1-- creates circ error --

    Bob Umlas

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    Bob - That formula doesn't work for me...did you post it correctly?

  6. #6
    @ Bob

    Very clever - you negleted to mention that you need to select cell A2 and then define the name exactly as written to make this work.
    Its a shame that it will give a circular error if entered in row 1. Otherwise it is pretty elegant.

    @ Jeffey

    Thanks for your formulas, will eveluate for performance.

    @ Jesse

    Whilst that formula will work in a cell, like ROW(A1), it can't be used in a Named Range because the values returned depend on the selected cell at the time that the Named Range is being defined. i.e. if I select cell A1 and define a Named Range Incr =CELL("row",!A1), then using =Incr in row 1 will start a series at 1, but using =Incr in row 21 will start the series at 21 - not what I want, the series needs to start at 1 no matter what cell I enter it in.

  7. #7
    Seeker Bob Umlas's Avatar
    Join Date
    Mar 2011
    Location
    New York
    Posts
    10
    Articles
    0
    Ed is right -- I forgot to mention that you need to start in row 2 when you define it as I said. Then it works in all places as advertised except row 1.

  8. #8
    @Bob

    There is a catch.

    Enter your formula in -say- cells A2 to A10 on Sheet1.
    Select sheet2
    Enter the formula in cells A2 and A3 only
    Open the VBE and type:
    Application.CalculateFull
    in the immediate window and hit enter.

    See what has happened to Sheet1...

  9. #9
    Seeker Bob Umlas's Avatar
    Join Date
    Mar 2011
    Location
    New York
    Posts
    10
    Articles
    0
    Jan is right (and I knew that! -- was trying to keep it simple). To avoid that gotcha, on sheet1 when in cell A2, define "next" as =MAX(Sheet1!A$1:A1)+1
    Maybe best to define it as a local name and then you can only use it in sheet1. But if you don't have the concern about using it on multiple sheets, then the original easy deefinition works on any sheet (but only ONE sheet)!

  10. #10
    Neophyte music43's Avatar
    Join Date
    Mar 2011
    Location
    Southampton, UK
    Posts
    2
    Articles
    0
    Hi

    These are themes on what has already come up but you could try

    =ROW(INDIRECT("1:"&something)) where 'something' is a NAMED FORMULA giving you the highest value in the array say 10, 20 or whatever

    the above is volatile

    or you could use

    =ROW($1:$10)

    Oli

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •