# The Naked Scientists Forum

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

#### CliffordK

• Neilep Level Member
• Posts: 6321
• Thanked: 3 times
• Site Moderator
##### Is it possible to treat a cell as a constant in Excel/OpenCalc?
« on: 27/04/2012 00:02:54 »
Periodically when I'm cutting and pasting in Excel/OpenCalc, I need to keep one cell constant, and vary another cell.

 A B C D E F 1 Constant Value Formula EveryRow ModFormula 2 5 1 =A2+B2 5 =D2+B2 3 2 =A2+B3 5 =D3+B3 4 3 =A2+B4 5 =D4+B4 5 4 =A2+B5 5 =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?

#### CliffordK

• Neilep Level Member
• Posts: 6321
• Thanked: 3 times
• Site Moderator
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #1 on: 27/04/2012 00:16:36 »
 A B C D E F 1 Constant Value Formula EveryRow ModFormula 2 5 1 =\$A\$2+B2 5 =D2+B2 3 2 =\$A\$2+B3 5 =D3+B3 4 3 =\$A\$2+B4 5 =D4+B4 5 4 =\$A\$2+B5 5 =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.

#### graham.d

• Neilep Level Member
• Posts: 2208
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #2 on: 27/04/2012 09:04:05 »
Darn. I was going to tell you the answer but when I scrolled down you'd done it!

#### Geezer

• Neilep Level Member
• Posts: 8328
• "Vive la résistance!"
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #3 on: 27/04/2012 21:01:48 »
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.

#### imatfaal

• Neilep Level Member
• Posts: 2787
• rouge moderator
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #4 on: 28/04/2012 18:30:25 »
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

#### imatfaal

• Neilep Level Member
• Posts: 2787
• rouge moderator
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #5 on: 28/04/2012 18:42:12 »
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

#### Geezer

• Neilep Level Member
• Posts: 8328
• "Vive la résistance!"
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #6 on: 28/04/2012 19:32:04 »
Gawd! I hope I never have to get that good with it!

#### CliffordK

• Neilep Level Member
• Posts: 6321
• Thanked: 3 times
• Site Moderator
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #7 on: 28/04/2012 20:07:37 »
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.

#### Geezer

• Neilep Level Member
• Posts: 8328
• "Vive la résistance!"
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #8 on: 28/04/2012 20:34:08 »
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 »

#### CliffordK

• Neilep Level Member
• Posts: 6321
• Thanked: 3 times
• Site Moderator
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #9 on: 28/04/2012 22:09:58 »
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.

#### nicephotog

• Sr. Member
• Posts: 387
• Thanked: 7 times
• H h H h H h H h H h
##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #10 on: 17/05/2012 08:54:51 »

#### The Naked Scientists Forum

##### Re: Is it possible to treat a cell as a constant in Excel/OpenCalc?
« Reply #10 on: 17/05/2012 08:54:51 »

Login
Login with username, password and session length