I have a number of pivot tables in my workbook all pointing to a tab in the workbook with the source data in it. I send this by email to colleagues and when they modify the data and refresh the pivot tables it looks for the data in my original folder and not in the data source tab. I would like them to be able to modify the data and have a look at the results in the pivot tables before sending it back to me for consolidation.

I notice that when i open the workbook in the email i sent and look at the data source of the pivot table it has forced the full directory spec of where the file is located on my disk and not just the tab in the workbook.

Hope this makes sense and would really appreciate some help on this.