OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Switched from MSOffice to OpenOffice. HELP!!

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
RealityDirt
Newbie
Newbie


Joined: 29 Aug 2007
Posts: 3

PostPosted: Wed Aug 29, 2007 7:50 pm    Post subject: Switched from MSOffice to OpenOffice. HELP!! Reply with quote

I'm a noob to OpenOffice and I'm trying to switch my MSOffice use to OpenOffice.

The file in question is a template I created in Excel. I use it to calculate an invoice for jobs I do throughout the month. Each row calculates a subtotal that is totaled at the bottom of the final column. When I open this Excel file in Calc everything works great in the top line where all the formulas are. Unfortunately, there are 2 problems that I just can't seem to solve.

In excel when I get to the final column, I hit tab and it automatically inserts a row below the row I was just working on. That row contains all of the formulas of the previous row. In Open Office when I hit tab it just moves to the next column. When I hide those columns and hit tab in the final column nothing happens.

#1 I want to know how to tab (or use some other button) and create a new row below the one I was just working on...
and...
#2 I want that new row to contain the formulas from the previous row.

If there is no automatic way to do this, I will have to stick with excel. HELP!!!!
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Thu Aug 30, 2007 4:58 am    Post subject: Re: Switched from MSOffice to OpenOffice. HELP!! Reply with quote

RealityDirt wrote:
If there is no automatic way to do this, I will have to stick with excel.


I may be wrong, but I doubt that there is some direct, automatic way without reprogramming [writing a macro.]

Different cars have different controls, and in different places. So do spreadsheets. No two are entirely alike in all respects, nor entirely compatible, period. Your choice, I should think. Personally, I don't understand why people so frequently say that they will stick to the one just because of some one single little difference that they find personally annoying. But then, I know people who wouldn't dream of driving anything other than a North American Ford, even though they have put neither thought nor effort into driving either European or Japanese, or whatever.

I have on hand all three, Excel, Quattro Pro, Calc, and at times others. I have absolutely no expectation of finding the complete functionality of one within the other, nor complete compatibility. In fact, they ALL lie about the latter. More often than not, it has been my experience that if you start, run, and finish in the one [either one] with no expectation of compatible functions, but just dependence on available functions for the one you are using, you will not be so disappointed. So I have taken the time and trouble to re-write my own efforts from scratch in each, as needed, sometimes with extensive effort. In the long run that is still less time-consuming than trying to make one do what the other does in the same manner. Also, there is sometimes a BIG advantage in redoing, as there is always room for improvement with a second writing, as with any text.

Again, it's your choice as to which program you use, but not part of the real query about compatibility. It's another question altogether, involving many other factors, including cost, not just one simple difference.

David.
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu Aug 30, 2007 6:46 am    Post subject: Re: Switched from MSOffice to OpenOffice. HELP!! Reply with quote

RealityDirt wrote:
In excel when I get to the final column, I hit tab and it automatically inserts a row below the row I was just working on. That row contains all of the formulas of the previous row. In Open Office when I hit tab it just moves to the next column. When I hide those columns and hit tab in the final column nothing happens.

#1 I want to know how to tab (or use some other button) and create a new row below the one I was just working on...
and...
#2 I want that new row to contain the formulas from the previous row.

When using various version of Excel at work, I have never seen that operation, but perhaps it because I haven't looked for it. Has someone written a macro for you to perform this operation but so long ago that you've forgotten? (I can hardly remember yesterday, let alone years ago Embarassed )
Back to top
View user's profile Send private message
RealityDirt
Newbie
Newbie


Joined: 29 Aug 2007
Posts: 3

PostPosted: Thu Aug 30, 2007 8:32 am    Post subject: Macros Reply with quote

I use MS Office Version X for my Mac. No macro was ever written for me for this function. I have a PC in my home office and I want to migrate all of my work off of the Mac to it. I love trying out new programs so I went with OpenOffice.

As for redoing the spreadsheet, I have no problems doing that. Unfortunately I would still need the functions I mentioned. It's not a matter of not giving OpenOffice a chance. It's a matter of what is most efficient and least time consuming.

My spreadsheet is compiled over a month's time, so it's not like I sit down with all my data and create it in one sitting. It's a living document for the duration of the period I invoice so I need it to work for me. It sounds like I can't duplicate my MS Office experience. Is that true? I'm still holding out hope. Can anyone help???
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu Aug 30, 2007 9:18 am    Post subject: Reply with quote

I can't get any such functionality from Excel 2003 on my work laptop, even having looked at various options. Perhaps you've been spoilt with some extra functionality in the Mac version.

