Formula to reference data from a PivotTable

Question

I am having some difficulty writing a specific Excel formula. I have a summary sheet that pulls data from various other sheets extracted from a database. One of these is a PivotTable whereby using the Item Number in the first column and the dates along the top row as a reference I can pinpoint the data I need. eg:

To address the highlighted cell I would normally manually write:

=GETPIVOTDATA(HighPiv,"SPN010977-204 11333")  

HighPiv is the name I gave to the pivot table as I am referring to it from my summary sheet.

This works, however the Week numbers along the top will continuously be changing in the pivot every month and therefore this formula will not pick up the values accurately once the pivot is updated. I have been looking into a way to make the referencing more dynamic. This is the summary where the data is required:

Rather than within the quotation marks of the formula (adding the specific Item number and Week number word for word), I was hoping to refer to the cell references of the summary sheet. (So if I wanted Item number, say A55, and Week number, say H50). The dates in the summary sheet change according to the pivot so referring to the dates on the summaries to get the data would be a better way for it to be kept up-to-date.

The problem here is I don't know how to go about it. I have tired to refer to the cells in question but it doesn't seem to work giving me #REF! or #VALUE! errors.

Answer 1

I think what you would like is:

=GETPIVOTDATA("Qty",HighPiv,"Item",A55,"Week",H50)

I find the easiest way to write such a formula is to start by ensuring that Pivot Table Tools > Options > PivotTable – Options, Generate GetPivotData is checked then in the desired cell enter = and select the required entry from the PT (here63). That would show (for example) “SPN010977-204” and 11333 or ”11333” but these can be changed to A55 and H50.