Forum Discussion
JJVega
Sep 26, 2023Copper Contributor
Copying without refreshing references
Hello, I'd like to copy a formula in a cell to another cell without refreshing references, I mean, exactly the same formula en other location. I know that i can move the formula with "CtrlX" but...
HansVogelaar
Sep 26, 2023MVP
Let's say you have a formula such as
=SUM(A1:A4)
If you copy the cell with the formula and paste it elsewhere, the range in the formula will change. You can prevent this by making the range reference absolute:
=SUM($A$1:$A$4)
The range will then remain the same when you copy/paste the cell with the formula.
Another option:
- Select the cell with the formula.
- Press F2 to edit the cell, or click in the formula bar.
- Select the entire formula (Ctrl+A) and copy it (Ctrl+C).
- Press Esc to stop editing the cell.
- Select the target cell and paste (Ctrl+V).
JJVega
Sep 26, 2023Copper Contributor
Thank you Hans,
The absolute reference is a good point however I can't use it because I'd like to drag the formula to other cell from the new position so I need to conserve it with no dollars.
Copying the formula from the formula bar is the option that I'm using right now but I was wondering if an automatic process without cutting exists.
Thank you so much for your response!
The absolute reference is a good point however I can't use it because I'd like to drag the formula to other cell from the new position so I need to conserve it with no dollars.
Copying the formula from the formula bar is the option that I'm using right now but I was wondering if an automatic process without cutting exists.
Thank you so much for your response!
- HansVogelaarSep 26, 2023MVP
I'm not aware of another built-in way to do what you want.
A VBA macro would be another possibility, but that is probably overkill.