Filter out rows based on the value of a cell

Using Windows XP and Excel 2007

Would like to create a search box of sorts, where when I type a word
into a specific cell, let's say B2, then the rows displayed filter out
based on what I typed, so that only rows where column D equaled
"Smith" for example, would show. It would also be helpful if I could
select a value from a dropdown next to it determining which column
would be checked for the filtering. For example, I type "Smith" in
cell B2, and then select "Last Name" from the drop down so that the
rows displayed are filtered by showing only rows where the D column
(LastName) equals or contains "Smith".

My super-elite wish here, is that the filtering would update every
time a character was typed while still in the cell.

Is any of this possible?

Thanks in advance,

magmike
0
magmike7 (50)
1/17/2012 10:02:01 PM
excel 39879 articles. 2 followers. Follow

4 Replies
545 Views

Similar Articles

[PageSpeed] 57

On Jan 17, 5:02=A0pm, magmike <magmi...@yahoo.com> wrote:
> Using Windows XP and Excel 2007
>
> Would like to create a search box of sorts, where when I type a word
> into a specific cell, let's say B2, then the rows displayed filter out
> based on what I typed, so that only rows where column D equaled
> "Smith" for example, would show. It would also be helpful if I could
> select a value from a dropdown next to it determining which column
> would be checked for the filtering. For example, I type "Smith" in
> cell B2, and then select "Last Name" from the drop down so that the
> rows displayed are filtered by showing only rows where the D column
> (LastName) equals or contains "Smith".
>
> My super-elite wish here, is that the filtering would update every
> time a character was typed while still in the cell.
>
> Is any of this possible?
>
> Thanks in advance,
>
> magmike

90% of what you want is natively supported in Excel thru AutoFilter.
Once you have established the filter, all you need to do is click on
the Last Name column header cell and pick "Smith" from the drop-down
list.  I suggest you check-out:

http://www.contextures.com/xlautofilter01_2003.html
0
1/17/2012 10:51:55 PM
On Jan 17, 4:51=A0pm, James Ravenswood <james.ravensw...@gmail.com>
wrote:
> On Jan 17, 5:02=A0pm, magmike <magmi...@yahoo.com> wrote:
>
>
>
>
>
> > Using Windows XP and Excel 2007
>
> > Would like to create a search box of sorts, where when I type a word
> > into a specific cell, let's say B2, then the rows displayed filter out
> > based on what I typed, so that only rows where column D equaled
> > "Smith" for example, would show. It would also be helpful if I could
> > select a value from a dropdown next to it determining which column
> > would be checked for the filtering. For example, I type "Smith" in
> > cell B2, and then select "Last Name" from the drop down so that the
> > rows displayed are filtered by showing only rows where the D column
> > (LastName) equals or contains "Smith".
>
> > My super-elite wish here, is that the filtering would update every
> > time a character was typed while still in the cell.
>
> > Is any of this possible?
>
> > Thanks in advance,
>
> > magmike
>
> 90% of what you want is natively supported in Excel thru AutoFilter.
> Once you have established the filter, all you need to do is click on
> the Last Name column header cell and pick "Smith" from the drop-down
> list. =A0I suggest you check-out:
>
> http://www.contextures.com/xlautofilter01_2003.html- Hide quoted text -
>
> - Show quoted text -

I am aware of Auto Filter and do use it. However this option would be
simpler to use in my instance, as tens of thousands of rows produce
quite the list to scroll through. I also know that you can also filter
by text, but the method I am trying to acheive would be a little bit
faster and simpler to use.

I was hoping there was a way to cause the sheet (through VB) to filter
out rows that did not contain the text of B2 once the cell was updated
or left.

Is this possible?

Mike
0
magmike7 (50)
1/21/2012 4:41:17 PM
On Jan 21, 10:41=A0am, magmike <magmi...@yahoo.com> wrote:
> On Jan 17, 4:51=A0pm, James Ravenswood <james.ravensw...@gmail.com>
> wrote:
>
>
>
>
>
> > On Jan 17, 5:02=A0pm, magmike <magmi...@yahoo.com> wrote:
>
> > > Using Windows XP and Excel 2007
>
> > > Would like to create a search box of sorts, where when I type a word
> > > into a specific cell, let's say B2, then the rows displayed filter ou=
t
> > > based on what I typed, so that only rows where column D equaled
> > > "Smith" for example, would show. It would also be helpful if I could
> > > select a value from a dropdown next to it determining which column
> > > would be checked for the filtering. For example, I type "Smith" in
> > > cell B2, and then select "Last Name" from the drop down so that the
> > > rows displayed are filtered by showing only rows where the D column
> > > (LastName) equals or contains "Smith".
>
> > > My super-elite wish here, is that the filtering would update every
> > > time a character was typed while still in the cell.
>
> > > Is any of this possible?
>
> > > Thanks in advance,
>
> > > magmike
>
> > 90% of what you want is natively supported in Excel thru AutoFilter.
> > Once you have established the filter, all you need to do is click on
> > the Last Name column header cell and pick "Smith" from the drop-down
> > list. =A0I suggest you check-out:
>
> >http://www.contextures.com/xlautofilter01_2003.html-Hide quoted text -
>
> > - Show quoted text -
>
> I am aware of Auto Filter and do use it. However this option would be
> simpler to use in my instance, as tens of thousands of rows produce
> quite the list to scroll through. I also know that you can also filter
> by text, but the method I am trying to acheive would be a little bit
> faster and simpler to use.
>
> I was hoping there was a way to cause the sheet (through VB) to filter
> out rows that did not contain the text of B2 once the cell was updated
> or left.
>
> Is this possible?
>
> Mike- Hide quoted text -
>
> - Show quoted text -

