Sequential numbers in a formula?

[Jay]

I have a few formulas (OFFSET & MATCH) which are the same (with relative
references chanfing accordingly) and occupy cells B2:B500. In each
formula one of the function arguments refers to a single cell in the
adjacent column i.e A1:A500 which contains the numbers 1 to 500 (and the
column is hidden as the numbers are needed only in the formulas and not
to be displayed).

The numbers are used to give the relative row number in the ranges used
by OFFSET. So, the first formula uses 1 (via B2) and the second formula
uses 2 (via B3) and so on.

My question is: Can I somehow refer to a sequentially increasing number
(staring at 1 in increments of 1, until 500) in the formula without the
need to refer to a cell which contains that number?

Any help greatly appreciated.....Regards, Jason

 

[Rick Rothstein \(MVP - VB\)]

ROWS($1:X)

where X is the number you want to start counting from, will produce a
sequential series of numbers when dragged down.

Rick

 

[Jay]

That's great Rick, MANY thanks. I wasn't even aware of the ROWS (or
COLUMNS) functions so many thanks for teaching me *2* new functions.

I use ROW and COLUMN all the time, particularly in array formula & can
see similar applications for ROWS and COLUMNS.

Rgds.....Jason