Forum Discussion

Tim_Parisi's avatar
Tim_Parisi
Copper Contributor
Apr 22, 2019

copy and paste a range of cells and keep formula eferences

My goal is to copy a range of cells and have all the formulas in the copied range of cells pasted into a new location keeping the original formula references. I.e, I will have two identical data in two sets of cells on a sheet.

 

E.g. one of the cells in the range to be copied might have a formula similar to, "='sheet 2'!K75". I wish for that formula to be pasted into the destination cells and be "='sheet 2'!K75".

  • Tim_Parisi 

    Hey, thanks for the advice. But your solution is ultra specific to your use case (you have an array that is fixed). I wondered if you had found a more general workaround for different formulas linked to different cells, with no logic behind (oftentimes an output to a chaotic input sheet)

    Thanks guys!

  • Tim_Parisi , both assume changes cell by cell. To copy entire range the workaround could be

    - select entire range

    - Ctrl+H and replace = on #

    - copy/paste the range on new location

    - select range, Ctrl+H and replace # on = (for both ranges)

    • John_Bloggs_is_Me's avatar
      John_Bloggs_is_Me
      Copper Contributor
      Incredibly helpful!, I was also able to replace cell references in bulk, but I first have to replace = with # before doing so, then reverse that after I'm done replacing the cell references. A bit of a workaround but it is way better than moving the reference selections.
  • Twifoo's avatar
    Twifoo
    Silver Contributor
    If you dislike modifying your references from relative to absolute, select the original cell, select the entire formula therein on the formula bar, press Ctrl+C, press Esc, select the destination cell, press Ctrl+V, and press Esc.

Resources