The Naked Scientists

The Naked Scientists Forum

Author Topic: Is it possible to treat a cell as a constant in Excel/OpenCalc?  (Read 10931 times)

Offline CliffordK

  • Neilep Level Member
  • ******
  • Posts: 6321
  • Thanked: 3 times
  • Site Moderator
    • View Profile
Periodically when I'm cutting and pasting in Excel/OpenCalc, I need to keep one cell constant, and vary another cell.

ABCDEF
1   Constant ValueFormula      EveryRow  ModFormula 
2   51=A2+B25=D2+B2
32=A2+B35=D3+B3
43=A2+B45=D4+B4
54=A2+B55=D5+B5

So, in the above formula, I want to keep A2 constant, and use it in all the formulas in column C.
When I cut and past the formula in column C2, it automatically increments the cells for both columns A&B. 

One possibility is to create a column D, and put my constant value in every cell.
Then I can cut and past my formula E2.  But, that means creating a whole bunch of unnecessary cells.

However, is there a more elegant way to do it, so it automatically increments the cells in one column, but not the next?


 

Offline CliffordK

  • Neilep Level Member
  • ******
  • Posts: 6321
  • Thanked: 3 times
  • Site Moderator
    • View Profile
ABCDEF
1   Constant ValueFormula      EveryRow  ModFormula 
2   51=$A$2+B25=D2+B2
32=$A$2+B35=D3+B3
43=$A$2+B45=D4+B4
54=$A$2+B55=D5+B5

Ahhh.
Found it.
I thought I had looked for the answer earlier.
But, this time the search was more fruitful.

I need to use $A$2 instead of A2.
then cut and paste normally.
 

Offline graham.d

  • Neilep Level Member
  • ******
  • Posts: 2208
    • View Profile
Darn. I was going to tell you the answer but when I scrolled down you'd done it!
 

Offline Geezer

  • Neilep Level Member
  • ******
  • Posts: 8328
  • "Vive la résistance!"
    • View Profile
Don't forget you can also use A$2 and $A2.

I use the Open Office variant ('cos it's free!) but I hate the way it insists on automatically filling series in columns. I don't think there is a way to turn it off, although you can prevent it by hitting shift or alt or something.
 

Offline imatfaal

  • Neilep Level Member
  • ******
  • Posts: 2787
  • rouge moderator
    • View Profile
f4 in excel and shift-f4 in openoffice.  scrolls through $A$2 A$2 $A2 A2 - so hold both col and row, just row, just column, neither
 

Offline imatfaal

  • Neilep Level Member
  • ******
  • Posts: 2787
  • rouge moderator
    • View Profile
the other way you can do it is by naming the cell.  in excel type a name in cell A1 and right click in cell A2 "define name" - it will default to naming the cell from the cell on the left.  This name will now stick to that cell (you can look in the name box in the top right hand corner - immediately left of where you enter data) and if you use that cell as part of a formula it will no longer change if you copy to a range - and it will use the name in all formulas when inspected. 

For complex formulae (some of my spreadsheets have formula 5 lines long) its almost impossible to follow unless you name stuff.  You can get the names to follow across a sheet or across the whole workbook: =Sheet4!E18 is very hard to follow where as =IFOprice is a piece of cake
 

Offline Geezer

  • Neilep Level Member
  • ******
  • Posts: 8328
  • "Vive la résistance!"
    • View Profile
Gawd! I hope I never have to get that good with it!
 

Offline CliffordK

  • Neilep Level Member
  • ******
  • Posts: 6321
  • Thanked: 3 times
  • Site Moderator
    • View Profile
the other way you can do it is by naming the cell.  in excel type a name in cell A1 and right click in cell A2 "define name" - it will default to naming the cell from the cell on the left.
Thanks,
Unfortunately there are some tricks I haven't picked up over time, as I often find myself using brute force to get the job done, and often don't need the end result to look pretty.  This has been enlightening.

In OpenOffice/LibreOffice/OpenCalc/LibreCalc, it is through the menu.

Insert-Names-(Define/Manage/etc).

I could see the advantage of this when using macros.  Now, if OpenOffice & Excel would just use the same macro language.
 

Offline Geezer

  • Neilep Level Member
  • ******
  • Posts: 8328
  • "Vive la résistance!"
    • View Profile
My problem is I use it infrequently, so I have to re-learn everything I forgot since I last used it.

But here's a funny (peculiar) thing. I can still remember all  Z80 assembly language and lot of the machine code, and I haven't written a single line of the stuff in thirty years! It must be in "non-volatile" memory.
« Last Edit: 28/04/2012 20:37:34 by Geezer »
 

Offline CliffordK

  • Neilep Level Member
  • ******
  • Posts: 6321
  • Thanked: 3 times
  • Site Moderator
    • View Profile
My problem is I use it infrequently, so I have to re-learn everything I forgot since I last used it.
I find myself having to look up functions like mod, div, trunc, & etc for each system I use, as well as remembering if I need one equal sign(=), or two(==) .  I suppose if syntax was standardized, then there would only be one programming language, but it is a pain that there are so many different implementations. 

I should be able to remember the $ thing as it is also used in the charts.
Named cells in Excel/OpenCalc sound like a great idea, but perhaps I have few enough spreadsheets that they would have a limited lifespan, especially since the naming interface seems to be a bit cumbersome (and far too hidden), at least in OpenCalc.
 

Offline nicephotog

  • Sr. Member
  • ****
  • Posts: 387
  • Thanked: 7 times
  • H h H h H h H h H h
    • View Profile
    • Freeware Downloads
 

The Naked Scientists Forum


 

SMF 2.0.10 | SMF © 2015, Simple Machines
SMFAds for Free Forums