How to control which values are incremented on drag down?

[gunwald]

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. 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?

[jimk]

Cross-posted to LibreOffice Calc: How to control which values are incremented on drag down? - Stack Overflow.

[robleyd]

Or you could use absolute references for the fixed ranges in your initial formula:

=INDEX($Sheet1.$B$1:$B$100;MATCH(C1;$Sheet1.D1:D100;0))

If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

[librebel]

+1 That’s even less work… assuming the second range is also meant to be an absolute reference $Sheet1.$D$1:$D$100

[librebel]

Hello @gunwald,

You could give a fixed Name to your two ranges $Sheet1.B1:B100 and $Sheet1.D2:D100, via the menu Sheet : Named Ranges and Expressions : Manage... ( CTRL+F3 ).

Suppose you named your ranges “Sheet1_B1_B100” and “Sheet1_D2_D100” respectively, then you could put the formula:

=INDEX(Sheet1_B1_B100;MATCH(C1;Sheet1_D2_D100;0))

and drag the cell handle downwards so that only C is incremented.

HTH, lib