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

Prevent auto-incrementing of part of a formula when pasting

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


Joined: 06 Aug 2006
Posts: 8

PostPosted: Fri May 18, 2007 6:10 pm    Post subject: Prevent auto-incrementing of part of a formula when pasting Reply with quote

Is it possible to keep a link to another sheet's cell static in a formula when pasting?
For example, if I copy this formula:
=H32*Etsy.N4+I32*Etsy.N6

and paste it on the next line, it comes out as:
=H33*Etsy.N5+I33*Etsy.N7

when what I really want it to be is:
=H33*Etsy.N4+I33*Etsy.N6
(note the "Etsy" references remain the same)

Is there a way to prevent auto-incrementing of specific portions of the formula so I can paste the formula all willy-nilly and keep the Etsy cell reference the same?

Thanks!
Jacob
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri May 18, 2007 6:18 pm    Post subject: Reply with quote

You have to know where to look in Help. I had a devil of a job. On the Index tab, type absolute addresses in spreadsheets. The great mystery is that you type $ in front of any element which you wish to remain constant (="absolute") - in the original cell from which you copy.
_________________
search forum by month
Back to top
View user's profile Send private message
echostatic
General User
General User


Joined: 06 Aug 2006
Posts: 8

PostPosted: Fri May 18, 2007 6:19 pm    Post subject: Reply with quote

Wow, how funkular.. thanks so much for the pointer! (Happily pasting all over this mo' now Wink )

Jacob
Back to top
View user's profile Send private message
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Fri May 18, 2007 6:21 pm    Post subject: Reply with quote

I also use $, but named ranges would also work. In the help, lookup names, defining for cells.
_________________
<signature>
* Did you solve your problem? Do others a favor: Post the solution
* OpenOffice.org Ninja
* BleachBit
</signature>
Back to top
View user's profile Send private message Visit poster's website
bobharvey
Super User
Super User


Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

PostPosted: Sat May 19, 2007 1:12 am    Post subject: Reply with quote

noranthon wrote:
The great mystery is that you type $ in front of any element which you wish to remain constant (="absolute") - in the original cell from which you copy.


Not too great a mystery, surely? Spreadsheets have worked like this since at least the early 1980s?
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Sat May 19, 2007 2:43 am    Post subject: Reply with quote

I called it a mystery mainly because when I found the section in Help, it seemed to be extraordinarily long for such a simple proposition - somewhere hidden in all the verbiage was a very simple message.

It's also a complete mystery to some, as shown by the number of (new) spreadsheet users who ask about it. Help should be under some likely index such as "Copying formulas", not hidden under an esoteric term like "absolute" - the third mysterious feature.
_________________
search forum by month
Back to top
View user's profile Send private message
bobharvey
Super User
Super User


Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

PostPosted: Sat May 19, 2007 3:24 am    Post subject: Reply with quote

noranthon wrote:
Help should be under some likely index such as "Copying formulas", not hidden under an esoteric term like "absolute" - the third mysterious feature.


Hmm. Relative and Absolute addressing is another long-established definition. I rather agree with you that the help is less than helpful in many cases (see Here, for example).

But the help is probably never going to be big enough to contain a beginners guide to everything, that is not, really, what it is for. There may be a very good case for having a "getting started" guide installed along with the application, but until that happy day I would consider some of the following:
http://www.tutorialsforopenoffice.org/
http://www.learnopenoffice.org/tutorials.htm
http://www.openofficetips.com/


You could also consider joining the documentation project, or adding contributions here: http://www.oooauthors.org/en
Back to top
View user's profile Send private message
RonIA
Super User
Super User


Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

PostPosted: Sat May 19, 2007 3:37 am    Post subject: Reply with quote

noranthon wrote:
It's also a complete mystery to some, as shown by the number of (new) spreadsheet users who ask about it. Help should be under some likely index such as "Copying formulas", not hidden under an esoteric term like "absolute" - the third mysterious feature.


Agreed, OO.o is like many applications, if you know what something is called in the help system, it is easy to find. (Could almost say, if it is easy to find in the Help files, you probably already know it anyway.) If you don't know what it is called, the cross references may not be in there for, "Copying a formula and making the rows stay the same while letting the columns automatically update to their new location, but only for two of the four references in this formula."

I don't know if we will ever (we in the universe of computer applications) be able to get a help system that is not too "watered down" for power users while still being accessible by neophytes. It's kind of like why there are 1000s of books about one application (MS Word is a perfect example). To be useful to users with 20 years experience using computers daily it has to assume you know A-X and only need help with Y and Z. To be useful to users with zero experience it has to assume you MIGHT be able to turn on the computer unassisted.

I think that is why we will always need the human element to translate in real time (like on this forum) from what a normal user might call something ("that blue squiggly thing in the upper right hand side of the screen that looks like a dolphin playing soccer") into what the application calls it ("Oh, you mean the Donglefritz Diety Transfixer, you will find that information in the help file under, 'Esoteric functions used by tri-lingual monks in changing the references to god into GOD whilest changing references to God into god and changing GOD into God on even numbered Wednesdays in October during a full moon.'")
_________________
Ron from Iowa, USA
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat May 19, 2007 7:13 am    Post subject: Reply with quote

noranthon wrote:
The great mystery is that you type $ in front of any element which you wish to remain constant (="absolute") - in the original cell from which you copy.


The great mystery to me is that spreadsheets, being so inordinately complex and requiring so much effort, and skill and knowledge to produce, I'm thankful for what I get. But dealing with health issues doe that to you, I suppose.

The F4 key assists there a lot, I hear.

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: Sat May 19, 2007 7:36 am    Post subject: Reply with quote

David wrote:

...
The F4 key assists there a lot, I hear.

David.

It's Shift+F4. It toggles all addresses within the active cell. While in edit-mode it toggles the addresses within a selected portion of a formula.
A1 > $A$1 > A$1 > $A1 > A1 > ...
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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