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).
[Solved] How to keep a static cell reference?
[Solved] How to keep a static cell reference?
Last edited by Hagar Delest on Wed Sep 01, 2010 10:48 am, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
Re: Probably a simple question..
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.
= (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.
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.
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:
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.)
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
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.
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
Re: [Solved] How to keep a static cell reference?
See the survival guide, please:edit: Another question
"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.
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.
Re: Probably a simple question..
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.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.
See Insert > Names > etc
LibreOffice 7.3.7. 2; Ubuntu 22.04
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.
=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