I have a large array (startingHoles). Next to te array I have a column
containing a formula This formula needs to know the relative row of the
array it's next to for use in the formula.

Here is the formula:

=HLOOKUP(weekNumber,startingHoles,5,FALSE)

The "5" in the formula above is the relative row it's next to. I'd rather
not have to edit every cell in this column to increment this number.

Looking for a better way.....

If you want the 5 to become 6, 7, 8 etc as you copy the formula
across, then change it to:

COLUMN(E1)

This will become COLUMN(F1), COLUMN(G1), COLUMN(H1) etc, which will
evaluate to 6, 7, 8 etc as it is copied across.

Hope this helps.

Pete

On Mar 10, 10:56*am, fedude wrote:
I have a large array (startingHoles). * Next to te array I have a column
containing a formula *This formula needs to know the relative row of the
array it's next to for use in the formula.

Here is the formula:

=HLOOKUP(weekNumber,startingHoles,5,FALSE)

The "5" in the formula above is the relative row it's next to. *I'd rather
not have to edit every cell in this column to increment this number. *

Looking for a better way.....

BINGO. Thanks!

"Pete_UK" wrote:

If you want the 5 to become 6, 7, 8 etc as you copy the formula
across, then change it to:

COLUMN(E1)

This will become COLUMN(F1), COLUMN(G1), COLUMN(H1) etc, which will
evaluate to 6, 7, 8 etc as it is copied across.

Hope this helps.

Pete

On Mar 10, 10:56 am, fedude wrote:
I have a large array (startingHoles). Next to te array I have a column
containing a formula This formula needs to know the relative row of the
array it's next to for use in the formula.

Here is the formula:

=HLOOKUP(weekNumber,startingHoles,5,FALSE)

The "5" in the formula above is the relative row it's next to. I'd rather
not have to edit every cell in this column to increment this number.

Looking for a better way.....

You're welcome - glad it worked for you.

Pete

On Mar 10, 11:38*am, fedude wrote:
BINGO. * Thanks!



"Pete_UK" wrote:
If you want the 5 to become 6, 7, 8 etc as you copy the formula
across, then change it to:

COLUMN(E1)

This will become COLUMN(F1), COLUMN(G1), COLUMN(H1) etc, which will
evaluate to 6, 7, 8 etc as it is copied across.

Hope this helps.

Pete

On Mar 10, 10:56 am, fedude wrote:
I have a large array (startingHoles). * Next to te array I have a column
containing a formula *This formula needs to know the relative row of the
array it's next to for use in the formula.

Here is the formula:

=HLOOKUP(weekNumber,startingHoles,5,FALSE)

The "5" in the formula above is the relative row it's next to. *I'd rather
not have to edit every cell in this column to increment this number. *

Looking for a better way.....- Hide quoted text -

- Show quoted text -