Xl file

[Closed]
Report
Posts
1
Registration date
Sunday October 9, 2011
Status
Member
Last seen
October 9, 2011
-
 shail -
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

2 replies

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
119
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
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