StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUse Excel/OpenOffice cell names within drag-completition
text
Body
copied!<p>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.</p> <p>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 <a href="https://stackoverflow.com/questions/733019/declaring-variables-in-excel-cells">Variable in Excel</a>, but when I use drag-complete, this cells are not adapted for the next column, like $D$1 does instead of D1.</p> <p>Ideas for solutions:</p> <ol> <li>Perhaps I can declare an row of cells as an array and index it with cellname(row), but how is this possible?</li> <li>Perhaps it is easier with a small vba script, but I would like to avoid this.</li> </ol> <p>Thanks in advance.</p> <p><strong>Edit 1:</strong></p> <p>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:</p> <p>My file looks like the uploaded short example <a href="http://www.lukas-drude.de/wp-content/uploads/example.ods" rel="nofollow noreferrer">example.ods</a>.</p> <p>I created cell names in the second column like "<code>size</code>". Then I have put a human readable formula like "<code>=size+step+thickness*weight</code>" in <code>C7</code>. When I drag-complete it to cells <code>D7</code> and <code>E7</code> like shown in <a href="http://www.lukas-drude.de/wp-content/uploads/example.png" rel="nofollow noreferrer">example.png</a>. I get of course the same result as in <code>C7</code>, because the cell names are used as absolute names like <code>$B$2</code> for example.</p> <p>How can I have human readable formulas applied to <code>D7</code> and <code>E7</code> without editing <code>D7</code> and <code>E7</code> by hand? When I use for C7 "<code>=C2+C3+C4*C5</code>", I can use the drag-completition of course.</p> <p>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 "<code>=C2+C3+C4*C5</code>".</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload