how to count all the qty in sheets and put it in master sheet

Hi All,

I have this spreadsheet (sample attached).

I have Mastersheet which contain all the location and the product quantities, now I have to split them in a separate sheet which will be taken by a counter person and key in numbers, now I want the some of A product to automatically calculated and put in the column counted_qty on the master sheet if it possible? can someone help, please.

Thank you.

Regards,

A
ammartahir1978Asked:
Who is Participating?
 
ShumsDistinguished Expert - 2017Commented:
Hi Ammar,

You can change previous formula with below:
=IF(COUNTIF($C$2:C2,C2)=1,SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!"&"$C:$C"),$C2,INDIRECT("'"&SheetNames&"'!"&"$E:$E"))),"")
Check in attached...
Ammar_Tahir_Stocktake28122017WF_v1.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You forgot to upload the file.
0
 
ammartahir1978Author Commented:
sorry here is the file
Stocktake28122017WF.xlsx
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ShumsDistinguished Expert - 2017Commented:
Hi Ammar,

  1. Have the standard format of all your sheets which you gonna give your staff for counting, as attached...
  2. Add Sheet Names in P2 until last sheet name.
  3. Create a Dynamic Named Range for your sheets with this formula: =OFFSET(MasterSheet!$P$2, 0, 0, COUNT(IF(MasterSheet!$P$2:$P$100="", "", 1)), 1) and name as SheetNames.
  4. Add this formula in O2: =IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!"&"$C:$C"),$C2,INDIRECT("'"&SheetNames&"'!"&"$E:$E"))),"") and drag down until you need
  5. Try entering any count in any sheet for any product list, it will update Master Sheet as requested.
Check in attached...
Ammar_Tahir_Stocktake28122017WF.xlsx
0
 
ammartahir1978Author Commented:
Hi Shums,

thanks i will check and get back to you.
0
 
ammartahir1978Author Commented:
Hi Shum

is the sheetname not auto populated in mastersheet.
0
 
ShumsDistinguished Expert - 2017Commented:
Here is another approach with below Array Formula, confirmed with Ctrl+Shift+Enter:
=IFERROR(VLOOKUP($C2,INDIRECT("'"&INDEX(SheetNames,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetNames&"'!$C:$E"),$C2)>0),0))&"'!$C:$E"),3,FALSE),"")
Ammar_Tahir_Stocktake28122017WF_v2.xlsx
0
 
ShumsDistinguished Expert - 2017Commented:
You need to create a NamedRange as SheetNames as I directed above
0
 
ammartahir1978Author Commented:
Hi Shum,

There is a bug in formula so i put 1000 units on counted qty for a product see below:

A-BB-01-A      5880064      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      1000      

and when i filter it it shows following:

Location      Product      Des1      Current_Stock      Counted_quantity      Sheet Names
A-BB-01-A      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      29      1000      
A-BB-01-A      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-02-A      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      43      1000      
A-BB-02-A      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-18-F      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-19-B      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-20-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-21-B      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-21-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-23-C      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-23-D      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-26-D      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-30-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-31-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-32-B      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-42-D      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-51-D      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-GG-39-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-GG-53-F      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      


so 1000 is been entered every where.
0
 
ammartahir1978Author Commented:
Excellent expert, very helpful
0
 
ShumsDistinguished Expert - 2017Commented:
You're Welcome Ammar! Glad I was able to help you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.