Pivot Table and Vlookup - Daily Updating

[martinwinter]

Dear All,

I have a table that contains Date, SKU number and quantity (Number) sold. It is added to each day! Every day this is summarised into an existing pivot table which has the SKU number in column A, The dates heading across each column and the quantity (number sold) in the data (tow/column) fields. The total quantity sold by SKU number is shown in the final column.

I have a separate spreadsheet that allows for anyone to input a single SKU number, and an associated lookup formula that returns the total quantity sold. However, every day, the Vlookup formula has to be changed to return the value in the final column of the pivot table, as the final total column in the pivot table increases by one column every day.

As the pivot table has more than a years data I also intend to add a further option to show the total sold in the last month - I have a separate pivot table (dated filtered from the master) but this also changes, as in a month where there is a holiday and no sales, there is no corresponding data and months with less than 31days means the total column is not always in the same column.

I have tried many different options but never succeeded to find a solution - how to always return the total quantity sold by SKU number entered, without having to make daily changes to the Vlookup - so turn to you great guys for guidance.

Thank you in anticipation.

 

[Teeroy]

Hi and welcome to the Forum.

It's common to think of a PivotTable as a standard range, but Tables introduce more flexibility than that and cause trouble (as you've found) when you treat them as "dumb" ranges.

Instead of doing a Vlookup on the Table can use the GETPIVOTDATA function. An example of its format is:

=GETPIVOTDATA("qty",$A$25,"sku","000001")

which requests the qty of items where SKU (column title) matches "000001" of the PivotTable starting in Cell A25(which doesn't change when the table gets updated).

You could also retrieve the result by a SUMIF of the raw data, bypassing the PivotTable completely.

I hope that this helps.

 

[martinwinter]

Thank You Teeroy, both options worked as you suggested Appreciate your response.

 

[Teeroy]

You're welcome.

I'm glad you got a solution to your problem.