Multiple search options from datatable in excel

[Atanu Chanda]

Hello All,


First of all I should thank Chandoo for his excellent work which I have silently followed up since late 2009.


I have made a master data table which comprises of almost 6000 rows, each row containing very important employee info.I was trying to build a "Search"-option on the top in-order to fetch the data from the table instead of using auto-filters or ctrl-F.


Table-headers:

EMP_ID YR MNTH DAY ENAME SURNAME DESIGN CLASS TECH_NON-TECH BANK_ACN PAN# PF_ACN PF_DN POSTING DT_JOIN DT_RETIREMENT SERVICE_TENURE


I was only able to workout on a single column-i.e,"EMP_ID" from the data-table.


EMPLOYEE INFORMATION[Selected Table-headers]=OFFSET(DATA[[#Headers],[COLUMNNAME]],MATCH(SEL_EMP_ID,DATA[EMP_ID],0),0)


Would it be possible for you to suggest something by which I can return values,

if there is any "1" Search Parameters filled by the user.[unique values for any employee]


ENTER_EMP_ID: Currently applied.

ENTER_ENAME :

ENTER_PAN# :

ENTER_BANK_ACN:

ENTER_PF_ACN :

 

[Luke M]

Your simplest and quickest setup would be to run an Advanced Filter. You've already got a nice table, good header names. If you changed the layout of search criteria to being horizontal rather than vertical, everything would be perfect. For niceness, you could have the user enter all the search criteria and then hit a button to run a short macro, or you could setup an event macro that automatically runs the filter whenever the user makes an entry. Another benefit is that users could use wildcard searches, or something like ">2010" for the YR field.

Basic intro to adv. filters:

http://www.contextures.com/xladvfilter01.html

filters w/ macros:

http://www.contextures.com/excelfiles.html#Filter

 

[Mike86]

There's also always the "Data Form". It's not as easy to find as it used to be, and not in the ribbons, but can be installed in the Quick Access toolbar. Installation instructions can be found in Excel help under "data form". This tool allows data entry/ deletion, basic multi-variable search criteria with previous and next, and is pretty easy to use.

 

[Atanu Chanda]

Thanks guys for your suggestions. I'll surely work on both these ideas to see which ones easier.

 

[dan_l]

You could set up MS query to run if you don't want to use another workbook to do it.