In OpenOffice-Calc, using the advanced filter, how can I compare one column to another?

As an example, I need to filter for items that have less than a 30 days supply. Using the column name Test, my criteria are below.


I have column headers of BOH (Bottles on Hand), Thirty (how many bottles are sold in 30 days) and a new column Test. I get the following error.

Range does not contain valid query.

Why is the formula returning this error message?

  • What version of Open Office are you using? – Ryan Gates Jun 21 '13 at 18:25
  • OO 3.4.1, Mac OS 10.8.3 – Paul Bressler Jun 21 '13 at 19:36

I don't believe that you can reference columns by their name the way that you are in your question.

You need to reference individual cells by column(A-Z...) followed by row(1-100...). Take the following example:

enter image description here

The formula ends up being the following:

  • =A2<=B2
  • =A3<=B3
  • =A4<=B4

And so on...

I did this with LibreOffice, but you should get the same behavior out of OpenOffice.

  • Thank you for your help. I was able to do that (create a new column for the test and set the test equal to true) using names. Unfortunately, I need to test for several other conditions as well. Using that method I actually need to create columns in the spreadsheet. What I was hoping to do was set the tests in the criteria, and that isn't working. I shouldn't have to have a full spreadsheet column for a test on data already in the spreadsheet. – Paul Bressler Jun 24 '13 at 21:43
  • @PaulBressler Can you add those conditions to your question? – Ryan Gates Jun 24 '13 at 22:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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