Yet more Excel help needed please

[DoubleCheese]

I've got two spreadsheets; one which lists of all our clients and their data on it and another that just lists some data but is just the currently open/active accounts. I'm not interested in the old and closed accounts off the first sheet.

I want to filter the first spreadsheet by having it compare and exclude everything but the active ones from the second sheet. Can anyone help with a formula that will look up the clients reference number and exclude the ones that aren't on the second list?

Thanks.

 

[Participant]

In the old sheet:

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$500,2,false)=true),0,1)

Where A1 = the client reference number, account number, or whatever unique reference you have, and sheet 2 = the list of currently active data.

Note that this formula assumes both data sets are in the same workbook but on different worksheets. From what you've said, they might be in separate workbooks, so try

=IF(ISERROR(VLOOKUP(A1,'[xxx.xlxs]Sheet1'!$A$1:$B$500,2,false)=true),0,1)

instead if this is the case.

Then Autofilter by "1" on the old sheet.