Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'd like to refer to an entire column, like you can in Excel by using A:A. I found a discussion on the openoffice.org forums which is a few years old, and suggests there is/was no neat way to do it. The options presented are

  1. Use A1:A65536.
  2. Use OFFSET($A$1;0;0;65536;1) as the previous range may get altered if you insert or remove rows.
  3. Use Data -> Define Range... to name the column range (but which for me still just equates to $A$1:$A$1048576).

These approaches seem over-complicated and still don't achieve my goal perfectly. Does anyone know of a simpler way to refer to an entire column?

share|improve this question
1  
What is your goal and how doesn't (3) achieve it perfectly? Do you know that Excel's A:A isn't just syntactic sugar for A1:Amax and that the internal representation is different? –  msw Jul 6 '10 at 10:05
1  
There's no single goal here; there's numerous situations I find where I'd like to reference a column. The way that (3) isn't perfect functionally is that it suffers from the same problem as (1): the range changes as rows are deleted. What I want is a quick way to refer to (necessarily) an entire column. Hope that clarifies. Cheers. –  Andy Jul 6 '10 at 10:35
5  
I'm looking for a quick way. None of these approaches are as quick and simple as I'd like. If on top of that they don't even achieve what you want perfectly, you might post a question on superuser asking for a better way;) It's hard to imagine a situation where 3 would cause problems, but I don't want to rely on my imagination! I'd prefer something that's just semantically sound - I want a whole column. I do appreciate your time, but so far you're not actually helping make progress, but rather challenging the validity of the question. It's a simple Q, deserves a simple A (perhaps 'no!'). –  Andy Jul 6 '10 at 15:05
2  
I agree with you entirely. I just merely wanted to know if it was possible with Calc, and open the question up to a wide audience! I'm not working on a project and at an impasse or anything. –  Andy Jul 7 '10 at 8:35
1  
When I select an entire column in LibreOffice 3.5.2, it calls it A1:A1048576, not A1:A65536. If you try to use A1:A1048576 in a chart, it just shrinks it to something like $Sheet1.$A$2:$A$26. Then you add more things to the column and they aren't included in the chart. It would be really nice if we could just say A:A to mean "use everything in this column". –  endolith Apr 12 '12 at 14:03

3 Answers 3

You can make it less complicated by naming the column. Select the column and name it via the Name Box near the upper, left corner of calc. You can't use A:A but AA will work. This is similar to #3 above. It seems to handle insert/delete of rows fine but I didn't test it all that much. Make a template with every column named and start there when you need the feature. That seems to be the approach requiring the least effort.

However, this still doesn't work like I would expect. It's the same as using $A:$A in excel. If you copy the formula to a new column, it will continue to reference column A instead of being translated to a new column by the offset of the copy. Makes things really cumbersome if you're used to this feature.

In the end, to duplicate the functionality precisely, looks like we have to use A$1:A$1048576.

share|improve this answer

Go to some cell (let's say, B1) and insert the value "A1048576" on it.

Then, to reference all values from the A column, simply enter something like

=max(a1:index(b1))

and doesn't matter if you delete some rows from the range, the b1 content always assure that you are referencing the last cell from the A column.

note: in my Calc the formula would be =máximo(a1:índice(b1)), so I'm not sure that in English the function name is really index.

share|improve this answer

Was looking for the answer to this myself and found another option along the way..

The answer referring to naming the column was unclear to me when I read it..

From this beginner's view it was trying to say that when you select the whole row, in the drop down box in the upper left hand corner it says to the effect A1:A1048576.. this you rename to give the column a name.. and use that in place of Excel's A:A

This just kept causing me issues though, and like the way I found better, if it's applicable to whoever else finds this post's needs.

Instead, in say A1.. I have a title.. Total we'll say.. with all the values beneath it.

I just used that instead of renaming the column and worked basically the same without as many issues.. though in cases of Title characters that use functions such as / (which was an issue I was having) you need to remember to put apostrophes before and after ie.. 'Km/Gal' confused Calc until I realized I needed them...

Had to name it something normal first (kmgal) and then change it after I put in the formula to calculate the average.. In which Calc corrected it for me, and then I noticed what it did and fixed them myself.

So simply, in the use I used...

=ROUND(AVERAGE('Km/Gal');3)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.