[Solved] How to keep a static cell reference?

Discuss the spreadsheet application

[Solved] How to keep a static cell reference?

Postby bahnny » Wed Sep 01, 2010 9:24 am

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
bahnny
 
Posts: 2
Joined: Wed Sep 01, 2010 9:13 am

Re: Probably a simple question..

Postby Zizi64 » Wed Sep 01, 2010 9:39 am

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; AOO4.0.1, LO3.6.4, LO4.2.2 on WinXPprof.SP3
Zizi64
Volunteer
 
Posts: 1783
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Probably a simple question..

Postby bahnny » Wed Sep 01, 2010 9:46 am

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?

Postby keme » Wed Sep 01, 2010 11:12 am

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.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby Zizi64 » Wed Sep 01, 2010 12:15 pm

edit: Another question

See the survival guide, please:
"One issue per thread:"
viewtopic.php?f=5&t=166#p718
Tibor Kovacs, Hungary; AOO4.0.1, LO3.6.4, LO4.2.2 on WinXPprof.SP3
Zizi64
Volunteer
 
Posts: 1783
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Probably a simple question..

Postby jrkrideau » Wed Sep 01, 2010 9:09 pm

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
AOO 4.0.1, Ubuntu 13.04
jrkrideau
 
Posts: 1910
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

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

Postby keme » Thu Sep 02, 2010 11:03 am

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.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests