Vlookup to add data from pivot table

Hi guys,

I have a master excel workbook (Field DP test interviewer output.xlsx) which I will be using to capture data on a monthly basis to indicate the number of test/practice and real interviews done by market research interviewers.

I have monthly data files (in this case I have attached July titled: UK_AMP_JUL17_Practices__IntUse.xlsx) - which I will turn into a pivot table as already been done, to show me the number of test/practice and real interviews done by interviewers.

Can anyone advise me on a VLOOKUP or the best way to find interviewers on a monthly basis and best extract this onto my master sheet (Field DP test interviewer output.xlsx)? Please note there may be existing and new interviews added to each monthly file.....

Thanks,

R.

Hi Sandy,

I think as I am using Excel 2016, it does have power query (according to google/when I tried to download it). Please see attached the master workbook with July data populated if this helps you figure something out.

Ok.
Fill your source tables & save
In main.xlsx add suitable items to the appropriate fields in Pivot Table by yourself because in your example headers are different (DP vs AMP) and I don't know what is what
after that right click on PT and select refresh

Hi Sandy.

Thanks for this.

2 PROBLEMS

1) The pivot table has a problem in terms of what you provided (as I appreciate you mentioned not knowing what is what) as you do not have a version (filter) 1 means practice, 2 means real. See the images attached. This is making it difficult for me to use the filter for the pivot table. Significantly, the refresh does not work because I think it is linked to a file on your desktop. Strangely, the option to edit the link is greyed out.

Thanks for your help so far, much appreciated.

sorry but i didn't see any option for filter. in your source should be any column with data for filtering. that is why i said i don't know what is what. in original source header for filter is not similar to any header in the last example, so probably you didn't define this option.

any more questions?

edit: this is example. we will change location/link between files later.
Give me example of DP with all months with data and with columns there named Filter (for each month)
your example should contain dummy data but real structure
and
unzip attached file into C:\ directly and play with files there (C:\xxx\(files)