Previous Topic Next Topic

Xl file

Closed
shailendra5800 Posts 1 Registration date Sunday 9 October 2011 Status Member Last seen 9 October 2011 - 9 Oct 2011 à 11:33
 shail - 11 Oct 2011 à 00:46
Hello,

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
Regards,Shail
Related:

2 responses

Réponse 1 / 2
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
10 Oct 2011 à 06:09
Hi Shail,

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

https://authentification.site/files/30680207/sample.xlsx

for Available the formula is :
=SUMIFS(master!$L:$L,master!$E:$E,$C9,master!$B:$B,F$7)

and for Weekly avg formula is:
=SUMIFS(master!$G:$G,master!$E:$E,$C9,master!$B:$B,G$7)

Cheers! :)
Rahul
Réponse 2 / 2
shail
11 Oct 2011 à 00:46
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.


thanx
shail