Hello,
I have a table and would like to know how to filter the results based
on what is entered in a cell

For example from a table below, I want to be able to input in cell A1
"Part123" (no quotes) and the list autofilters all the results. If I
want to autofilter based on the part description, I would like to
enter "Part345" in cell B1 and all filtered results appear.
Any ideas?
Much appreciated for your help!


A B C
100 Part123 Part123 Description Qty
101 Part345 Part345 Description Qty
102 Part678 Part678 Description Qty

You could record a macro when you do it manually:

Show all the data (if you want)
Filter the column that you want using a string
Modify that code to use the cell's value instead of your string
Add a button from the Forms toolbar adjacent to the cell
Assign the macro to this button

Some place in your code, you'll have a line that includes the criteria:

...., Criteria1:="YourString", ...

You'll want to change it to look like:

...., Criteria1:=activesheet.range("A1").value, ...

If/when you need help, just post back with what you have.


On 10/19/2011 19:10, Tony D wrote:
> Hello,
> I have a table and would like to know how to filter the results based
> on what is entered in a cell
>
> For example from a table below, I want to be able to input in cell A1
> "Part123" (no quotes) and the list autofilters all the results. If I
> want to autofilter based on the part description, I would like to
> enter "Part345" in cell B1 and all filtered results appear.
> Any ideas?
> Much appreciated for your help!
>
>
> A B C
> 100 Part123 Part123 Description Qty
> 101 Part345 Part345 Description Qty
> 102 Part678 Part678 Description Qty

--
Dave Peterson

On Oct 20, 7:21*am, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> You could record a macro when you do it manually:
>
> Show all the data (if you want)
> Filter the column that you want using a string
> Modify that code to use thecell'svalue instead of your string
> Add a button from the Forms toolbar adjacent to thecell
> Assign the macro to this button
>
> Some place in your code, you'll have a line that includes the criteria:
>
> ..., Criteria1:="YourString", ...
>
> You'll want to change it to look like:
>
> ..., Criteria1:=activesheet.range("A1").value, ...
>
> If/when you need help, just post back with what you have.
>
> On 10/19/2011 19:10, Tony D wrote:
>
>
>
>
>
>
>
>
>
> > Hello,
> > I have a table and would like to know how to filter the resultsbased
> > on what is entered in acell
>
> > For example from a table below, I want to be able toinputincellA1
> > "Part123" (no quotes) and the list autofilters all the results. *If I
> > want toautofilterbasedon the part description, I would like to
> > enter "Part345" incellB1 and all filtered results appear.
> > Any ideas?
> > Much appreciated for your help!
>
> > * * * * *A * * * *B * * * * * * * * * * * * * * * * C
> > 100 *Part123 * Part123 Description * *Qty
> > 101 *Part345 * Part345 Description * *Qty
> > 102 *Part678 * Part678 Description * *Qty
>
> --
> Dave Peterson

Hello Dave, I'm afraid that I don't understand. I understand VBA a
little bit and understand recording macros but can't make sense of
what you've written. Thanks so much for responding to my post!

What part did you not understand?

Did you try recording a macro? Post your recorded code and describe what you
tried and how it failed.

On 10/20/2011 22:08, Tony D wrote:
> On Oct 20, 7:21 am, Dave Peterson<peter...@XSPAMverizon.net> wrote:
>> You could record a macro when you do it manually:
>>
>> Show all the data (if you want)
>> Filter the column that you want using a string
>> Modify that code to use thecell'svalue instead of your string
>> Add a button from the Forms toolbar adjacent to thecell
>> Assign the macro to this button
>>
>> Some place in your code, you'll have a line that includes the criteria:
>>
>> ..., Criteria1:="YourString", ...
>>
>> You'll want to change it to look like:
>>
>> ..., Criteria1:=activesheet.range("A1").value, ...
>>
>> If/when you need help, just post back with what you have.
>>
>> On 10/19/2011 19:10, Tony D wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> Hello,
>>> I have a table and would like to know how to filter the resultsbased
>>> on what is entered in acell
>>
>>> For example from a table below, I want to be able toinputincellA1
>>> "Part123" (no quotes) and the list autofilters all the results. If I
>>> want toautofilterbasedon the part description, I would like to
>>> enter "Part345" incellB1 and all filtered results appear.
>>> Any ideas?
>>> Much appreciated for your help!
>>
>>> A B C
>>> 100 Part123 Part123 Description Qty
>>> 101 Part345 Part345 Description Qty
>>> 102 Part678 Part678 Description Qty
>>
>> --
>> Dave Peterson
>
> Hello Dave, I'm afraid that I don't understand. I understand VBA a
> little bit and understand recording macros but can't make sense of
> what you've written. Thanks so much for responding to my post!

--
Dave Peterson

I got it! Thank you so much! Is it possible to still run the
autofilter results without clicking on a form button?

Here was my code:

Sub Column1AutoFilter()

ActiveSheet.Range("$A$2:$B$6").AutoFilter Field:=1,
Criteria1:=Range("A3")

End Sub


Sub Column2AutoFilter()
ActiveSheet.Range("$A$2:$B$6").AutoFilter Field:=2,
Criteria1:=Range("B3")

End Sub


