Auto filter a table based on the value of a cell in a different sheet

[kryptonian]

In Sheet1, I type in the following info: cell A1=EmployeeNumber, A2=Date, A3=Reason, A4=Tag. The info typed here are then saved to a database in Sheet2, i.e., column A=EmployeeNumber, column B=Date, column C=Reason, column D=Tag; this is done via macro when a SAVE button is clicked.

What I would like to happen is to filter the data in Sheet2 automatically depending on the data that I typed in A1, i.e., if I typed in 123456 in A1, I would like the table in Sheet2 to be filtered and only show all data for 123456.

Is this possible? Thanks in advance!

 

[Excel_User_10k]

The reason I asked you to create a new discussion is because this discussion is OPEN with R3Z (his problem has been discussed in several messages and we are trying to get to some results), and mixing message to/from you and to/from R3Z would simply create too much confusion. Other user queued their request to this discussion but then the previous question had already been solved.

HOWEVER I now understand your point: it works if the filter is typed in E6; it doesn't work if the filter is generated by a formula.
But this is the way the event Worksheet_Change works, and we used that event to trigger the filter.
You might use the Worksheet_Calculate event instead:

Private Sub Worksheet_Calculate()
Range(Range("F10"), Range("F10").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("E6").Value & "*"
End Sub

BUT this event triggers too often and that could dramatically slowdown all your workbook.

It would be better that you examine the formula in E6 and find which manual change determine the value in E6 and use that cell address to validate the range autofilter. Ie, not If Not Application.Intersect(Target, Range("E6")) Is Nothing Then but

If Not Application.Intersect(Target, Range("TheCellWithTheManualChange")) Is Nothing Then

I don't know whether this cell is C2

But please, if this basic suggestion doesn't close the problem either wait that the discussion with R3Z get to an end or open a new discussion.

It has fixed it!! See, something simple haha.

I now have another issue which may be to do with the Macro too, but I will create another discussion for it.

Just wanted to let you know it worked (and anyone else who may see this) and to say thank you

 

[R3Z]

Hi Anthony47, I followed your suggestion and created a new discussion and was able to get the help that I needed and got some way to get this to work but it still needs some more tweaks but I think it will be possible to do what I need.

Thank you very much for your time and help.

 

[Anthony47]

Glad to know that you got satisfactory help on your question

 

[JC500]

Hi,
I happened to stumble across this article which I believe may be my saviour, but for some reason the code appears not to work (Note: I'm not a VB expert or even a beginner, so I may get the wording wrong so apologies in advance.

Background:
I have 2 x sheets, called:
Master: This is a front menu page that all users see that contains an image (4 Pillars) as per the example:

. Each Pillar is associated within a different cell and name i.e.,
B11 = Business, D11 = Capability, F11 = Invocation & G11 = Testing
Standards: This sheet contains a list of rows that are filtered primarily by the Pillar type:

Note: I can associate each pillars in the Master sheet to a link to the "Standards" sheet, based upon defined names e.g., Business, Capability, Invocation etc. However, this feature does not automatically filter the data to show only the selected pillar, infact it show the current pillar as well as the remaining pillars.

The Ask:
When the user selects one of the Pillars from the Master sheet e.g., D11 = Capability can the "Standards" sheet be automatically filtered so that only the the relevant selection is shown i.e., Capability?

 

[Joe4]

Hi,
I happened to stumble across this article which I believe may be my saviour, but for some reason the code appears not to work (Note: I'm not a VB expert or even a beginner, so I may get the wording wrong so apologies in advance.

Background:
I have 2 x sheets, called:
Master: This is a front menu page that all users see that contains an image (4 Pillars) as per the example: View attachment 108273. Each Pillar is associated within a different cell and name i.e.,
B11 = Business, D11 = Capability, F11 = Invocation & G11 = Testing
Standards: This sheet contains a list of rows that are filtered primarily by the Pillar type: View attachment 108276

Note: I can associate each pillars in the Master sheet to a link to the "Standards" sheet, based upon defined names e.g., Business, Capability, Invocation etc. However, this feature does not automatically filter the data to show only the selected pillar, infact it show the current pillar as well as the remaining pillars.

The Ask:
When the user selects one of the Pillars from the Master sheet e.g., D11 = Capability can the "Standards" sheet be automatically filtered so that only the the relevant selection is shown i.e., Capability?

Re-posted here: Automatically setting filters based upon user selection criteria

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.