QUESTION: Dear, Excel 2007 Suppose, I have anfile with 5 columns: Student Name, Nationality, Country, City, Hobbies. Let's say we have data of 100 students (100 lines). I need the following (not through the built in filter): 1. When I write down, name or country or city or hobbies to give me the whole line that contains theI wrote. 2. Will it be possible to get the auto-complete word, I mean while writing to suggest me the rest of the word. 3. Will it be possible to get all lines that contain the same word, I mean when I write France, it will list all Student living in France. Imad
ANSWER: IMAD I don't see why you don't use filters. It appears that you just want to replicate filter functionality. You need to explain what you want that is different from filtering. ---------- FOLLOW-UP ----------
QUESTION: By using filter, there are many steps: 1. Get the dropdown menu 2. Unmark all lines 3. Search for the required word 4. Tick the word But I need to directly write down thein a cell on the worksheet itself.
IMAD
You don't need to do all that with and Advanced filter. You can set up a criteria range and you can use wildcards to identify theyou want to filter for if it is not a complete item.
You could possibly simplify the process a little by writing a macro that initiates the filtering process, but, the only efficient way for the macro to work would be to initiate an advance filter.
Dear, Excel 2007 Suppose, I have anfile with 5 columns: Student Name, Nationality, Country, City, Hobbies. Let's say we have data of 100 students (100 lines). I need the following (not through the built in filter): 1. When I write down, name or country or city or hobbies to give me the whole line that contains theI wrote. 2. Will it be possible to get the auto-complete word, I mean while writing to suggest me the rest of the word. 3. Will it be possible to get all lines that contain the same word, I mean when I write France, it will list all Student living in France. Imad
It'd be easier to show than explain. Please send a sample workbook to me at bobumlas@yahoo.com, use subject of "AllExpertsQ" and repeat what you want done. And why don't you want to use filters? It's a builtin feature which does all the things you're asking to do!
Dear, Excel 2007 Suppose, I have anfile with 5 columns: Student Name, Nationality, Country, City, Hobbies. Let's say we have data of 100 students (100 lines). I need the following (not through the built in filter): 1. When I write down, name or country or city or hobbies to give me the whole line that contains theI wrote. 2. Will it be possible to get the auto-complete word, I mean while writing to suggest me the rest of the word. 3. Will it be possible to get all lines that contain the same word, I mean when I write France, it will list all Student living in France. Imad
(1) could be done with an array filter (see http://www.aidanheritage.byethost3.com/excel/Array_Filter.xls) (2) I'm guessing this extra data comes from another column or sheet, so see http://www.google.co.uk/#hl=en&q=excel+auto+complete+macro&aq=f&aqi=g-sx1&aql=&oq=&gs_rfai=&fp=f2fa0bbe2956370e - you might also like to look at data validation to give you a series of lists
(3) sounds the same as 1?
Please feel free to ask follow up questions if it helps.
Advertisement