Filter in Excel 2013

Hi,

In excel 2013 I applied a "filter" so that you can eliminate certain things & then saved the file..........over the weekend my spreadsheets sheet 1 updated but sheet 2 didnt. So, the filter didn't update/ hold its criteria, I had to go in and reapply filter.

Is there a way for the filter to auto update?

Sheet 1 has data, Sheet 2 is just linked to sheet 1 with a filter for 1 particular person
when data is added to sheet 1, its doesn't auto update in sheet 2.

Thx

PS. Shg had suggested (view->custom view) but that isn't working

Hi,

Quickest way to do so is create a short macro which triggers the reapply of the filter every time you activate the sheet.
Check the attachement and see if this helps.

Originally Posted by joris moerings

Hi,

Quickest way to do so is create a short macro which triggers the reapply of the filter every time you activate the sheet.
Check the attachement and see if this helps.

Thanks for the suggestion

Originally Posted by FDibbins

If you put your criteria in a cell of its own - I used D2), then you can use this ARRAY formula to pull in what you want...
=IFERROR(INDEX(Sheet1!D:D,SMALL(IF(Sheet1!$D$3:$D$86=Sheet2!$D$2,ROW(Sheet1!$A$3:$A$86)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Change the bolded range to pull from other columns

Thanks for the suggestion

Originally Posted by joris moerings

Hi,

Quickest way to do so is create a short macro which triggers the reapply of the filter every time you activate the sheet.
Check the attachement and see if this helps.

Can you show me or tell me how to create the macro?

Originally Posted by joris moerings

Hi,

Quickest way to do so is create a short macro which triggers the reapply of the filter every time you activate the sheet.
Check the attachement and see if this helps.

I guess I am trying to figure out how you created this macro and copy/edit for my needs. OR create a new one so that I use it going forward for the other spreadsheets

Hi,

The macro isn't that complicated. It basically just reapply an already existing filter but by combining it with specific excel event (in this case the activation of the worksheet) it gives the macro it's strength. I placed the code on a sheet module which can be shown by right click on the Sheet2 Tab and choose "View Code".

Please Login or Register  to view this content.

If you want to know more about EVENTS and how to combine with VBA i recommend the pages regarding VBA and EVENTS by Chip Pearson These provide a lot of usefull insights.

Hopes this helps.