Xl file

shailendra5800 Posts 1 Registration date Sunday October 9, 2011 Status Member Last seen October 9, 2011 - Oct 9, 2011 at 11:33 AM
 shail - Oct 11, 2011 at 12:46 AM

Hi,I need a help for my inventory sheet. I have to create a sheet as shown in summary.That sheet is picking data from Pivot table and that table is made from master sheet.I am using VLLOKUP to get the data in my summary sheet from pivot.The file is only showing some locations in sheet but in actual i have 70stores with 209phone models.So its really very hard to use Vlookup for this much data.I need a Macro or Sumproduct use in the sheet so that data can be automatically updated in the summary sheet as per location and model.The summary sheet should pick available quantity from in Stock column of Pivot and for weekly average it should multiply daily average with 7.This should be done for all stores automatically. thanks to all of you in advance..Looking forward for your support. The file can be viewed on shailendraisking.hpage.com,under introduction category with name pls help sample.

thanks once again

2 replies

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Oct 10, 2011 at 06:09 AM
Hi Shail,

Use can make use of Sumifs function which I used in your sheet and uploaded it as :


for Available the formula is :

and for Weekly avg formula is:

Cheers! :)
Hi Rahul,
Thanks for the help...is there any way by which i can get the values in column weekly average multiplied by 7.It should multiply the value extracted from master daily avg.