I found a solution to this that is really super-cool at
http://www.contextures.com/excelfiles.html#filters using the one
called Fast Filters.

magmike
0
magmike7 (50)
1/22/2012 6:36:59 PM
Actually the link is http://www.contextures.com/excelfiles.html#filter
(I had an "s" on the end of the other which is incorrect)

magmike




0
magmike7 (50)
1/22/2012 6:39:57 PM
Reply:

Similar Artilces:

error value in axis
I am trying to plot a chart for varying number of weeks. I use the formula =IF(J63=0,NA(),J63-1) to stop with zero th week starting from the week I define. I also use similar formulas for getting data i.e =IF(ISERROR(VLOOKUP($K64,$A:$D,2)),NA(),VLOOKUP($K64,$A:$D,2)), where k64 indicates week number. The problem now is the data line stops with 'NA' values as expected, but the axis which contains week number shows 'NA' after the week numbers. Any ways to fix this. Now you need dynamic names (aka named ranges) that know where the numerical data ends, and use these names a...

Filters *SOS* Filters *SOS* Filters *SOS*
1) On formLoad, I need to show only those items which are unassigned. 2) I need for the end-under to select only the items they are assigned to. 3) I need an option to select "ALL" so I can view all items. Any insight offered would be appreciated!!! On Wed, 29 Aug 2007 16:24:01 -0700, valdaiway <valdaiway@discussions.microsoft.com> wrote: >1) On formLoad, I need to show only those items which are unassigned. Use a query to put a criterion on the field that defines the assigned status. I assume you know how that information is stored in your table; nobody here does, ...

extracting sheets on the basis of Row character
Hi, I have the below data...i have only shown the st column but i have the data in 15 coulmns. Like this i have 20 sheets... and i need a separate sheets for the name that are contained in column A ie separate sheet for "Mark" where it will extract the data from 20 sheets where there is row with name mark... Name Mark Steve Mark Gary Mark Gary Steve Regards, radha If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line ...

Criteria for Advanced Filter HELP
I have what I think is a simple question that I need answered IMMEDIATELY. I have a spreadsheet with 20 fields and I want to filter it so it only shows records that have something in of any of 5 of those fields. So once filtered, I will not have any records that have all 5 of those fields blank. I know I have done this before but I'll be damned if I can remember or find out how. -- Thanks You all are teaching me so much don't cross-post -- answer supplied in misc NG "knowshowrosegrows" <knowshowrosegrows@discussions.microsoft.com> wrote in message news:ACBC0...

Counting occurences of codes in cells
I have a spreadsheet with various codes separated by periods, in a single cell. Like: C T C,R T,C,R R and so forth. It's easy to count the single codes but how do I cound the "C" when it's with another character? There must be a simple way, without writing code, to do this. Is there? Thanks! maybe... =countif(a1:a10,"*" & "c" & "*") or =countif(a1:a10,"*c*") or if x99 contained the letter c. =countif(a1:a10,"*" & x99 & "*") salgud wrote: > > I have a spreadsheet with various codes separa...

Image in Knowledge base
Hi, Is it possible to add an image in an article of the knowledge base? Thanks for your help I don't think this is possible. But, it is pretty easy to drop in a hyperlink to a picture on another server, or a local file, etc. When you publish the article, the hyperlink becomes active and will open another window with the picture. Not that great, but it does work. Good luck! Steve C. "B. Guillout" wrote: > Hi, > Is it possible to add an image in an article of the knowledge base? > Thanks for your help ...

