Naked Science Forum
Non Life Sciences => Geek Speak => Topic started by: CliffordK 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?
-
| 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.
-
Darn. I was going to tell you the answer but when I scrolled down you'd done it!
-
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.
-
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
-
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
-
Gawd! I hope I never have to get that good with it!
-
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.
-
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.
-
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.
-
Have you ever done WSH scripting?
Take a look at these.
http://stackoverflow.com/questions/10040289/specifying-cells-in-excel-using-tcom
http://msdn.microsoft.com/en-us/library/aa221581(v=office.11).aspx