0

I have a formula that references A1:B5 for instance, the result lives in any arbitrary cell, say D1. How do I copy and paste the same formula, referencing the same values as in D1, through the entire column D? For some reason, I can't find this answer anywhere.

If I select D1 for instance, and click the bottom right, and drag to use auto-fill, it seems to increment the A1:B5 values which ruins the results.

  • 1
    Try this: click a1:b5. Then in another cell, (a1:b6 for example), perform the formula you'd like to perform on a1:b5 (add 10% for example). Then copy a1:b6 - not a1:b5 Finally, highlight the set of cells where you want to paste this formula (for example a1:b10 to a1:b20), then paste. The results formula in A1:b6 will be copied and run in a1:b10. those results should be copied to a1:b11 etc. – David DelMonte Sep 10 '13 at 2:39
  • 2
    You are using Fixed value ($) for Cell A and fixed for field 1 ($A$1) it will copy and repeat that in the row 1, but also in the coulomb D. So try following $A1:$B5 (without $ for the field). Now when you pull down it will copy correctly but also in the row 1. – Ruskes Sep 10 '13 at 8:08
2

You need to use an absolute reference with $.

So that the cell references never changes when doing a copy and paste or fill change it from A1:B5 to $A$1:$B$5.

If you want you can even make a reference that is half absolute. The reference $D1 means that when you copy and paste the D will stay the same but the row number will change.

0

Did you try copying the formula from D1 (select the cell and do Copy, not selecting the formula itself). Then select the range of cells D2 to D-whatever and paste. This should work as long as you don't have the text inside the formula definition box selected, but rather you copy the cell itself.

0

You are using Fixed value ($) for Cell A and fixed for field 1 ($A$1) it will copy and repeat that in the row 1, but also in the coulomb D.

So try following $A1:$B5 (without $ for the field).

Now when you pull down it will copy correctly but also in the row 1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .