Use Excel/OpenOffice cell names within drag-completition

Question

I have a lot of measured values in each column. I use formulas under those values to calculate with them. I always edit the first column and drag-complete (small square in the south-east of the selected cell) to change the other columns, too.

It was fine while dealing with 5 values, but with 20 values in a formula, things are getting complicated. I would like to use cell names, as I found in Variable in Excel, but when I use drag-complete, this cells are not adapted for the next column, like $D$1 does instead of D1.

Ideas for solutions:

1. Perhaps I can declare an row of cells as an array and index it with cellname(row), but how is this possible?
2. Perhaps it is easier with a small vba script, but I would like to avoid this.

Thanks in advance.

Edit 1:

I was afraid that my question is not that clear. I will try to clearify it with the following files. Thus the Excel-Tag is removed, I uploaded an ods-File:

My file looks like the uploaded short example example.ods.

I created cell names in the second column like "size". Then I have put a human readable formula like "=size+step+thickness*weight" in C7. When I drag-complete it to cells D7 and E7 like shown in example.png. I get of course the same result as in C7, because the cell names are used as absolute names like $B$2 for example.

How can I have human readable formulas applied to D7 and E7 without editing D7 and E7 by hand? When I use for C7 "=C2+C3+C4*C5", I can use the drag-completition of course.

I hope this is more clear now. I guess this is some basic functionality, but I just don't know how to describe it well. Perhaps you have a similar idea to have it more readable than "=C2+C3+C4*C5".

Answer 1

This works in OpenOffice.org Calc as well as in LibreOffice Calc, but it's crucial to define the cell names for every column that will be evaluated by the formula. Here's a step-for-step solution, based on the example document:

1. Start with a spreadsheet containing just the values together with row and column heads:

2. Create the cell range names:

   a. Select the data range including the column holding the row names (OOo will use those Strings as names in the next steps):

   

   b. Select "Insert -> Names... -> Create":

   

   c. Select "Left Column" to name the rows based on the content of the first column:

   

   Result: four names, one for each row, named as desired:

   

3. Create the formula for the first data row (here incomplete, demonstrating OOo's tooltips):

4. Drag-complete for all other data rows, giving the final result (with Tools -> Detective -> Trace precedents activated - the detective points to the array's first column, but the formula will use the values of the current column):

   

Answer 2

You can use relative references in Names, it easier to use R1C1 mode for this:

Define a Name Size with a RefersTo of =R2C Then wherever you use the name Size in a formula it will refer to the current column and row 2