how to read all the sheets in excel
hi All i have a workbook which has almost more then 2 sheets.
I have a master sheet which has all the locations , product code and system qty.
what i want is :
1. I want counted qty from other sheets against the products and there location pick counted values automatically in master sheet.
2. I have a variance sheet in which i want cumulative of products with there total counted qty.
so for example:
Mastersheet:
Location Product System Qty Counted Qty
A-AA-001 1234 20 18 (this value will come from there respective sheets)
A-DD-021 1234 15 19
Variance Sheet:
Product System Qty Total Counted QTy
1234 35 37
I have attached the spreadsheet if someone can help i know excel but not to that level.
if product and location is not in the respective sheets, can we insert them in mastersheet bottom and colour them green?
Thank you
sheets-28-12-2016.xlsx
I have a master sheet which has all the locations , product code and system qty.
what i want is :
1. I want counted qty from other sheets against the products and there location pick counted values automatically in master sheet.
2. I have a variance sheet in which i want cumulative of products with there total counted qty.
so for example:
Mastersheet:
Location Product System Qty Counted Qty
A-AA-001 1234 20 18 (this value will come from there respective sheets)
A-DD-021 1234 15 19
Variance Sheet:
Product System Qty Total Counted QTy
1234 35 37
I have attached the spreadsheet if someone can help i know excel but not to that level.
if product and location is not in the respective sheets, can we insert them in mastersheet bottom and colour them green?
Thank you
sheets-28-12-2016.xlsx
Ammar,
Please find attached.
First update your Master Sheet then Variance Sheet.
Updating from several sheets takes time, I tried to speed up macro to some extent, but still its not that quick. Please have patience while updating.
sheets-28-12-2016.xlsm
Please find attached.
First update your Master Sheet then Variance Sheet.
Updating from several sheets takes time, I tried to speed up macro to some extent, but still its not that quick. Please have patience while updating.
sheets-28-12-2016.xlsm
ASKER
Hi Shumm,
StorQty on Master sheet was from database it will stay as it is.
only counted quantity will vary.
Thanks for the sheet i will have a look now and get back to you.
Regards,
Ammar
StorQty on Master sheet was from database it will stay as it is.
only counted quantity will vary.
Thanks for the sheet i will have a look now and get back to you.
Regards,
Ammar
ASKER
Hi Shums,
The spread sheet you shared doesnt work.
in the variance sheet you have put locations but in variance sheet i need only
Product Total QTYStor Total Counted Qty.
Thank you
The spread sheet you shared doesnt work.
in the variance sheet you have put locations but in variance sheet i need only
Product Total QTYStor Total Counted Qty.
Thank you
I am working on in, will revert soon.
Ammar,
In Variance sheet, do you want only those products which has variance or all the Product Codes listed in Master Sheet?
In Variance sheet, do you want only those products which has variance or all the Product Codes listed in Master Sheet?
Ammar,
I have deleted Manual Sheet, which was in the loop.
Hope this is what you wanted.
sheets-28-12-2016.xlsm
I have deleted Manual Sheet, which was in the loop.
Hope this is what you wanted.
sheets-28-12-2016.xlsm
ASKER
Hi Thank you let me check
In-Active. Considered as Accepted Solution
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
great
You're welcome Ammar! Glad I was able to help.
Please add VBA in your topic.
Please add VBA in your topic.
Formula base solution will slow down your workbook, I can provide you VBA solution.
In your master sheet Variance formula should be wise versa, you have F - E, it should be store quantity minus counted quantity, please advice.
Don't you wanna have master sheet only for those products which has counted quantity, then in Variance you will already have system quantity and we bring counted quantity from Master sheet to Variance sheet?