Dragging/Copying Lookup Formulas

[Paul Sheppard]

I have the following lookup formula in cell A5 :-

=HLOOKUP($A$3,Apr!$G$4:$AQ$300,37,0)

When I drag the formula down, the row index number 37 does not change,
so I have to manually change it

Is there a way of dragging the formula down so the row index number
changes automatically

This would save a lot of time as I have 300 rows of data

 

[Guest]

use the row() function and add 32,as you copy down the row number wil change
with the row and you r modifier will take you to the row you want

 

[Guest]

I have the following lookup formula in cell A5 :-
=HLOOKUP($A$3,Apr!$G$4:$AQ$300,37,0)
When I drag the formula down, the row index number 37 does not change,
so I have to manually change it
Is there a way of dragging the formula down so the row index number
changes automatically
This would save a lot of time as I have 300 rows of data

One way, assuming you want it to increment sequentially: 37, 38, 39 ...
as you copy down from A5.

In A5's formula, just replace the "37" with this incrementer: ROW()+32

Note that the incrementer is row sensitive, so if you subsequently insert
rows above A5, you have to amend the incrementer in A5 to suit (adjust the
"32"), and then refill down

---

 

[Tibbs]

The way I do it is to have a line of numbers (37,38,39,40,41, etc etc)
and reference off that, so your formula would look like:

=HLOOKUP($A$3,Apr!$G$4:$AQ$300,D3,0)

You just need to put the dollar signs in the appropriate place. I can
never remember which way round they go!

HTH,

Chris

 

[Guest]

... you have to amend the incrementer in A5 to suit

The "A5" referred to will now be eg: "A6" or "A7"
depending on how many rows are inserted above
(i.e. the new "starting cell" for the col of formulas)

---

 

[Paul Sheppard]

Thanks Guys

Sorted