Calc - How to filter rows based on another sheet

Dear Calc Experts,

I’m new to this forum and I’m glad to be with you all.
A colleague of mine sent me Calc file called “Products” with multiple columns and the first column is “Code”. Now he sent me another Clac file called “Filtered items” containing “Code” only column with reduced rows and he asked me to filter the “Products” file based on values from “Filtered items” file. How do I perform this?

Please let me know if further clarification needed.

Thank you so much.

Kind regards,

Hi, using the functions INDEX/MATCH could be a way doing this, are you able to upload your files, or atleast a sample?


to get that done - do the following:

  • Copy the sheet “Filtered items” containing “Code” only column at the end of “Products” (e.g. name it Filter as in my example file attached below)

  • Assure that the heading Name of Code columns are the same (in my ecample “A1=Code” on both sheets)

  • In sheet Filter select all values in column Code (incl. – important – header)

  • Sheet -> Named Ranges and Expresssions -> Define .... and

    • Provide a Name: (e.g. CODEFILTER)
    • Click on Range Options and set [x] Filter
    • Click Add button

    (see screenshot below, showing the related dialog)

  • In sheet containing your products (called Products in my sample file) select all columns containing data

  • Tools -> More Filters -> Advanced and from dropdown labeled -undefined- select the name you defined above ( (e.g. CODEFILTER)

  • Click button OK

Your data should be filtered now by the entries defined in column Code of table Filter.

  1. Sample file: FilterProductByCode.ods
  2. Screenshot:

image description

Want read more: LibreOffice Help - Applying Advanced Filters

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.


Thank you so much for your valuable reply. I followed all steps but they don’t filter my sheet, although I tried on the sample sheet you provided and it works fine. Do you think the number of rows is an issue? I’m trying to filter 15000 out of 22000 rows.

Do you think the number of rows is an issue?

Can’t imagine - but it would be a simple test. Just shorten column Code on sheet Filter to a few entries only. But I’d assume a slight deviation from the important things and my first thought goes to Header. Advanced filter works based on Header columns and their names - and the they must exactly match by name - check for invisble characters in both of the columns headed Code (spaces, etc.).

Is your Range or formula expression really showing 15.000 lines (e.g. Filter.$A1.$A$15000) ?

Many thanks for all your support. I don’t know, but now it works just fine :slight_smile:
Can’t thank you enough.

Thanks a lot. This solution worked for me perfectly.