Ask Your Question
0

How to control which values are incremented on drag down?

asked 2018-01-12 01:43:30 +0100

gunwald gravatar image

updated 2018-01-12 01:56:10 +0100

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?

edit retag flag offensive close merge delete

Comments

Jim K gravatar imageJim K ( 2018-01-12 15:56:16 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-01-12 04:50:02 +0100

robleyd gravatar image

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.

edit flag offensive delete link more

Comments

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

librebel gravatar imagelibrebel ( 2018-01-12 15:27:05 +0100 )edit
0

answered 2018-01-12 02:29:21 +0100

librebel gravatar image

updated 2018-01-12 02:30:22 +0100

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-12 01:43:30 +0100

Seen: 14 times

Last updated: Jan 12