Calc: select rows which contain a number from a list of numbers which is in a column in a different sheet?

[PetrBřeň]

Hello everybody.

I am relatively new to these things so please have patience with me.

I need to select rows which contain a number from a list of numbers which is in a column in a different sheet, like so:

Sheet 1:

Name	        Surname		ID number

Peter	        Black		456321
Tom	            Smith		789132
Jane	        Jones		228589
Mary	        Simmons		375941
Lucy	        Carlson		161890
Owen	        Miles 	    297784

Sheet 2:

ID numbers

789132
375941
161890

I need to select the matching rows in Sheet 1 (the ones beginning with Tom, Mary and Lucy in this example).

I was hoping I could use the Standard filter function for this, however the Value field in it only seems to accept a static value, not a range like Sheet2.A1:A3 which is what I would need.

What is your suggestion? I guess I have to write a macro for such a thing?

Thank you very much and have a nice day

Petr Břeň

(P.S. I’m using LibreOffice 4.4.5.2 on Fedora 22 x86_64, if it’s of any importance.)

Edit:
I guess I described the issue in an overtly complicated manner.

I basically need to show rows which contain a number from a separate list of numbers.
Names of the columns don’t matter, the fact that it is a number doesn’t matter, just show rows matching a string from a list of strings.

It should work just like this: if a row in the first sheet contains a number from the list in the second sheet, show the row. If a row in the first sheet doesn’t contain a number from the list in the second sheet, do not show the row.

Thank you very much

Petr

[PYS]

Hi

You can use the Data Filter Advanced filter that allow to define the filter criteria in another sheet. The only constraint is to use the same column name as in the Filter.ods attached.

You can go further and facilitate its use by defining a name for the criteria: Insert Name Define, clik Range Options and check Filter:

When you start Data Filter Advanced filter you can select your filter name:

Regards

[PetrBřeň]

Thank you very much! So simple and elegant!