stop functions following source cel's

[ThomasRoes]

Take a simple example:
A1 = 5
B1 = 5
C1 = '=A1 + B1

As soon as I cut A2, and paste it over A1, my function in C1 breaks. It become's =#ref + B1

I can solve this by using INDIRECT or INDEX.

But these functions have 2 major drawback's:
1) I can't copy - paste these functions from C1 to C2, and have them ajust to =indirect("A2")+indirect("B2")
2) If I have a big spreadsheet, that is finished as far as the functions are concerned, and only need's data entry, and someone didn't use the indirect or index trick's, I have a lot of rebuilding to do, specially because of point 1.

So what I am really looking for, is a trick to disable (per sheet or workbook) the feature of excel automaticly ajusting formula's when source cell's are cut and paste.

Thomas Roes

 

[Fishboy]

Take a simple example:
A1 = 5
B1 = 5
C1 = '=A1 + B1

As soon as I cut A2, and paste it over A1, my function in C1 breaks. It become's =#ref + B1

I can solve this by using INDIRECT or INDEX.

But these functions have 2 major drawback's:
1) I can't copy - paste these functions from C1 to C2, and have them ajust to =indirect("A2")+indirect("B2")
2) If I have a big spreadsheet, that is finished as far as the functions are concerned, and only need's data entry, and someone didn't use the indirect or index trick's, I have a lot of rebuilding to do, specially because of point 1.

So what I am really looking for, is a trick to disable (per sheet or workbook) the feature of excel automaticly ajusting formula's when source cell's are cut and paste.

Thomas Roes

Hi Thomas, welcome to the boards.

As far as I can tell (from some Googling) there is not set option to turn this feature off but there are a couple of quick and dirty workarounds that may suit you.

Option 1 - Use Find / Replace on the cells you want to copy. Replace = with # (or any other non-formula symbol). Copy the cells to the new location, then do another Find / Replace and swap the # back to =

Option 2 - Press Ctrl and ` (tilde key just below escape) to set formula view mode. Select the range to be copied and copy to clipboard. Open Notepad and paste. Reselect all the data from Notepad and copy. Go to the top left cell of the target range and paste. Press Ctrl and ` again to switch back out of formula view mode.

Option 3 - Use VBA instead of manually copy pasting. The following lines of code can be tweaked to suit your data requirements. Change the bold red Sheet1 to suit your sheet name. Change the bold green A11:D20 to suit your intended target range. Change the bold blue A1:D10 to suit the range you are trying to copy.

Sub CopyNonRelativeFormulas
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With
End Sub

I hope something above is of some use to you.</code>