Keep a variable while copy/paste cell [duplicate]

Question

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

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

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

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

The problem happens when I try to use this formula 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)

Answer 1

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: https://stackoverflow.com/questions/7984282/excel-formula-meaning-of

Answer 2

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.