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!

 

[R3Z]

The value you type in B2-C2-D2 need to be of the same type of the data to be filtered; if they are Numbers (NOT a string that display as numbers) then you cannot use & "*" as that transforms the criteria value to a String
If a column contains Strings (even if they look as numbers) then you need to format the filtering cell (B2-C2-D2) as Text before writing characters in it (or use the character "single quote" to force a number to be a string, eg '55 will enter the string 55)
If you are unsure whether a cell is a number or a string try formatting it as number with two decimals: if it display accordingly then it's a number, otherwise it's a text)

Hope things are a little bit more clear now

They're numbers you say I can't use the &"*" do i need to change the code?
I tried to change the new data and put the "single quote" before the number, but when a search on B2 for that number It doesn't filter.
For example: In my table I have the the B5 column is all numbers, when I search in B2 for the number it show me the results.
But now I add more rows to table and when I search on B2 for the data on the new rows it doesn't show the new one's only the oldest one's.
The oldest cells are a text I did what o told me to try to put the tow decimals and the Zeros don't show up, and if I change then from general to number or text they stop showing when I search them.
I'm I noob on excel, I'm really trying to explain the best I can so you could help me I really need to put this document to work.
Thank You very much for your time.

 

[Anthony47]

You cannot mix in the same column numbers and strings

In case a column contains numbers, the code has to be modified; the options are EqualTo, NotEqualTo, GreaterThan, LessThen and few other option less usual

 

[R3Z]

I'm trying to add the number "25414" to the table this is the "B" column.
When I search on B2 for the 25414 I don't get the filter to work and show the result.
Even when I delete the B2, to search other thing,

They're numbers you say I can't use the &"*" do i need to change the code?
I tried to change the new data and put the "single quote" before the number, but when a search on B2 for that number It doesn't filter.
For example: In my table I have the the B5 column is all numbers, when I search in B2 for the number it show me the results.
But now I add more rows to table and when I search on B2 for the data on the new rows it doesn't show the new one's only the oldest one's.
The oldest cells are a text I did what o told me to try to put the tow decimals and the Zeros don't show up, and if I change then from general to number or text they stop showing when I search them.
I'm I noob on excel, I'm really trying to explain the best I can so you could help me I really need to put this document to work.
Thank You very much for your time.

the row 25414 show as to small and I need to double click on the row to resize it.

24817	SER - 12
25414	PHO
24818	SER - 12

 

[R3Z]

You cannot mix in the same column numbers and strings

In case a column contains numbers, the code has to be modified; the options are EqualTo, NotEqualTo, GreaterThan, LessThen and few other option less usual

Can you help me with this modification?

 

[R3Z]

Can you help me with this modification?

Or can I find a way to format the new cell in this row to equals the others?
I try to format the cell to Text, or putting the "single quote" before the 25414 number but I still can't get the information to show up when I put the 25414 on B2.

 

[Anthony47]

Ok, I think you understand that the filter behave in strange way if you apply a criteria to a column that mixes text with numbers.
You also understand that Text and Numbers are different "type" of information, and what count is the type used by excel to mark that field. As a rule of thumb, if you format a cell that contain a "type number" as numeric with N decimals, then the cell will looks like the format you apply. Whereas if it doesn't change its appearance that it is a Text
Also, if your column is Text then your filter need to be a Text and you have options like EqualTo, DifferentThan, StartBy, Include, DoesntInclude. Whereas if your data is numeric, the key has to be numeric and the options are EqualTo, NotEqualTo, GreaterThan, LessThen and few others.
A different situation apply if the column contains Dates, and the options are totally different, as they

If you need that a certain column be forced to contain Text (even though you typein numbers, such as 123) then you better format that column as Text before start typing data; otherwise you must remember to use "Single Quotation Mark" in front of numbers (eg '123 to insert it as a text). Same approach for cells B2-C2-D2, where you insert the keys for that columns.

If this information is not enough then you should clarify which type of information you need to deal with (text, numbers, or even dates) and (in case of numbers or dates) how you would like to apply the filter, considered the available options for that type of data.

 

[Excel_User_10k]

If the solution proposed to kryptonian does not work for you it means that your question is different; and indeed it needs a different approach.
Rightclick on the tab with the name of the sheet with the table, select Display code
-the vba editor will open
-copy and past the following code in the blank frame at the right:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E6")) Is Nothing Then
    Range(Range("F10"), Range("F10").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("E6").Value & "*"
End If
End Sub

When you modify cell E6 then the filter will be applied to the column starting in F10 and will select only the names that "begin" with the string in E6; if E6 is empty all the names will be shown.

Bye

Hi Anthony47,
I have been looking for a similar function. I have managed to get this code to work with a couple of tweaks for my Sheet.

However, it only works by typing in the value and pressing "Enter". I setup a formula for this value to change based on other criteria. For a short time it was still updating. But now it isn't unless I clear the formula and just enter the value manually. I want this cell to be locked for other users and for it to update based on a "user ID" that they enter. Now I have gotten all of that to work. It is just that the filtering isn't updating.

Appreciate the help.

 

[Anthony47]

Hi Anthony47,
I have been looking for a similar function. I have managed to get this code to work with a couple of tweaks for my Sheet.
However, it only works by typing in the value and pressing "Enter". I setup a formula for this value to change based on other criteria. For a short time it was still updating. But now it isn't unless I clear the formula and just enter the value manually. I want this cell to be locked for other users and for it to update based on a "user ID" that they enter. Now I have gotten all of that to work. It is just that the filtering isn't updating.

I seem you are asking for some help about a situation that however I didn't understand clearly

Also, this discussion is now still open for the problem with User R3Z

For both these reasons I suggest that you open a new discussion where you clarify your current situation: how your data are organized, which type of filtering you would like to obtain, what has already been obtained and what is still to be resolved. You will receive for sure the help needed

 

[Excel_User_10k]

I seem you are asking for some help about a situation that however I didn't understand clearly

Also, this discussion is now still open for the problem with User R3Z

For both these reasons I suggest that you open a new discussion where you clarify your current situation: how your data are organized, which type of filtering you would like to obtain, what has already been obtained and what is still to be resolved. You will receive for sure the help needed

It is on the back end of this discussion and VBA code that you have made (and I am trying to use). Others have done the same thing and received answers, yet I am asked to create an entirely new discussion?

I may have overcomplicated it, sorry haha. Ok so in your formula where the Search Criteria is in E6. When the value of E6 is based on a formula instead of just "type and press Enter", the Macro does not work and the filter does not change. Do you know how I can fix it so that it still updates the filter if, for instance, the value of E6 changed based on the value of C2?

It is just the smallest little tweak I am sure but I cannot figure it out.

Thank you.

 

[Anthony47]

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.