Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Copy/Pasting Formulas with Relative References

Request Demo

I am trying to copy a formula with a relative reference to another cell, but all the relative references are acting as absolute references. For example, in cell E1 I have the formula =sumif(A:A,D1,C:C) if I copy and paste this formula down to cell E11 the exact formula is copied into E11. With relative referencing I would expect =sumif(A:A,D11,C:C) to be the resulting formula in this cell. Relative referencing works when dragging the formulas, but here I dont have the option as I have other data in between the cells. 

Is there a way to get relative references to change when using copy/paste?

0

Comments

9 comments

Date Votes
  • Mike

    Hi Michael,

    Welcome to the Community! Thanks for the question.

    Copy and paste should honor the relative reference, for sure, if you copy and paste the cell itself. If you copy the formula in the function bar and paste that, it will not, however. Here's a quick example:

    Is this maybe what his occurring for you? If I've missed the mark, or you have any follow-ups for me, let me know and I'll be happy to help. In the meantime, thanks much for your contributions and have a great rest of your day. :)

    0
  • Michael Tabler

    Hi Mike,

    Thanks for the response, but this is unfortunately not my issue. I am copying the cell, not formula text.

    After my first post I did notice that when I copy/paste one cell the relative referencing works, similar to you example above. I was able to consistently reproduce my issue when I would copy multiple adjacent cells and paste them elsewhere. In that case, all of the relative references will act as absolute references.

    0
  • Onni Kytonummi

    I'm having a similar issue and even copying a single cell doesn't seem to help. When copying something like =CONCATENATE(K1;K2;B16;E16;C16), it doesn't keep relative references when copying to another cell. It works for simple =SUM functions, but not for every function.

    0
  • Isabel Messore
    • Edited

    Onni Kytonummi I'm looking into this for you now! To confirm, are you saying that when you copy this formula to another cell it maintains the cell references of K2, B16, etc?

    0
  • Onni Kytonummi

    Yes. If I copy the formula to another cell, it maintains the cell references. So =CONCATENATE(K1;K2;B16;E16;C16) stays as =CONCATENATE(K1;K2;B16;E16;C16) regardless where you copy it to. Noticed the same happening with SUMIF function as well.

    0
  • Isabel Messore

    Apologies for the delay Onni Kytonummi!

    I pasted that formula into a spreadsheet in Workiva and also got the #value! error. After updating the ";" to "," it worked perfectly, including copying and pasting and having the formula update! Give this a shot and let me know if you're still having issues!

    0
  • Onni Kytonummi

    Our local settings are set as Suomi (Finland) so our formulas use ";" for separation. I now tested changing the local settings to English (United States) and relative references seem to work when copy/pasting. So the problem seems to affect only locals that use ";" for formulas.

    1
  • Sami Ukonaho

    It seems that this copy-paste behaviour has somehow changed in wdesk in the last few days/weeks since we also started experiencing this issue but to our knowledge we have not changed any of our locale settings. Also Suomi (Finland) as locale here and ";" in all problematic formulas. 

    1
  • Isabel Messore

    Onni Kytonummi and Sami Ukonaho,

    I've created a support ticket for the both of you. Our Support Team will reach out, take a deeper dive into this and determine what's causing the issue. 

    1

Please sign in to leave a comment.