# 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
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?

#### bomb #21

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",
))))

#### lightnb

Thread Starter
Excellent! Thank you.

#### clhcpa

Banned
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.

#### exegete

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.

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.

over 807,865 other people just like you!