OpenOffice Calc Tips

It’s been two years since I switched from MS Office to OpenOffice for all my word processing, spreadsheet, presentation and local database needs. I had been using OpenOffice for all my personal documents for a number of years prior and with the advances in Calc’s functionality I was pretty sure it could handle my professional work as well.

After two whole years I can confidently say that OpenOffice Calc does everything I need as well or better than MS Office. Over this period, due to changed business needs, I’ve had to spend more time with spreadsheets than in the previous ten years. With the much heavier usage I was concerned that I would eventually hit a need for some feature of Excel that is not yet implemented in Calc. To my delight I have not found any missing features and my employer got a productivity bonus because I haven’t had to spend time learning the new Microsoft UI.

Lately I’ve gotten around to writing custom functions for Calc and had quite a bit to learn because this is the one area where decades of 123 and Excel experience is not much help. I’ve put some tips and custom functions in a Function Demos Spreadsheet that you can download. One spreadsheet feature that I’ve found to be essential for long term maintenance, that most people don’t use, is named ranges. If you haven’t used named ranges in Calc spreadsheets I recommend reading the tips linked at the end of this post.

Recently I needed to work with a Calc document I had constructed a year and a half ago. While I had used named ranges extensively making it easier to read the formulas, I wanted to change the range names as the abbreviations I had used weren’t as clear as I’d like. With dozens of named ranges defined across multiple sheets and each used on other sheets it wasn’t going to be easy to edit them one at a time. to manually edit a couple range names all you have to do is:

  1. Go to the menu item Insert->Names->Define .
  2. Select the Name you want to change.
  3. Copy the range definition from the Assigned to text box.
  4. In the name text box change the name.
  5. Paste the range definition in to the Assigned to text box.
  6. Then click the add button, you now have two names for the range, click OK to close the dialog.
  7. Use Edit->Find & Replace to change the old name in formulas to the new name.
  8. Go to the menu item Insert->Names->Define .
  9. Select the old name and click the delete button.

While this process is workable for changing a few names, it’s faster to do than to describe in text, it is not really efficient when you’ve got dozens of names to change. To aid with managing the multiple changes I created a custom array function that creates a list of all named ranges, it’s in the Function Demos Spreadsheet. After entering the array function in a new sheet, select the whole array and use the Data->Text to Columns… function to make the name list static. Next copy the name list to the column to the right, and edit that column to the new names you want to use. You now have a neat two column list of old and new range names that you can use to copy and paste names from for the Insert->Names->Define and Edit->Find & Replace dialogs.

General Info

Range Names Tips

Other Tips