Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Using Excel I am searching for records using Filters>Down Arrow>Text Filters>Contains (refer screenshot), this is very tedious as the seach window disappears after taking the imput and I have to repeat the steps again for searching a new word, is there a option where I can input the search word in a neighbouring cell and the list gets filtered?

Thanks, Kiran

enter image description here

share|improve this question
    
If this really is for a single column you might be better off using Edit, Find and wildcards. –  pnuts Sep 24 '12 at 21:51
add comment

1 Answer

up vote 2 down vote accepted

While you could make some VBA-code and/or use the advanced filter, I would recommend you the following solution:

Add another column to your data with this formula pasted down:

M2=IF(ISERROR(SEARCH($X$1,A2,1)),FALSE,TRUE)

With your desired search string inside X1, your search column A with beginning data in row 2 and always starting at the first character.

Now, this will mark your search data, so you can filter by column M = true.

When you change your search data in X1, just update the filter - i.e. press ALT, then R, then S, then E. You can create some shortcut or a makro with custom shortkey to execute just this filterupdate.

share|improve this answer
    
Not sure the filterupdate is necessary (with Autocalc on?). FLASE might be neater as FALSE. M2 perhaps better as M2 (ie unformatted)? –  pnuts Sep 25 '12 at 13:15
    
had autocalc on, but this does not refilter your dataset. I use M2 in code to state, that CELL M2 = ... , thanks for the hint with FLASE ^^ - but you know, you could have just suggested an edit for that ;) –  Jook Sep 25 '12 at 13:24
    
I've seen advice somewhere 'don't bother to edit for a single letter' - and FLASE does work! Sorry about Autocalc, definitely 'my bad'. (I was using such a small sample I'd applied a filter but not actually bothered to select anything!) –  pnuts Sep 25 '12 at 13:41
    
@Jook sorry for the delay in reply, when you say X1 do you mean cell X1? –  Kiranshell Oct 16 '12 at 14:33
    
@Jook Very elegant solution, worked perfectly, thanks Jook! –  Kiranshell Oct 16 '12 at 14:45
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.