Excel Formulas: Cell Value Auto-Incrementing Issue

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

lightnb

Thread Starter
Joined
Jun 16, 2002
Messages
786
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?
 
Joined
Jul 1, 2005
Messages
8,546
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",
))))
 

clhcpa

Banned
Joined
Feb 15, 2005
Messages
1,459
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.
 
Joined
Oct 26, 2005
Messages
354
And continue hitting F4 to cycle through all four possible absolute/relative combinations.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top