Web Applications Stack Exchange is a question and answer site for power users of web applications. 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

Possible Duplicate:
'Lock' part of formula when copying from one cell to another

I am using in one of my spreadsheet a formula referencing to the column title (matching how many times it is listed in another sheet, to be precise).

Something like:
Cell B2:

=IF(countIf(Data!2:2;B1)>0 ;"present","absent")

So if, in the row2 of the sheet "Data", the 1st line/title(B1) of my column(B) is present at least one time, it write "present", otherwise it write "absent". This is working without problems.

The problem happens when I try to use this formulta for the next row, if I paste it in B3, it looks like:

Cell B3:

=IF(countIf(Data!3:3;B2)>0 ;"present","absent")

The B1 from my original formula becomes B2 and now refers to the value of the previous row.

How can I keep a variable static whenever I copy/paste the cell ? (I do still want for the other values to increment)

Thank you very much !

share|improve this question

marked as duplicate by Al E., phwd Sep 11 '12 at 4:14

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

up vote 8 down vote accepted

Like in Microsoft Excel, you can use the $ to make an absolute reference.

Thus, you could try this:

=IF(countIf(Data!2:2;$B$1)>0 ;"present","absent")

Here is a Stackoverflow thread about this: http://stackoverflow.com/questions/7984282/excel-formula-meaning-of

share|improve this answer
Thank you very much for this quick answer, exactly what I was looking for ! – JulienH Sep 7 '12 at 12:46

Just like in Excel, you need to put a dollar sign $ before the column and row header in your formula like this:

=IF(countIf(Data!2:2;$B$1)>0 ;"present","absent")

If you want the column and row of B1 not to change. I don't know which of both you want static, you can take away one of the $'s.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.