How to mark elements of formula to "don't shift"

Question

I have two worksheets in my Gnumeric file.

In the first I have a lot of rows which contain an index. On the second I have the respective values for that index.

Now, I can use the ­­index function to reference the aspect I need.

=index(IndexValues!B1:B7;B1)

IndexValues is the second worksheet, B1:B7 denotes the value areas in that sheet, B1 is the column on the first sheet where the index number is stored.

But if I want to copy the line to the second row I get:

=index(IndexValues!B2:B8;B2)

which is not what I wanted. Instead I wanted

=index(IndexValues!B1:B7;B2)

So that the lookup area on the second sheet stays the same, but the field where the index number is taken from.

Context: For this specific case its about the names of Weekdays that need to be mapped to a input (which is a number from 1 to 7). But I'm having this problem more then here and would like to have a general case solution.

Answer 1

Use the $ prefix to indicate that cells and ranges should stay put when copy/paste/dragging:

=index(IndexValues!$B$1:$B$7;B2)

You can apply the $ to different parts of the cell address, so

$B2 means B should always be B when copied, but 2 can move relative
B$2 means B can move relative, but 2 must remain the same