Google Spreadsheet - 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 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 !

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: http://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.