How to disable Excel's automatic cell referenc

Question

I've got a massive Excel 2003 spreadsheet I'm working on. There are a lot of very large formulas with a lot of cell references. Here's a simple example.

='Sheet'!AC69+'Sheet'!AC52+'Sheet'!AC53)*$D$3+'Sheet'!AC49

Most of them are more complicated than that, but this gives a good idea of what I'm working with. Few of these cell references are absolute ($s). I'd like to be able to copy these cells to a different location without the cell references changing. I know I can simply use f4 to make the references absolute, but there is a lot of data and I may need to use Fill later. Is there any way to temporarily disable the cell reference changing on copy-paste/fill without making the references absolute?

EDIT: I just found out that you can do this with VBA by copying the cell contents as text instead of a formula. I'd like to not have to do this though because I want to copy whole rows/columns at once. Is there a simple solution I am missing?

Answer 1

A very simple solution is to select the range you wish to copy, then Find and Replace (Ctrl + h), changing = to another symbol that is not used in your formula (e.g. #) - thus stopping it from being an active formula.

Then, copy and paste the selected range to it's new location.

Finally, Find and Replace to change # back to = in both the original and new range, thus restoring both ranges to being formulae again.

Answer 2

I think that you're stuck with the workaround you mentioned in your edit.

I would start by converting every formula on the sheet to text roughly like this:

Dim r As Range

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 1) = "=") Then
        r.Formula = "'ZZZ" & r.Formula
    End If
Next r

where the 'ZZZ uses the ' to signify a text value and the ZZZ as a value that we can look for when we want to convert the text back to being a formula. Obviously if any of your cells actually start with the text ZZZ then change the ZZZ value in the VBA macro to something else

When the re-arranging is complete, I would then convert the text back to a formula like this:

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 3) = "ZZZ") Then
        r.Formula = Mid$(r.Formula, 4)
    End If
Next r

One real downside to this method is that you can't see the results of any formula while you are re-arranging. You may find that when you convert back from text to formula that you have a slew of #REF errors for example.

It might be beneficial to work on this in stages and convert back to formulas every so often to check that no catastrophes have occurred

Answer 3

Try this: Left-click the tab, making a copy of the whole sheet, then cut the cells you want to keep the same and paste them into your original worksheet.

Answer 4

From http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2:

1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
2. Select the range to copy.
3. Press Ctrl+C
4. Start Windows Notepad
5. Press Ctrl+V to past the copied data into Notepad
6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
8. Press Ctrl+V to paste.
9. Press Ctrl+` to toggle out of formula view mode.

Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and click Next. Clear all of the Delimiter option checkmarks except Tab.

Or, from http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/:

If you're a VBA programmer, you can simply execute the following code: 
With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Answer 5

I came to this site looking for an easy way to copy without changing cell references. But now I thnk my own workaround is simpler than most of these methods. My method relies on the fact that Copy changes references but Move doesn't. Here's a simple example.

Assume you have raw data in columns A and B, and a formula in C (e.g. C=A+B) and you want the same formula in column F but Copying from C to F leads to F=D+E.

1. Copy contents of C to any empty temporary column, say R. The relative references will change to R=P+Q but ignore this even if it's flagged as an error.
2. Go back to C and Move (not Copy) it to F. Formula should be unchanged so F=A+B.
3. Now go to R and copy it back to C. Relative reference will revert to C=A+B
4. Delete the temporary formula in R.
5. Bob's your uncle.

I've done this with a range of cells so I imagine it would work with virtually any level of complexity. You just need an empty area to park the coiped cells. And of course you have to remember where you left them.

Answer 6

This simple trick works: Copy and Paste. Do NOT cut and paste. After you paste, then reselect the part you copied and go to EDIT, slide down to CLEAR, and CLEAR CONTENTS.