CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Nick Harper
Nick Harper

Reputation: 31

Copy Formulas to all rows but only increment one part (not all)

I have a spreadsheet where the first row works great using this formula:

=SUBSTITUTE(Variables!B2,"",Variables!A3)

I want the next row to be:

=SUBSTITUTE(Variables!B2,"",Variables!A4)

(So only the A part changes and B2 stays the same).

When I drag down, it also changes the B2 to B3.

Is there a way to lock this to only change the A number when I drag down?

Upvotes: 2

Views: 1871

Answers (1)

ed2
ed2

Reputation: 1497

See this suggestion:

=SUBSTITUTE(Variables!$B$2,"",Variables!$A3)

Note the dollar signs ($) to change your relative references to absolute references, everywhere except the bit you want to be changeable. (These can be for rows, columns or both).

When you fill down or right (including by dragging), the part with the $ will not change but the part without will increment.

As noted above, pressing the F4 key while the cursor is on the reference in the formula bar will toggle between having dollar signs for row, column or both.

Upvotes: 1

Related Questions