Auto Update of Filters

%3CLINGO-SUB%20id%3D%22lingo-sub-2152952%22%20slang%3D%22en-US%22%3EAuto%20Update%20of%20Filters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152952%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20combo%20box%20on%20page%201%3C%2FP%3E%3CP%3EOn%20page%202%20the%20value%20of%20the%20selection%20from%20page%201%20is%20in%20a%20cell.%3C%2FP%3E%3CP%3EOther%20cells%20then%20refer%20to%20it%20and%20return%20either%201%20or%200%20depending%20on%20the%20value%20selected.%26nbsp%3B%3C%2FP%3E%3CP%3EThese%20values%20are%20in%20a%20column%20and%20the%20auto-filter%20is%20set%20to%201.%3C%2FP%3E%3CP%3EEach%20time%20I%20select%20a%20value%20in%20page%201%20and%20go%20to%20page%202%20I%20need%20to%20press%20the%20auto-filter%20to%20get%20the%20data%20to%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20this%20to%20change%20automatically%20when%20the%20selection%20is%20made%20on%20page%201.%26nbsp%3B%20Excel%20is%20configured%20to%20auto-calculate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20can%20anyone%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EMarilyn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2152952%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153246%22%20slang%3D%22de-DE%22%3ESubject%3A%20Auto%20Update%20of%20Filters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153246%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973560%22%20target%3D%22_blank%22%3E%40CookysLady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%20Even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%20This%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a combo box on page 1

On page 2 the value of the selection from page 1 is in a cell.

Other cells then refer to it and return either 1 or 0 depending on the value selected. 

These values are in a column and the auto-filter is set to 1.

Each time I select a value in page 1 and go to page 2 I need to press the auto-filter to get the data to change.

 

I would like this to change automatically when the selection is made on page 1.  Excel is configured to auto-calculate.

 

Please can anyone help me with this?

 

Thank you,

Marilyn

3 Replies

@CookysLady 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

* Knowing the Excel version and operating system would also be an advantage.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

 

@CookysLady 

 

Try this. Right click on your worksheet tab that has your autofilter, select view code, and past this into the code window that appears. Then, close the vba window.

Private Sub Worksheet_Calculate()
     On Error Resume Next
     Application.EnableEvents = False
     Me.AutoFilter.ApplyFilter
     Application.EnableEvents = True
End Sub

 

@JMB17 

 

Thank you so much. 

 

I ended up creating blocks of data for each filter option then using a lookup formula.  It worked OK but what a job to do!