Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using the following Excel formula to get the relative row number for a calculation.

=$A9*(C3)

Cell C3 contains a constant value, so I always want to use that value, regardless of the row. When I apply the function to the rows, the rows increment correctly, but Excel is also incrementing the C column to C4, then C5, then C6. I want the formula to always use the value in C3, regardless of the row, how do I force it to do that?

share|improve this question

migrated from stackoverflow.com Jul 3 '11 at 8:21

This question came from our site for professional and enthusiast programmers.

    
Ok, figured this out on my own. To keep a cell reference constant in a formula, even after copying the formula around the spread sheet, simply use $C$3. – user646584 Jul 3 '11 at 4:43
up vote 7 down vote accepted

Just use $C$3. The dollar signs tell Excel not to vary that row/column.

share|improve this answer
    
Why does Excel vary the row with $A9, but not with $C$3? Just seems a bit non-intuitive, they should have used a different symbol to differentiate it – user646584 Jul 3 '11 at 4:48
    
The dollar signs apply to rows and columns separately. $A9 varies the row but not the column; A$9 varies the column but not the row; A9 varies both and $A$9 varies neither. – jtbandes Jul 3 '11 at 4:53
    
ok, makes sense, thanks! – user646584 Jul 3 '11 at 5:06

You must log in to answer this question.