How do I copy formulas but using the same range each time I copy

I want to fill/copy a formula within a specific range but each time I try to
fill or sopy it changes the range it is supposed to looking in so I am
getting errors.

For example,

Row 1 - =LOOKUP(J2,Sheet1!A2:A153,Sheet1!B2:B153)
Row 2 - =LOOKUP(J3,Sheet1!A2:A153,Sheet1!B2:B153)

But, if I just copy or fill I am getting the second row showing up as:
=LOOKUP(J3,Sheet1!A3:A154,Sheet1!B3:B154) instead of as Row 2 above. I do
not want to type the formula 500 times.

Is there a solution?

For the cell references that you do not want to change as you copy it down
put a $ before the Row and column refernce of the cell. for example:

=LOOKUP(J2,Sheet1!$A$2:$A$153,Sheet1!$B$2:$B$153)

When you copy this down the J2 part will progress but the other cells will
not progress.


"Laffin" wrote:

> I want to fill/copy a formula within a specific range but each time I try to
> fill or sopy it changes the range it is supposed to looking in so I am
> getting errors.
>
> For example,
>
> Row 1 - =LOOKUP(J2,Sheet1!A2:A153,Sheet1!B2:B153)
> Row 2 - =LOOKUP(J3,Sheet1!A2:A153,Sheet1!B2:B153)
>
> But, if I just copy or fill I am getting the second row showing up as:
> =LOOKUP(J3,Sheet1!A3:A154,Sheet1!B3:B154) instead of as Row 2 above. I do
> not want to type the formula 500 times.
>
> Is there a solution?

Just going off on a tangent, I want my formula to stay the same when dragged from left to right, so the $ works fine, but I want the Sheet reference to change.

The sheets are Apr06, May06, Jun06 etc.. and are in consecutive order, is there anyway to accomplish this??

Thanks