What relative row I'm in?

[fedude]

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

 

[Pete_UK]

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

 

[fedude]

BINGO. Thanks!

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

 

[Pete_UK]

You're welcome - glad it worked for you.

Pete

BINGO.   Thanks!






- Show quoted text -