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

[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

[paul]

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
--
paul
remove nospam for email addy!



"Paul Sheppard" wrote:

>
> 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
>
>
> --
> Paul Sheppard
>
>
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=530894
>
>

[Max]

"Paul Sheppard" wrote:
> 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

> ... 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Paul Sheppard]

Thanks Guys

Sorted