Linking a worksheet to a pivot table

Hi,

I wasn't sure where to post this thread as it does relate to a pivot table but I'm guessing that I'm also going to need formulae for the solution.

I am trying to create a spreadsheet that links into a pivot table, I want this to pull the results into a certain layout when the pivot table is refreshed. The issue I have is that the pivot table pulls from figures that change on a weekly basis and the data in the pivot table is not always in the same place so I am unable to do a simple H or Vlookup to pull the data from there. The data is split across a number of different sites.

I have created an example that shows the issue in a much smaller version. For the example I have chosen the months of October and November.
What I want to happen is: If I choose October as the month I want these figures to populate into the site analysis P&L tab from the pivot tab, into the correct row heading (department) and the correct column heading (Site). Then when I choose Novmeber I'd like to do the same thing, but Novemeber might have some headings that October doesn't have and vice versa, so if I was to put a sumif or V/Hlookup it would look to a certain line and that line maybe different in the pivot table to the previous month.

Is there a way for the Site analysis P&L to get the data from the Pivot and put this in the right place, even if the pivot data is changed each month?

Any help on this I'd be most grateful.

Thanks,

Michael

Hi Jonesm,

Welcome to the forum.

I have used a simple vlookup formula here and it seems to be working in both the months.. check the attached and let me know in case of any issues.. thanks.
Example(1).xlsx

regards,
DILIPandey

<click on below 'star' if this helps>