I have two worksheets. I want to use the data in worksheet one to filter a column in worksheet two. I tried working this through the advanced filter option and had no luck. Can someone please explain how I can achieve this?
I do what you're describing routinely with a spreadsheet of financial data I download from my brokerage, then selectively import into a workbook of my own creation using the relatively new FILTERfunction and pointing to that downloaded spreadsheet as the array to be filtered.
So if in your own Excel you're able to enter =FILTER and start responding to the prompts, that means you have the current version. If you get an error message of some kind, you don't.
The first step, in other words, is to find out if FILTER works at all. I'd recommend upgrading if you don't have it. My Excel (on an iMac) is version 16.40, and I update regularly via a subscription to Microsoft Office 365.