[Solved] How to keep a static cell reference?

[Solved] How to keep a static cell reference?

So i'm creating a spreadsheet (with about two hundred - three hundred rows)
To calculate from columns B - J (row 2) multiplied by the number of units i place in its corresponding row and in row L i calculate total cost (B2*B4)
As you can imagine having a few hundred rows to add, I tried 'paste special' and chose Shift down (and even if i dont)
It moves both rows down.
Is there anyway I can 'anchor' the formula to continue to use row 2 yet continue to modify the second value (+1)?

I've tried using the find & replace and it says it can't find the value (I use "current selection only" because i dont want it modifying the entire table.. but it doesn't seem to work so at the moment i'm actually alt tabbing out to notepad and using its find & replace to modify all the values.)

Is there an easier way to do this?

My current formula looks like:
= (B2*B15) + (C2*C15) + (D2*D15) + (E2*E15) + (F2*F15) + (G2*G15) + (H2*H15) + (I2*I15) + (J2*J15)

I want B2 to remain fixed but B15 to move to B16 (When I paste it into L16).

I've been reading about absolute values but i'm not even sure i'm on the right track, it's way the hell over my head

Thanks

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).
Last edited by Hagar Delest on Wed Sep 01, 2010 10:48 am, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
bahnny

Posts: 2
Joined: Wed Sep 01, 2010 9:13 am

Re: Probably a simple question..

= (B\$2*B15)... or
= (\$B\$2*B15)...

For the ABSOLUTE reference.
The "\$" singn will to fix a coordinate of a cell-reference. The fixed coordinate(s) will not change when you copy-paste the cell content (the formula) into an another row or column.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.6 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.2 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 6576
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Probably a simple question..

HARR. Thanks I actually just figured it out and was on my way back to say thanks anyway.
Regardless thanks muchly zizi!

And for anyone reading this changing my above formula to:

= (\$B\$2*B4) + (\$C\$2*C4) + (\$D\$2*D4) + (\$E\$2*E4) + (\$F\$2*F4) + (\$G\$2*G4) + (\$H\$2*H4) + (\$I\$2*I4) + (\$J\$2*J4)

Was the resolution and works greatly.

Thanks again zizi.

edit:: Another question

I see this post here:
viewtopic.php?f=9&t=7810&p=36674&hilit=Pinning+cells#p36674

Except it doesn't work
I highly all the cells i want to freeze but then i scroll and I still cannot see them when I scroll down.
(It is checkmarked, though.)
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
bahnny

Posts: 2
Joined: Wed Sep 01, 2010 9:13 am

Re: [Solved] How to keep a static cell reference?

Read that solution again. You don't select the cells you want to freeze.

Select the top left cell that should not be fixed (or "frozen"), and then Window - Freeze. That should do it.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 2794
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [Solved] How to keep a static cell reference?

edit: Another question

viewtopic.php?f=5&t=166#p718
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.6 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.2 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 6576
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Probably a simple question..

bahnny wrote:HARR. Thanks I actually just figured it out and was on my way back to say thanks anyway.
Regardless thanks muchly zizi!

And for anyone reading this changing my above formula to:

= (\$B\$2*B4) + (\$C\$2*C4) + (\$D\$2*D4) + (\$E\$2*E4) + (\$F\$2*F4) + (\$G\$2*G4) + (\$H\$2*H4) + (\$I\$2*I4) + (\$J\$2*J4)

Was the resolution and works greatly.

Thanks again zizi.

Just as a matter of programming style, I'd recommend using a name rather than an absolute reference. The two approaches do the same thing but it is often easier to see what is happening in a spreadsheet if named cells/ranges are used especially if you come back to the spreadsheet six months from now.

See Insert > Names > etc
OpenOffice 4.1.4 Ubuntu 16.04 Xenial Xerus
jrkrideau
Volunteer

Posts: 3481
Joined: Sun Dec 30, 2007 10:00 pm

Re: [Solved] How to keep a static cell reference?

Also, with unbroken ranges the SUMPRODUCT() function may be an improvement:
=SUMPRODUCT(\$B\$2:\$J\$2;B4:J4) should do the same as the long formula.
jkrideau's suggestion to use of a named range would still be useful, and perhaps also easier to handle (one named range instead of nine named cells).

Again quoting jkrideau, it's much a matter of style. Whatever works best for you, you use.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 2794
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway