Dynamic named range & advanced filtering

Question

Hello. Still looking for the best options for my spreadsheet. I tried a macro code that another user helpfully provided, but it didn't work (probably because I messed something up :/). Anyway, hoping someone can help again!

I have 16 columns of data on 5 sheets. The column headers are all the same. I would like to either extract or filter the data (ideally onto a different sheet) based on criteria in Column I (TR, TE or ST). I also need, at a minimum, the corresponding data in Columns C, M, O, and P to be extracted/filtered with it (if not possible, the whole row of data works). However, the criteria in Column I will change (eg: TR instead of TE). Also, I will be adding or deleting rows in the future, and would like the extracted/filtered data to automatically update.

I tried a dynamic named range & advanced filtering, copying to another place - but when I changed the data in Column I, it didn't re-filter. Is there a way to automatically do this?

Thanks again!

Answer 1

Hi,

No filter, be it Simple, Custom or Advanced, will work across sheets.  Therefore, you will have to first consolidate data from 5 sheets into 1.  Once this is done, we can via a simple advanced filter macro, extract data to another sheet.

To consolidate data from 5 sheets into 1 (which will update when new rows of data are added), you may refer to this link on  my website - http://www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/.  In point 18, select Table instead of Pivot Table.

First just try to consolidate data from 5 sheets to 1 sheet and once that works OK, we will frame the advanced filter macro.

Hope this helps.