« Random numbers: Normal Distribution II | Main | Text manipulation 1 : Concatenation »
January 28, 2005
Copying Formulas while preserving references
When you cut and paste formulas from a range of cells, the cell references within the formulas will be automatically adjusted. Here, we show how to work aroud this 'feature'.
WIth B7:E7 selected, we select Edit - Find & Replace We will be replacing the "=" characters with "#" in each formula. Now, we have a range of text values that will be unchanged in a cut/paste or copy/pastee operation.
We can now copy/paste on the range of text values.
The last step is to use Find & Replace to restore the original formulae by replacing "#" with "=", reversing the previous Find & Replace operation.
Posted by Dave at January 28, 2005 03:02 AM
Comments
Neat Trick! The two problems I have run into recently go like this: Create a ledger of Accounts sheet, with account titles across top row. Use the Insert->Names->Labels menu item to make those columns labeled as accounts, with the "top row" option. Now you have labels/ranges/data references (docs are light in this department) that are those account names. Go to another sheet, and use those names. Save. Reload the file. The Labels have automagically disappeared, and all cells using the labels say #!NAME or something like. To fix, one must reapply the labels, then go to each cell using one of the and screw around with the quotes to make it switch to actually using the label properly. Makes no sense to me! Second issue, using the same labels, how does one make the label absolute? Such that moving the formula (by any means) to another cell DOES NOT ADVANCE the label the next label... Dollar sign in front of label thoroughly confuses OpenOffice.org. Any thoughts? Thanks a bunch in advance!
Michael Fabio
Maine Maritime Academy
Posted by: Michael Fabio at March 31, 2005 12:35 AM