Pulling Data from Database...could you give me advice?

[PP3321]

I need to create a Excel system where employee ID is entered,
names and tel.no are automatically filled.

1 solution is create 1 sheet with the data source, and use vlookup to match them.
But I do not want to use this solution because data gets updated quite often.

Is there anyway to connect to Database directly? should I use MS Query or PowerQuery?

*I cannot use VBA Macro because Macro is forbidden for the end-users...

Any advice is highly appreciated...thank you...

 

[Chihiro]

Even with query, if you need parameter to be dynamic (i.e. Employee ID). It will require use of VBA.

One workaround is to pull entire table using MS Query (in separate sheet). Then use Lookup(s) to display.

 

[PP3321]

Thank you Chihiro...

 

[Chihiro]

Another method, which is bit more efficient, is to use PowerQuery with some custom functions. However, do be careful how you use it. Depending on data folding and how you do your operation, it may hamper performance.

See following articles on how to do this.
Step1: http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

Step2: http://www.excelguru.ca/blog/2015/02/18/dynamic-database-queries/

 

[PP3321]

Wow thank you so much!!!!