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


--
neilcarden
------------------------------------------------------------------------
neilcarden's Profile: http://www.excelforum.com/member.php...o&userid=35623
View this thread: http://www.excelforum.com/showthread...hreadid=554594