Querying linked tables to create a pivot table

Hi Forum!

I want to create a pivot table with slicers to display turnover and revenue.
I have 2 worksheets each containing a table linked via ODBC from our ERP (MSSQL) in an Excel 2010 Workbook.

Problem
One of them contains all the details of sales for the pivot table, however it contains only 1 field which identifies the customer responsible for the sale and that is customer number.
Obviously the customer number is not ideal for a user to select a specific customer in a filter or slicer.

The other table contains all of the customer contact data including the customer number and so-called "short name" in the form "Smith Industries, Berlin".
I would like to use the field "short_name" in the pivot filter (or in another slicer) to select the desired data as it's much more descriptive than a customer number.

How would an expert go about solving this?

Thanks for your time and consideration!!

Nick

Any chance for desensitized example Excel file?

#sandy666
Thanks for the quick reply!
I am away from the office at this time, however I'll try to construct a simple copy tomorrow.

no problem

#sandy666
Ok, That took a bit longer than expected - Sorry!

I have recreated the problem in the attached workbook, I believe that is sufficent to demonstrate what's needed.

(I don't know if it's relevant, but as I said in the opening thread the data tables in the real Workbook are ODBC Linked tables from our ERP Package. Is that relevant?)

Thanks for your time!!
That certainly is going in the right direction. (I wasn't sure if I could put formulas in a Sheet with linked tables. I thought the trick would have been to somehow reconfigure the slicers.)
I replaced the wrong customer number in the stats table with 22025 and notice it returns "Short_Name" (the table header name) instead of the correct entry which should be "Smith Transponders, Berlin" and the Cust_Num" 21360 returns "Smith Transponders, Berlin" instead of "Overland Transformers, Bonn". So it's returning a result from 1 line above the result it should return, how could I correct this?

Excellent!! Thanks a lot once again for your time, all is good and I got to learn something today!!
Kind regards... Nick

You are welcome

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.