| View previous topic :: View next topic |
| Author |
Message |
echostatic
General User
Joined: 06 Aug 2006
Posts: 8
|
 Posted: Fri May 18, 2007 6:10 pm Post subject: Prevent auto-incrementing of part of a formula when pasting |
 |
|
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 |
|
 |
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3318
|
| Posted: Fri May 18, 2007 6:18 pm Post subject: |
|
|
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 |
|
|
echostatic
General User
Joined: 06 Aug 2006
Posts: 8
|
| Posted: Fri May 18, 2007 6:19 pm Post subject: |
|
|
Wow, how funkular.. thanks so much for the pointer! (Happily pasting all over this mo' now  )
Jacob |
|
| Back to top |
|
|
AndrewZ
Moderator
Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA
|
| Posted: Fri May 18, 2007 6:21 pm Post subject: |
|
|
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 |
|
|
bobharvey
Super User
Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire
|
| Posted: Sat May 19, 2007 1:12 am Post subject: |
|
|
| 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 |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3318
|
| Posted: Sat May 19, 2007 2:43 am Post subject: |
|
|
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 |
|
|
bobharvey
Super User
Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire
|
| Posted: Sat May 19, 2007 3:24 am Post subject: |
|
|
| 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 |
|
|
RonIA
Super User
Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA
|
| Posted: Sat May 19, 2007 3:37 am Post subject: |
|
|
| 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 |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sat May 19, 2007 7:13 am Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat May 19, 2007 7:36 am Post subject: |
|
|
| 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 http://forum.openoffice.org |
|
| Back to top |
|
|
|