Employee Filters
Hello: On the employee filters in the HR Preferenes window, would this restrict seeing employee information in both Payroll and HR? Also, would it restrict reporting on certain employees in a division or department? Thanks! childofthe1980s Your message does not indicate which version of Great Plains you are using. Using 8.0 with SP3 applied, HR filters do apply to some of employee information outside of HR. The settings will filter selected forms, Smartlists and Lists items. The HR filter setting do not apply to reports outside of HR. The filtered forms are: Employee Maintenance,...

Hyperlink base: What does it do?
Excel 2007 I've been reading some posts on the hyperlink base feature, but I'm not sure what it's supposed to do. What I would THINK it should is that, when you press Ctrl-k in a cell, the Address would automatically appear as your base and the Current Folder would open to the base folder, so you could set you more- specific hyperlinked location from there. But that's not what happens. Does the hyperlink base have any practical value when creating hyperlinks using Ctrl-k? Thanks. ...

Time cell with colons
I want to format a cell for 24 hour time that will automatically add colons for hr:min:ss plus allow a zero to be used at the beginning of any 24 hour times before 10 am. This is a log sheet for logging response times at a fire department. It will not be necessary for any of these times to be used for data collection, etc. THANKS! You can simply use the format property to display date/time fields as you please or use the Format() in vba. -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please ra...

Filtering based on a filter
Hello, I have an Excel Worksheet with a Pivot Table at the top with a Filter and a data set returned from a DB2 database below it. I would like to filter the DB2 data set based on the Pivot Table filter that is enabled. Any ideas? Thanks! James, You can't filter two ranges on the same worksheet. You can, instead, drill-down on your pivot table, which will show all items that go into the value on a newly created separate sheet. The drawback to that is that you cna only drill down on one item at a time, and it is a separate sheet. HTH, Bernie MS Excel MVP "James" <an...

Question on the IMF filtering in exchange...
Hey I installed the IMF filter last night and it works great, with one exception. I set both thresholds to the same (5) and got almost all blocked emails into the archive that were junk...So that is good. However I had some users report that there was mail in their junk folder that should have been good Inbox mail. This I undersyand and can live with as folas-positives...the problem is, since both settings on the folter tab were set to 5, how does it know to send the mail to archive rather than Junk folder? Why wouldn't it just send them all to archive if both settings were at ...

template-adding rows-include formula
I have a template with formulas but when you try to add rows, it doesn't automatically add the formulas for that particular column. How do I automate that to copy the formula when adding rows? thanks One way: http://www.mvps.org/dmcritchie/excel/insrtrow.htm In article <1118603894.970291.208610@o13g2000cwo.googlegroups.com>, "guideme" <eveas@yahoo.com> wrote: > I have a template with formulas but when you try to add rows, it > doesn't automatically add the formulas for that particular column. > How do I automate that to copy the formula when ad...

vlookup for words content when there are duplicate values in the lookup-column
Hi, Would appreciate if someone help me here. Thanks in advance. When I vlookup Material1 to Material2 for the Project No, I get the vlookup Project No. as 222-56 for all the duplicate Material values. What formula to use so that when there are duplicate values in Material2, I want to have vlookup looking to match Project No. containg "AA" as 1st choice ? (if no Project No. containing "AA", then looks for its content "AB" and subsequently looks for "AC" if there is no "AB"). ColumnA ColumnB ColumnC Material2 Duplicate? Project No...

Variant Array Copy, Array row contains Object
Hi All, For the first time I had a need to put an object inside a two dimension variant array. The proc below was for some initial testing. I had to code an object test at the line marked with 1. I was surprised that the 'copy' at line 2. worked. Can you tell me why? Thanks. Neal Z. Sub Test() Dim vArray, vArrayB Dim Row As Long, Col As Long ReDim vArray(1 To 2, 1 To 2) Set vArray(1, 1) = ActiveWorkbook vArray(1, 2) = "abc" vArray(2, 1) = 24 Set vArray(2, 2) = ActiveSheet ReDim vArr...

Filtering without Filter
I just don't get the following. Any ideas? "A" row I have two different values I use: "Contact" and "Not a contact". "B" row I have the person's name (employee). "C" row I have a quantity, just one number. Now what I need is to output quantities of how many Contacts Employee1 or Employee2 or Employee3 have. Also what I need is to get the number of contacts - which can be found on row "C". Should I use COUNT.IF ? Row A can have 2 or more different values so should there be somekind of conditional IF function included? T...

Access 2007 Run-time Error 2467 when accessing a listbox value on a sub form
I have a database that has been in production on Access 2003 for quite some time and has run without errors. A user's machine was recently upgraded to Access 2007 and now the database throughs a Run-time Error 2467 "The Expression you entered referes to an object that is closed or does not exist" on the following line that references a listbox value on a sub form. If Nz(Me.sfrm_Consultant_Contract_Request.Form.Cmb_Consultant.Value) = "" Then .. .. .. I changed the code to If Nz(Forms! frm_Main.sfrm_Consultant_Contract_Request.Form.Cmb_Consultant.Value) = ""...

