Apache OpenOffice Community Forum

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).

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.

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.)

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.

edit: Another question

See the survival guide, please:
"One issue per thread:"
http://user.services.openoffice.org/en/ ... t=166#p718

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

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.