1. ## Sequential Number Formula

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. It's kind of the same thing as using the Row function:

=CELL("row",A1)

Jesse

3. 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. 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. Bob - That formula doesn't work for me...did you post it correctly?

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. 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. @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. 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. 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 Last