ammartahir1978
asked on
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
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
You forgot to upload the file.
ASKER
sorry here is the file
Stocktake28122017WF.xlsx
Stocktake28122017WF.xlsx
Hi Ammar,
Ammar_Tahir_Stocktake28122017WF.xlsx
- Have the standard format of all your sheets which you gonna give your staff for counting, as attached...
- Add Sheet Names in P2 until last sheet name.
- 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. - Add this formula in O2: =IFERROR(SUMPRODUCT(SUMIF(
INDIRECT(" '"&SheetNa mes&"'!"&" $C:$C"),$C 2,INDIRECT ("'"&Sheet Names&"'!" &"$E:$E")) ),"") and drag down until you need - Try entering any count in any sheet for any product list, it will update Master Sheet as requested.
Ammar_Tahir_Stocktake28122017WF.xlsx
ASKER
Hi Shums,
thanks i will check and get back to you.
thanks i will check and get back to you.
ASKER
Hi Shum
is the sheetname not auto populated in mastersheet.
is the sheetname not auto populated in mastersheet.
Here is another approach with below Array Formula, confirmed with Ctrl+Shift+Enter:
=IFERROR(VLOOKUP($C2,INDIR ECT("'"&IN DEX(SheetN ames,MATCH (1,--(COUN TIF(INDIRE CT("'"&She etNames&"' !$C:$E"),$ C2)>0),0)) &"'!$C:$E" ),3,FALSE) ,"")
Ammar_Tahir_Stocktake28122017WF_v2.xlsx
=IFERROR(VLOOKUP($C2,INDIR
Ammar_Tahir_Stocktake28122017WF_v2.xlsx
You need to create a NamedRange as SheetNames as I directed above
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent expert, very helpful
You're Welcome Ammar! Glad I was able to help you.