Filtering across two worksheets

Hi,

I've turned to this forum because I can't seem to find the right search in Google to solve the issue I'm having - I hope you can help

I have created a workbook that is used by one of the teams here at my workplace during their weekly team meetings. It contains a list of client data, which, to make it readable on the screen used in these meetings, is split across two worksheets.

The first three columns in each sheet are identical; Column 1 contains a list of numbers from 1 to 5,000-odd (added after somebody changed the sort order on one of the sheets and got the data out of sync across the two), column 2 is First Name and column 3 is Last name. Columns 4 onwards contain data unique to each sheet.

My problem is, while a filter on one of the first three columns can be easily replicated in the other sheet, a filter on column 4 onwards is not easily copied. I would like to automate that. My first thought is that because the first three columns are consistent across the two sheets, a filter based upon the data showing in one of those columns would be the easiest option (i.e. column 1 in Sheet 1 is only showing 1,5,9..., so filter column 1 in Sheet 2 based on that list, and vice versa). However there doesn't seem to be an option using Autofilter, to filter based upon a list in another column. I looked at using an advanced filter, but I don't understand enough about how these work to reach a solution, and I suspect it may not be possible there either.

I have attached an example workbook to give an idea of how everything is set up. I'm happy to use VBA if necessary to get the desired result, but the less the users of the spreadsheet have to do to filter across the two sheets the better; they struggle with Excel as it is.

Thanks in advance,

Hamish

Thanks Kevin, I hadn't thought of including all of the data on both sheets, but this seems like it should work. Now I just need to figure out the macro Looks like I'll be learning something new this afternoon!

Here are 2 options (both included in attached workbook)

Something that may work for you is to just use the one sheet and hide the non-required columns each time after filtering (attach each macro to a button for ease of use)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Or going back to the original idea, filter the data and then run this macro to divide the data into 2 sheets for viewing

Please Login or Register  to view this content.