4

I have a complex formula in a cell with various references to other cells.

=INDEX($Sheet1.B1:B100;MATCH(C1;$Sheet1.D1:D100;0))

I want all cell in the same column to have the same formula with only one reference incremented. But if I select the field and drag down the little square all references are incremented.

enter image description here

So the following fields would have the values:

=INDEX($Sheet1.B2:B101;MATCH(C1;$Sheet1.D2:D101;0))
=INDEX($Sheet1.B3:B102;MATCH(C2;$Sheet1.D3:D102;0))
=INDEX($Sheet1.B4:B103;MATCH(C3;$Sheet1.D4:D103;0))
…

But what I need is:

=INDEX($Sheet1.B1:B100;MATCH(C1;$Sheet1.D2:D100;0))
=INDEX($Sheet1.B1:B100;MATCH(C2;$Sheet1.D2:D100;0))
=INDEX($Sheet1.B1:B100;MATCH(C3;$Sheet1.D2:D100;0))
…

That means all matrix should not be incremented, the single reference to column C has to be incremented. How can I do this, where it is not possible to to it manually as too many rows are affected?

user5950
  • 242
  • 1
  • 12
  • 2
    Cross-posted to https://ask.libreoffice.org/en/question/142837/how-to-control-which-values-are-incremented-on-drag-down/. – Jim K Jan 12 '18 at 14:56

1 Answers1

7

Instead of:

=INDEX($Sheet1.B1:B100;MATCH(C1;$Sheet1.D1:D100;0))

use:

 =INDEX($Sheet1.$B$1:$B$100,MATCH(C1,$Sheet1.$D$1:$D$100,0))

Place a $ dollar sign before each column and row you don't want changed. That is $B$1 .... $B$100 .......... $D$1 ..... $D$100

Zebrafish
  • 11,682
  • 3
  • 43
  • 119