1

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.

2

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
2
  • Great! Thank you. Do you happen to know where in the manual that is written down? I tried searching it, but failed. – Angelo Fuchs Feb 11 '16 at 22:09
  • No sorry. Though spreadsheet apps tend to adopt the standards set by Excel for compatibility, so if you search for "how to do X in Excel" the answers will apply to LibreCalc, Google Spreadsheet, Excel, Gnumeric etc in many cases, particularly around the basics. – Paul Feb 11 '16 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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