View Full Version : Sequential Number Formula

eferrero

2011-03-22, 09:26 AM

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?

Jesse

2011-03-22, 09:41 AM

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

=CELL("row",A1)

Jesse

JeffreyWeir

2011-03-22, 10:26 AM

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))

Bob Umlas

2011-03-22, 06:56 PM

Define Next as

=MAX(!A$1:A1)+1

then simply enter =Next

Don't enter it in row 1-- creates circ error --

Bob Umlas

JeffreyWeir

2011-03-22, 11:24 PM

Bob - That formula doesn't work for me...did you post it correctly?

eferrero

2011-03-23, 02:38 AM

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

Bob Umlas

2011-03-23, 04:43 AM

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.

Jan Karel Pieterse

2011-03-23, 08:25 AM

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

Bob Umlas

2011-03-23, 01:46 PM

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)!

music43

2011-03-23, 03:22 PM

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

JeffreyWeir

2011-03-26, 02:06 PM

Oli: One problem with =ROW($1:$10) is that if someone inserts a new row at row 1 then your formula will return 2,3,4,5 (i.e. the '1' effectively drops out)

One problem with ROW(INDIRECT("1:"&something)) is that INDIRECT is volitile, meaning it evaluates any time anything on the sheet changes...which can really slow down a worksheet if you're using it to produce a big array of formulas.

=ROW(10000:10009)-ROW(10999:10999) will return 1 through to 10, and is robust to someone inserting more rows above. However, in the unlikely event that someone inserts a row somewhere between 10000:10009 then it will return 1 through 11. Might not be a problem. Could always set the scrolling area on your worksheet.

JeffreyWeir

2011-03-26, 02:14 PM

Whoops, that formula should have read =ROW(10000:10009)-ROW(9999:9999)

Also, apologies to Oli for pointing out that indirect is volatile...I see you've already pointed that out.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.