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?
From http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2:
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