On Oct 21, 8:12*am, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> What part did you not understand?
>
> Did you try recording a macro? *Post your recorded code and describe what you
> tried and how it failed.
>
> On 10/20/2011 22:08, Tony D wrote:
>
>
>
>
>
>
>
>
>
> > On Oct 20, 7:21 am, Dave Peterson<peter...@XSPAMverizon.net> *wrote:
> >> You could record a macro when you do it manually:
>
> >> Show all the data (if you want)
> >> Filter the column that you want using a string
> >> Modify that code to use thecell'svalue instead of your string
> >> Add a button from the Forms toolbar adjacent to thecell
> >> Assign the macro to this button
>
> >> Some place in your code, you'll have a line that includes the criteria:
>
> >> ..., Criteria1:="YourString", ...
>
> >> You'll want to change it to look like:
>
> >> ..., Criteria1:=activesheet.range("A1").value, ...
>
> >> If/when you need help, just post back with what you have.
>
> >> On 10/19/2011 19:10, Tony D wrote:
>
> >>> Hello,
> >>> I have a table and would like to know how to filter the resultsbased
> >>> on what is entered in acell
>
> >>> For example from a table below, I want to be able toinputincellA1
> >>> "Part123" (no quotes) and the list autofilters all the results. *IfI
> >>> want toautofilterbasedon the part description, I would like to
> >>> enter "Part345" incellB1 and all filtered results appear.
> >>> Any ideas?
> >>> Much appreciated for your help!
>
> >>> * * * * * A * * * *B * * * * * * * * * * * * * * * * C
> >>> 100 *Part123 * Part123 Description * *Qty
> >>> 101 *Part345 * Part345 Description * *Qty
> >>> 102 *Part678 * Part678 Description * *Qty
>
> >> --
> >> Dave Peterson
>
> > Hello Dave, I'm afraid that I don't understand. I understand VBA a
> > little bit and understand recording macros but can't make sense of
> > what you've written. Thanks so much for responding to my post!
>
> --
> Dave Peterson

Personally, I would keep the button. I think it's easier for users to
understand and easier to use (fixing typos before clicking the button instead of
processing the typo, for instance).

But there are events that excel monitors. One of these events is the
worksheet_change event.

If you want to try...

Rightclick on the worksheet tab that should have this behavior
Select View Code
Paste this into the code window

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'single cell at a time
If Target.Cells.Count > 1 Then Exit Sub

'only check A1
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

'tell excel to not look for changes
Application.EnableEvents = False

'your recorded code here
'but change to:
'..., criteria1:=target.value, ...

'tell excel to start looking again
Application.EnableEvents = True

End Sub

You'll have to insert the bulk of your code (not the sub and end sub
statements!) and modify it to use the target.value.

If you have trouble, post back with your code -- the entire code so far!





On 10/21/2011 21:43, Mr. T wrote:
> I got it! Thank you so much! Is it possible to still run the
> autofilter results without clicking on a form button?
>
> Here was my code:
>
> Sub Column1AutoFilter()
>
> ActiveSheet.Range("$A$2:$B$6").AutoFilter Field:=1,
> Criteria1:=Range("A3")
>
> End Sub
>
>
> Sub Column2AutoFilter()
> ActiveSheet.Range("$A$2:$B$6").AutoFilter Field:=2,
> Criteria1:=Range("B3")
>
> End Sub
>
>
> On Oct 21, 8:12 am, Dave Peterson<peter...@XSPAMverizon.net> wrote:
>> What part did you not understand?
>>
>> Did you try recording a macro? Post your recorded code and describe what you
>> tried and how it failed.
>>
>> On 10/20/2011 22:08, Tony D wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> On Oct 20, 7:21 am, Dave Peterson<peter...@XSPAMverizon.net> wrote:
>>>> You could record a macro when you do it manually:
>>
>>>> Show all the data (if you want)
>>>> Filter the column that you want using a string
>>>> Modify that code to use thecell'svalue instead of your string
>>>> Add a button from the Forms toolbar adjacent to thecell
>>>> Assign the macro to this button
>>
>>>> Some place in your code, you'll have a line that includes the criteria:
>>
>>>> ..., Criteria1:="YourString", ...
>>
>>>> You'll want to change it to look like:
>>
>>>> ..., Criteria1:=activesheet.range("A1").value, ...
>>
>>>> If/when you need help, just post back with what you have.
>>
>>>> On 10/19/2011 19:10, Tony D wrote:
>>
>>>>> Hello,
>>>>> I have a table and would like to know how to filter the resultsbased
>>>>> on what is entered in acell
>>
>>>>> For example from a table below, I want to be able toinputincellA1
>>>>> "Part123" (no quotes) and the list autofilters all the results. If I
>>>>> want toautofilterbasedon the part description, I would like to
>>>>> enter "Part345" incellB1 and all filtered results appear.
>>>>> Any ideas?
>>>>> Much appreciated for your help!
>>
>>>>> A B C
>>>>> 100 Part123 Part123 Description Qty
>>>>> 101 Part345 Part345 Description Qty
>>>>> 102 Part678 Part678 Description Qty
>>
>>>> --
>>>> Dave Peterson
>>
>>> Hello Dave, I'm afraid that I don't understand. I understand VBA a
>>> little bit and understand recording macros but can't make sense of
>>> what you've written. Thanks so much for responding to my post!
>>
>> --
>> Dave Peterson
>

--
Dave Peterson