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

user_6681974 0
Viewed 214

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?

excel-2010 excel-formula excel
1 Answers

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.

user_141529890
Related