If you really need this for the PC, then if nobody offers specific help here, I expect the OOo Macro forum will have people who can do it for you.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Aug 30, 2007 1:48 pm    Post subject: Reply with quote

No, Excel does not provide that feature on the sheet. But it provides an input form with an insert button. That insert button expands formulae indeed.

Wrote a quick and dirty macro, which provides that functionality on the sheet.
http://www.oooforum.org/forum/viewtopic.phtml?p=245816
Link some keyboard shortcut with this macro. I suggest Ctrl+Insert.
Back to top
View user's profile Send private message
RealityDirt
Newbie
Newbie


Joined: 29 Aug 2007
Posts: 3

PostPosted: Sat Sep 01, 2007 11:40 pm    Post subject: Reply with quote

How do I save this macro so I can load it to my document?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Sep 02, 2007 2:32 am    Post subject: Reply with quote

http://www.oooforum.org/forum/viewtopic.phtml?t=7995
The hierarchy in the macro-organizer is Containers>Libraries>Modules with routines (subs and functions). "Container" can be a document or public "My Macros". Container "OOo Macros" is availlable to all users of this installation and is read-only normally.
Since this gimmic is not specific to a certain document you may save it in any library of "My Macros" and then assign a shortcut to it (Tools>Customize...)
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Sep 02, 2007 5:21 am    Post subject: Reply with quote

Although the thread seems to have switched topics, I'd like to add my own humble opinion about macros, which are dealt with so readily by those with programming experience [I have a little myself, but am getting to be too old now to get intimately involved with every new language.] What I say here is in reference to all spreadsheets, not just Calc. So,in a perfect world....

Macros are extremely useful for automating certain tasks, either repetitive tasks, or tasks that would normally take several, or many steps to invoke, or they automatically initiate a task. With the thought in mind that most users of spreadsheets will have minimal programming experience, the development of a macro should be as automatic and simple as possible, being a straightforward recording of mouse and keystrokes, as much as possible.

What a macro should not be needed for is to provide additional useful components of the spreadsheet. If they are designated to be so useful, then they should be incorporated into the initial program as GUI functions. It should not be necessary to be a programmer to invoke these tasks.

The thing is that computers, initially, were a toy for the "eggheads", John von Neumann and his buddies. Now, they are available, and FAR more powerful, for the common folk like myself. As much as I appreciate that [immensely], I still see a few too many aspects of "eggheadedness" being necessary to proceed with as much ease as we would all like. It is still the realm of the eggheads, it would seem.

If I MUST program, I'd far rather use Visual Basic than GWBasic, for example. In other words, there is still much that can be done in this area of program development, I think.

Just a thought. Wink

David.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Sep 02, 2007 8:59 am    Post subject: Reply with quote

David, I fully agree.
Macros are a pest. Too often they make documents incompatible with other applications, stop working when the document's design has changed. And too often they hide the incompetence to utilize the given toolset of a complex application.

However, this one could serve quite a large group of users. It reduces a common set of user-actions to one single call. If you edit your document somewhere else (where this macro is not installed) you have to do a few more clicks to insert new records into a database-like list. That's all. No document's functionality will depend on the existence of this macro.

A common database-like list has one header row (possibly including auto-filters), some formatted input columns and calculated fields sharing the same formula within a column. Quite often such a list can be filtered and sorted while serving it's data to charts, data pilots and formula cells elsewhere.
It is a frequently reported problem that filters, sorting, charts, data pilots and references do not adjust to the referred list when you just append data below the list. You have to insert cells in order to adjust all references. Then you have to drag down the formulas of the list's calculated fields. The macro performs these simple actions wherever you insert and whereever your calculated fields are placed within the current region. It does not depend on certain preconditions like having formulas in columns D to F. It fails silently to insert if the selection is inappropriate. If there is nothing to drag down it fails silently as well. Today I added two more lines which select the blank cells of the inserted range, so you can navigate more easily within the new record. Added a Python version as well (meanwhile I prefer macros written in Python).
Since it operates within the current region (block of adjacent non-empty cells), you can exclude other ranges on the same sheet by simply keeping blank columns/rows around the list (possibly hidden and locked).
Expansion of references depends on the application wide setting Tools>Options>General>"Expand references when new columns/rows are inserted". If this option is *not* set, dependent references will adjust only if you insert cells within the range in question. Having this option set, dependent references will adjust even if you insert directly below and right of the range in question. This option may be annoying while you set up a new spreadsheet, which is the reason why you can turn it off.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group