Maintaining References in Excel Formulas

Scott McDaniel (Former Microsoft Access MVP - EE MVE )
Scott McDaniel (Former Microsoft Access MVP - EE MVE ) used Ask the Experts™
on
In my Excel worksheet, I have a formula that looks like this:

=IF(P25=1, LOOKUP(B6,AH4:AH7,AJ4:AJ7),LOOKUP(P25,AH4:AH7,AJ4:AJ7))

I've copied it to about 8 new rows, and changed the relevant references to point to the appropriate rows, so I now have this:

=IF(P26=1, LOOKUP(B6,AH4:AH7,AJ4:AJ7),LOOKUP(P25,AH4:AH7,AJ4:AJ7))
=IF(P27=1, LOOKUP(B6,AH4:AH7,AJ4:AJ7),LOOKUP(P27,AH4:AH7,AJ4:AJ7))
=IF(P28=1, LOOKUP(B6,AH4:AH7,AJ4:AJ7),LOOKUP(P28,AH4:AH7,AJ4:AJ7))
=IF(P29=1, LOOKUP(B6,AH4:AH7,AJ4:AJ7),LOOKUP(P29,AH4:AH7,AJ4:AJ7))
=IF(P30=1, LOOKUP(B6,AH4:AH7,AJ4:AJ7),LOOKUP(P30,AH4:AH7,AJ4:AJ7))

Those forumlas work, and leave the "hard" values of B6, and the Lookup criteria where they need to be. When I select and drag to copy, however, Excel changes the relative values to match the new rows, so I end up with this:

=IF(P32=1, LOOKUP(B13,AH11:AH14,AJ11:AJ14),LOOKUP(P32,AH11:AH14,AJ11:AJ14))
=IF(P33=1, LOOKUP(B13,AH11:AH14,AJ11:AJ14),LOOKUP(P33,AH11:AH14,AJ11:AJ14))
=IF(P34=1, LOOKUP(B13,AH11:AH14,AJ11:AJ14),LOOKUP(P34,AH11:AH14,AJ11:AJ14))

and so on - the drag/copy doesn't maintain my lookup references.

Is there some way to tell Excel to copy my formulas down differently, to maintain my lookup values better? I can do a Find/Replace, but it's a painful process, and for an upcoming project I'm going to have to build quite a few formulas that would be similar
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
Hmmm ... guess I should RTFM before posting.

You can maintain "absolute references" by using the $ in front of the reference:

$A1 maintains the column, but changes the Row
A$1 maintains the row, but changes the Column
$A$1 maintains both column and row

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial