5

I want to copy formulas with relative references to another range without changing the formula / updating those relative references. I know there are some paid add-ons that do that but I'm not looking to give money to people for something this simple.

Example:
A1 contains =C2+B3 (and is functioning/resolving)

Copy A1 into X1
X1 now contains =C2+B3 (and is functioning/resolving)

I do this and then i slightly modify the new copy of formulas via replacements and other means. (So i need them functional and not paste special - links)

Currently, I just replace = with x=, copy/paste, and then replace x= with =. I've seen people over the years use fancier ways via native XLS functionality but I don't know how. I need to do this for hundreds of different formulas at once so copying/pasting the formula from each one individually is not going to work.

/// There is an incomplete solution left to figure out:

Change cell format to text. Type =A1 now you get just text. Changing the format back to general doesn't resolve the formula. How can we resolve an array of these to start working as formulas again, not just text? (Other than going into every single one and pressing enter)

... (and for anyone curious Kutools add-on does that - exact copy)

5
  • Copy the formula and not the cell. Click on the cell. Highlight the text in the formula bar. Ctrl-C. Click the cell, then in the formula bar click and hit ctrl-v Commented Apr 27, 2017 at 14:43
  • For one by one yes, copying 500 of em at once is the goal (: And I've seen it done.
    – helena4
    Commented Apr 27, 2017 at 14:44
  • @ScottCraner I got a hint from someone in the building. You can change the format of the area to TEXT. then run a function (to simulate as if you went inside the cells and pressed enter.) copy, then change the cell format back to normal and run the function to calculate the formulas. Apparently F9 doesn't cut it.
    – helena4
    Commented Apr 27, 2017 at 16:03
  • Do you actually need that formula to be copied? What's the purpose behind it? Are you keeping it "saved" in X1 in case you change the A1 formula? Or do you want X1 to have the same exact formula showing in A1? We might be working on an XY Problem - can you describe the purpose/main idea behind what you're trying to do?
    – BruceWayne
    Commented Apr 27, 2017 at 19:51
  • Yes I do. I need to copy large parts of my sheets containing relative formulas, and make sure the relative formulas don't adjust for distance traveled. This is an absolutely apparent result from the test case if given with the x= replace.
    – helena4
    Commented Apr 28, 2017 at 6:06

4 Answers 4

2

This is assuming you have a saved copy of the workbook.


1. Cut the formulas for your range.
2. Open a new workbook and paste the cut range.
3. Close original workbook without saving.
4. Open original workbook.
5. Cut and paste the range from the new workbook to the new range in the original workbook.

The pasted cells will now explicitly reference the original positions (e.g.[WorkBook1.xlsx]Sheet1!A1). If you don't want the explicit reference, you can use find and replace to eliminate the extra text.

1
  • You may avoid closing the doc if you use a google doc for the paste target, then undo the software cut. But the solution is slower than just doing the replacement it seems.
    – helena4
    Commented Apr 28, 2017 at 6:30
1

You can use FORMULATEXT(). If the formula in A1 is =B1+C1, in D1 if you put =FORMULATEXT(A1), it'll return =C1+B1 as a string.

Then you can move the formula, copy/paste, etc.

enter image description here

5
  • You are close to winning this unless someone has a fancier method.
    – helena4
    Commented Apr 27, 2017 at 15:51
  • @helena4 - What do you mean "fancier"? What does your data look like? Can you clarify, in your OP, what exactly you're trying? Are you open to using Macros?
    – BruceWayne
    Commented Apr 27, 2017 at 15:52
  • Faster for huge data sets (:
    – helena4
    Commented Apr 27, 2017 at 15:53
  • @helena4 - You can drag the formula down and get all the formulas. Is it one or two formulas, and you want to copy over a large range? Can you post an example of the data and your expected output?
    – BruceWayne
    Commented Apr 27, 2017 at 15:54
  • Plus how do you calculate it? If you use paste special text from =FORMULATEXT you are left with formula text and you can't make it run the formula regularly via F9. And you don't want to be going inside every cell to press enter to the formulas start working.
    – helena4
    Commented Apr 27, 2017 at 16:08
0

What about using INDIRECT()?

=INDIRECT("A1"). This will show the value that A1's formula returns. If you update the formula in A1, it'll update everywhere. You can also copy/paste the INDIRECT() formula anywhere without having to adjust the reference:

enter image description here

1
  • Indirect is volatile, the computer starts to die when I am using around 500000 instances of that.
    – helena4
    Commented Apr 28, 2017 at 6:18
-1

I simply do this:

  1. In the cell with the formula hit F2
  2. Highlight the formula, and press Ctrl+c to copy it (as text).
  3. Paste it to another cell or range of cells :)
5
  • 1
    This is what @ScottCraner suggested, but OP has 500 such formulas to do and mentioned it's a bit cumbersome.
    – BruceWayne
    Commented Apr 27, 2017 at 15:13
  • I hadn't seen the comment, but given OP can highlight a (potentially massive) range of cells before the paste it's hard to see what especially cumbersome about it? Commented Apr 27, 2017 at 15:19
  • 1
    Well, not sure, but if each formula is different, going in to that many cells and manually copy/pasting the data might take a while. But without seeing an example of OP's data, this will work just as well. I am assuming that each formula is different. If it's just a few formulas to paste, then yeah OP can just do it over a range instead.
    – BruceWayne
    Commented Apr 27, 2017 at 15:23
  • Ah! i thought it was just a single formula, oh well, we'll see :) Commented Apr 27, 2017 at 15:25
  • ok i'll add the word "different" to the title.
    – helena4
    Commented Apr 27, 2017 at 15:48

You must log in to answer this question.

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