Solved

Maintaining References in Excel Formulas

Posted on 2012-03-18
1
352 Views
Last Modified: 2012-03-18
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
0
Comment
1 Comment
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 0 total points
ID: 37734909
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question