Filtering #11
Hi, can ayone tell me how I could do the following in excel 2007 or 2010: Horse Trainer Sam M Jones John M Jones Colin S Attwater Steve J Jackson Brian B Mathers James B Mathers Martin G Raggy Patrick S Thomson What I want to do is filter the dataset on 'Trainer' so that it only shows me the Trainers who feature once in the list. So if I was to do this properly it would only show S Attwater and G Raggy as the other trainers have more than one horse..... Hopefully someone can help because it is driving me crazy !! Thanks David Give this macro a try... Sub HideDuplicateTr...

Junk filter BEFORE other filters?
I am running Outlook 2003 and the Junk filter was working really well. Then I started making other filters...one of them looks for my domain name in the headers and if it's there it moves the email to a folder. Problem is, this filter seems to activate before the junk filter, so the junk sn't being filtered now! How can I set the junk filter to do it's job first and THEN to run my own rules? -- Bryan Anderson <www.anderson-it.com> I created this rule and set it as the first rule to run: Apply this rule after the message arrives with 'X-Spam-Level:******'or'...

an address in a row in excel
I have 6352 mailing addresses in a column, I would like to organize under Company, Adresse 1 Addresses 2, city, state zip phone etc in a row. is there a formular I can use in excel to do this Hi could you post some example data and the expected result -- Regards Frank Kabel Frankfurt, Germany "Betty F" <Betty F@discussions.microsoft.com> schrieb im Newsbeitrag news:C9F5F96A-F5AE-456D-8B6E-2D48C0961A31@microsoft.com... > I have 6352 mailing addresses in a column, I would like to organize under > Company, Adresse 1 Addresses 2, city, state zip phone etc in a row. is ...

unlock frozen cells
I opened excell program to add data but it is locked. I can't add nor delete data. None of the functions work. It opens, but is locked. Help It might be that you have a 'Freeze Panes' issue. To unfreeze the windows... In 2003 - WINDOWS > UNFREEZE PANES in 2007 + VIEW > WINDOWS > UNFREEZE PANES -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Windows 7" wrote: > I opened excell program to add data but it is locked. I can't add nor delete > data. None of t...

#Value! Error when Concatenating
I am trying to concatenate two fields together. They are both numerical fields. I have never had a problem but for some reason some will work but most give me the #Value! error. I am using the 2003 version of Excel. Does anyone know why this is happening? There are no formulas in these two cells. I tried +C13&D14 and I also tried =concatenate(C13,D14) and even =concatenate(C13&D14) but I keep getting the #Value! error. Thanks Lyma, Did you try =C13&D14 ? What are in C13 and D14? Formulas? Beege "LymaBeane" <l-tater@cox.net> wrote in message news:11...

Bad visbility off a cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, i am new here. I have a question, when I click in a cell in an excelsheet then this is obscured. I mean the square of the cell is almost invisible. So if there is a lot of figures in this sheet you can not find back the cell where u was working. Has anyone a solution how to change in a better visibiliy? Thanks in advance. Thijs On 3/3/10 8:04 AM, Thijs@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Hi, i am new here. I have a question, when...

How do I stop graphs reading zero in Excel from cells with ""?
Hi. I am making a model in Excel which is takes data updates from an outside source and then processes the information to develop charts on the data. There are a lot of charts so I have developed the model to process the data automatically as the data comes (down the sheet). This processing means that there are formulas in place, waiting for the data to come in. I use a =if(a1="","",<operation>) notation to do it for me. If there is data then the if statement is false and the data can be processed; if the data hasn't arrived yet then instead of getting...

Pasting into a filtered list
Can anyone tell me if it is possible to paste a list of price updates into a filtered list. - I have filtered the list to show just the products whose prices need to be updated, when I try to paste the updated prices, into the list, the prices are not just pasted into the visible cells but the hidden cells between. Can I paste into the visible cells only or do I have to update each line manually. I am using Excel 2002 Any help would be much appreciated Can you sort the list to bring the products that need updating together in the list? If not, try something like this: Your list of product...

Macro to copy a VLOOKUP formula
Hello, I was wondering if somebody could help me with this macro… I have some data with a filter on, so that only the rows with blank cells are showing. I want to enter a VLOOKUP in the cell, and copy it down to all the blank cells. I tried just recording a macro, but when I run the macro it doesn’t work. Any ideas of how to do it? Example: 1) Before the filter: Colums A -B - C A x x - 1st Row A - 2nd Row A x x - 3rd Row A - 4th row A x x - 5th row 2) After the fil...