Autofilter using the contents of a particular cell
Is it possible to autofilter a list using the contents of a particular cell?
I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
Autofilter using the contents of a particular cell
Matthew,
Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the window that appears. When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on column E for the code number entered into cell C5. Note that Column E must be the left-most column (column # 1 in the list) or you will need to change the Field:=1 line to reflect that. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$5" Then Exit Sub Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value End Sub "Mathew" wrote in message ... Is it possible to autofilter a list using the contents of a particular cell? I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
Autofilter using the contents of a particular cell
Bernie: Thanks for the help, but I could not get it to work!
I did the right click and paste the code into the view code as suggested. I saved the file. I then changed the data and nothing happened. Im using a pick list so Im sure the criteria matches. Any other suggestions? Nothing is too remedial as Im a novice! Below is the exact code I used! F3 has the criteria! The data begins in cell A8. Do I have to name the list? Mathew Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C3").Value End Sub "Bernie Deitrick" wrote: Matthew, Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the window that appears. When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on column E for the code number entered into cell C5. Note that Column E must be the left-most column (column # 1 in the list) or you will need to change the Field:=1 line to reflect that. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$5" Then Exit Sub Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value End Sub "Mathew" wrote in message ... Is it possible to autofilter a list using the contents of a particular cell? I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
Autofilter using the contents of a particular cell
Mathew,
You need to use F3, not C3, as the criteria value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Thanks for the help, but I could not get it to work! I did the right click and paste the code into the view code as suggested. I saved the file. I then changed the data and nothing happened. I'm using a pick list so I'm sure the criteria matches. Any other suggestions? Nothing is too remedial as I'm a novice! Below is the exact code I used! F3 has the criteria! The data begins in cell A8. Do I have to name the list? Mathew Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C3").Value End Sub "Bernie Deitrick" wrote: Matthew, Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the window that appears. When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on column E for the code number entered into cell C5. Note that Column E must be the left-most column (column # 1 in the list) or you will need to change the Field:=1 line to reflect that. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$5" Then Exit Sub Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value End Sub "Mathew" wrote in message ... Is it possible to autofilter a list using the contents of a particular cell? I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
Autofilter using the contents of a particular cell
Bernie: Again thanks for the help! Sorry to be so stupid, but I still could
not get it to work! I made the code change suggested, dumb mistake on my part sorry. I saved the file. I then changed the data and nothing happened. Any other suggestions? Nothing is too remedial as Im a novice! Code is below. When I view the code Microsoft Visual Basic under the tool bar I have Worksheet and then over to the right it is Change is that correct? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub "Bernie Deitrick" wrote: Mathew, You need to use F3, not C3, as the criteria value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Thanks for the help, but I could not get it to work! I did the right click and paste the code into the view code as suggested. I saved the file. I then changed the data and nothing happened. I'm using a pick list so I'm sure the criteria matches. Any other suggestions? Nothing is too remedial as I'm a novice! Below is the exact code I used! F3 has the criteria! The data begins in cell A8. Do I have to name the list? Mathew Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C3").Value End Sub "Bernie Deitrick" wrote: Matthew, Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the window that appears. When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on column E for the code number entered into cell C5. Note that Column E must be the left-most column (column # 1 in the list) or you will need to change the Field:=1 line to reflect that. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$5" Then Exit Sub Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value End Sub "Mathew" wrote in message ... Is it possible to autofilter a list using the contents of a particular cell? I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
Autofilter using the contents of a particular cell
Mathew,
Post your address (or reply to me privately) and I will send you a working version... HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Again thanks for the help! Sorry to be so stupid, but I still could not get it to work! I made the code change suggested, dumb mistake on my part sorry. I saved the file. I then changed the data and nothing happened. Any other suggestions? Nothing is too remedial as I'm a novice! Code is below. When I view the code Microsoft Visual Basic under the tool bar I have Worksheet and then over to the right it is Change is that correct? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub "Bernie Deitrick" wrote: Mathew, You need to use F3, not C3, as the criteria value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Thanks for the help, but I could not get it to work! I did the right click and paste the code into the view code as suggested. I saved the file. I then changed the data and nothing happened. I'm using a pick list so I'm sure the criteria matches. Any other suggestions? Nothing is too remedial as I'm a novice! Below is the exact code I used! F3 has the criteria! The data begins in cell A8. Do I have to name the list? Mathew Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C3").Value End Sub "Bernie Deitrick" wrote: Matthew, Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the window that appears. When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on column E for the code number entered into cell C5. Note that Column E must be the left-most column (column # 1 in the list) or you will need to change the Field:=1 line to reflect that. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$5" Then Exit Sub Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value End Sub "Mathew" wrote in message ... Is it possible to autofilter a list using the contents of a particular cell? I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
Autofilter using the contents of a particular cell
Bernie: thanks for you help! My e-mail address is:
"Bernie Deitrick" wrote: Mathew, Post your address (or reply to me privately) and I will send you a working version... HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Again thanks for the help! Sorry to be so stupid, but I still could not get it to work! I made the code change suggested, dumb mistake on my part sorry. I saved the file. I then changed the data and nothing happened. Any other suggestions? Nothing is too remedial as I'm a novice! Code is below. When I view the code Microsoft Visual Basic under the tool bar I have Worksheet and then over to the right it is Change is that correct? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub "Bernie Deitrick" wrote: Mathew, You need to use F3, not C3, as the criteria value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Thanks for the help, but I could not get it to work! I did the right click and paste the code into the view code as suggested. I saved the file. I then changed the data and nothing happened. I'm using a pick list so I'm sure the criteria matches. Any other suggestions? Nothing is too remedial as I'm a novice! Below is the exact code I used! F3 has the criteria! The data begins in cell A8. Do I have to name the list? Mathew Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C3").Value End Sub "Bernie Deitrick" wrote: Matthew, Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the window that appears. When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on column E for the code number entered into cell C5. Note that Column E must be the left-most column (column # 1 in the list) or you will need to change the Field:=1 line to reflect that. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$5" Then Exit Sub Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value End Sub "Mathew" wrote in message ... Is it possible to autofilter a list using the contents of a particular cell? I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
Autofilter using the contents of a particular cell
Sent.....
HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: thanks for you help! My e-mail address is: "Bernie Deitrick" wrote: Mathew, Post your address (or reply to me privately) and I will send you a working version... HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Again thanks for the help! Sorry to be so stupid, but I still could not get it to work! I made the code change suggested, dumb mistake on my part sorry. I saved the file. I then changed the data and nothing happened. Any other suggestions? Nothing is too remedial as I'm a novice! Code is below. When I view the code Microsoft Visual Basic under the tool bar I have Worksheet and then over to the right it is Change is that correct? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub "Bernie Deitrick" wrote: Mathew, You need to use F3, not C3, as the criteria value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Thanks for the help, but I could not get it to work! I did the right click and paste the code into the view code as suggested. I saved the file. I then changed the data and nothing happened. I'm using a pick list so I'm sure the criteria matches. Any other suggestions? Nothing is too remedial as I'm a novice! Below is the exact code I used! F3 has the criteria! The data begins in cell A8. Do I have to name the list? Mathew Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C3").Value End Sub "Bernie Deitrick" wrote: Matthew, Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the window that appears. When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on column E for the code number entered into cell C5. Note that Column E must be the left-most column (column # 1 in the list) or you will need to change the Field:=1 line to reflect that. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$5" Then Exit Sub Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value End Sub "Mathew" wrote in message ... Is it possible to autofilter a list using the contents of a particular cell? I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
Autofilter using the contents of a particular cell
Bernie: Thank you very much! I'm not sure what you did! The code is the
same for both I must have missed something! It gives me a learning opportunity to find my mistake! Again thanks! "Bernie Deitrick" wrote: Sent..... HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: thanks for you help! My e-mail address is: "Bernie Deitrick" wrote: Mathew, Post your address (or reply to me privately) and I will send you a working version... HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Again thanks for the help! Sorry to be so stupid, but I still could not get it to work! I made the code change suggested, dumb mistake on my part sorry. I saved the file. I then changed the data and nothing happened. Any other suggestions? Nothing is too remedial as I'm a novice! Code is below. When I view the code Microsoft Visual Basic under the tool bar I have Worksheet and then over to the right it is Change is that correct? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub "Bernie Deitrick" wrote: Mathew, You need to use F3, not C3, as the criteria value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value End Sub HTH, Bernie MS Excel MVP "Mathew" wrote in message ... Bernie: Thanks for the help, but I could not get it to work! I did the right click and paste the code into the view code as suggested. I saved the file. I then changed the data and nothing happened. I'm using a pick list so I'm sure the criteria matches. Any other suggestions? Nothing is too remedial as I'm a novice! Below is the exact code I used! F3 has the criteria! The data begins in cell A8. Do I have to name the list? Mathew Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$F$3" Then Exit Sub Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C3").Value End Sub "Bernie Deitrick" wrote: Matthew, Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the window that appears. When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on column E for the code number entered into cell C5. Note that Column E must be the left-most column (column # 1 in the list) or you will need to change the Field:=1 line to reflect that. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$C$5" Then Exit Sub Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value End Sub "Mathew" wrote in message ... Is it possible to autofilter a list using the contents of a particular cell? I have a list of rows that all have a Code Number. There may be 0 to 55 rows that correspond to the Code Number. I need a way that will automatically just display the rows matching the Code Number. I have several users and none of whom that will take the time to learn how to autofilter. I think I need an autofilter to filter based on the contents of a particular cell. The contents of cell C5 is a unique Code Number, say JB007. I need the autofilter to list only the rows, from a list that is 750 rows, to display only the records that match this unique code. Is this possible? If so please let me know how? Thanks for any assistance you can provide! |
All times are GMT +1. The time now is 03:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com