Forum Discussion
Tim_Parisi
Apr 22, 2019Copper Contributor
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".
- JustHereForExcelTipsCopper Contributor
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_MeCopper ContributorIncredibly 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.
- TwifooSilver ContributorIf 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.
Tim_Parisi , you may use absolute references for that
='sheet 2'!$K$75