Can the advanced filter List range be set to look at data on multiple worksheets?

I am working on a database that has the potential to house LOTS of data (Individual Fish lengths from years of surveys). I currently have the workbook saved as a Macro-enabled so I know i have a little over 1 million rows to work with, but in time that will not be enough.... Is there a way to set the advanced filter feature to look through multiple worksheets? Or do I need to look at using an access database to house this.

Thanks!

Tyler

Excel isn't meant to be used as a database.

Because of this, I would recommend using Access over Excel.

Originally Posted by MarvinP

Hi Tyler,

If you had a workbook for each year in the same folder and use Power Query (Get & Transform) to filter the data before it got to your Summary Workbook, you could do this problem. I'm working with a 3 million row database (in .txt format) and using PQ only pull in a subset of the data and it is instant. Read about Power Query using many files in the same folder.

http://excelunplugged.com/2015/02/10...n-power-query/
http://excelunplugged.com/2016/05/28...e-excel-files/
https://www.myonlinetraininghub.com/...-from-a-folder

Thanks for the info! I will do some research!