Sequential numbers in a formula?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 18 Jul 2007 20:05 GMT

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

Reply to this Message

Rick Rothstein (MVP - VB) - 18 Jul 2007 20:16 GMT

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

>I have a few formulas (OFFSET & MATCH) which are the same (with relative
>references chanfing accordingly) and occupy cells B2:B500. In each formula
[quoted text clipped - 12 lines]
>
> Any help greatly appreciated.....Regards, Jason

Reply to this Message

Jay - 22 Jul 2007 11:52 GMT

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

> ROWS($1:X)
>
[quoted text clipped - 19 lines]
>>
>> Any help greatly appreciated.....Regards, Jason

Reply to this Message