[Solved] How to keep a static cell reference?

Discuss the spreadsheet application
Post Reply
bahnny
Posts: 2
Joined: Wed Sep 01, 2010 9:13 am

[Solved] How to keep a static cell reference?

Post by bahnny »

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 :P

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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Probably a simple question..

Post by Zizi64 »

Use a "$" sign in formula:

= (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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
bahnny
Posts: 2
Joined: Wed Sep 01, 2010 9:13 am

Re: Probably a simple question..

Post by bahnny »

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:
http://user.services.openoffice.org/en/ ... lls#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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post by keme »

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 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

edit: Another question
See the survival guide, please:
"One issue per thread:"
http://user.services.openoffice.org/en/ ... t=166#p718
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Probably a simple question..

Post by jrkrideau »

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
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post by keme »

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 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Post Reply