Tech Support Guy banner
Status
Not open for further replies.
1 - 5 of 5 Posts

·
Registered
Joined
·
796 Posts
Discussion Starter · #1 ·
I'm having an issue with an excel formula.

I have:

Code:
=
IF(D7="DSOA",'Common Milages'!D8,
IF(D7="SRHS", 'Common Milages'!D6,
IF(D7="ECT", 'Common Milages'!D7,
IF(D7="-","0",
))))
This code works fine on a single cell. The problem occurs when I try to duplicate it, either by using 'fill series' or copy and paste.

Excel automatically increments the cell numbers, so that D7 is D8, and so on as it goes down. This is a good thing.

But the formula also references a second sheet 'Common Milages'. The cell values in the formula that references this sheet always needs to stay the same. In other words 'Common Milages'!D6 should always be 'Common Milages'!D6, no matter where the formula is located.

Is there a way to exclude certain cell numbers from auto-incrementing?
 

·
Registered
Joined
·
8,565 Posts
Use dollar signs to create absolute references for the other sheet , i.e.:

IF(D7="DSOA",'Common Milages'!$D$8,
IF(D7="SRHS", 'Common Milages'!$D$6,
IF(D7="ECT", 'Common Milages'!$D$7,
IF(D7="-","0",
))))
 

·
Banned
Joined
·
1,461 Posts
lightnb said:
Excellent! Thank you. :)
FYI - a quick way to do this in the future is to click your curser in the middle of the cell address you want to be permanent, and hit F4 -- it will automatically place the dollar signs where they need to be.
 

·
Registered
Joined
·
358 Posts
And continue hitting F4 to cycle through all four possible absolute/relative combinations.
 
1 - 5 of 5 Posts
Status
Not open for further replies.
Top