Search function - Urgent

  • Thread starter Thread starter Manu001
  • Start date Start date
M

Manu001

Hi
I have an excel sheet with following columns

location username extension

there are about 500 users mentioned with various locations
in the worksheet.

location username extension
japan Gary Lios 1234
japan Gary lim 2345
hongkong Gary leo 5432
UK Mahesh D 9876
... and so on
One a seperate worksheet I want to create a search
function such that after selection the desired location
say japan and in user name entering gary I should be able
to view all the users who have names starting with Gary
and their respective extension.

Request all to direct me in correct direction.
 
How about
D1: 'Firstname
D2: =IF(ISERROR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))
D3:... use the fill handle

then you can filter and select Gary
Ctrl+A
Data, Filter, Autofilter
click on the dropdown arrow on cell D1
 
Thanks, How I need to do is that onworksheet 1 to find a
name and users extension , I will enter the username in
one of the cells and location in another cell, All or
atleast 10 names , extension ( present in worksheet 2 )
which are similar to the name entered cell appear in the
cells on worksheet 1.

For example

Sheet 1 has following data
Sheet 2

If I enter user name in A1 and location in A2

Then depending upton the data entered say A1 = Japan A2=
Gary

Can I get the data in following format on Sheet 2

A5 A6
Gary Lios 1234
Gary lim 2345

I am very new to this sort of programming please advise
 
Can't help you with doing the autofilter selection from another sheet
with macros, but it seems to me that no effort is saved by using a second sheet
as opposed to using the filters on the original sheet.

You would use the filter arrows on Column A and on Column D.

Since it is Column B that has person's name and not Column A, the
formula in Column D that gets filled down should have been
D1: =IF(ISERROR(FIND(" ",B1)),A1,LEFT(B1,FIND(" ",B1)-1))

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Back
Top