How do i record stocks

[Nelson11]

Hey Folks,
hope your all enjoying the Christmas but now i need a serious help if someone can help me archive this God bless you in Advance Amen.

my question is i have been assign to build an excel workbook which will contain multiple sheet with user-form for logins and the excel will be using for stocks analysis for five branches i have done all of this but now my boss wants me to create master sheet whereby She will be using it monitor all these branches Opening Stocks and Closing Stocks from three cells (products names, quantity, and closing stock and we have different products and some branches do not sell some products and she wants me to link all these products and each quantity and closing stocks so is like linking the products names and opening and closing so when a branch receives or transfer a product with its quantity it should subtract from that particular products closing stocks

and its a cold store company imagine that the products and they're all in cartons
example is
Branch A

[TABLE="width: 500"]
<tbody>[TR]
[TD]Products List
[/TD]
[TD]Opening Stocks
[/TD]
[TD]Closing Stocks
[/TD]
[/TR]
[TR]
[TD]Beef
[/TD]
[TD]200
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]Tilapia
[/TD]
[TD]100
[/TD]
[TD]70
[/TD]
[/TR]
[TR]
[TD]Chicken Back
[/TD]
[TD]300
[/TD]
[TD]280
[/TD]
[/TR]
[TR]
[TD]Gizzard
[/TD]
[TD]60
[/TD]
[TD]40
[/TD]
[/TR]
</tbody>[/TABLE]


Branch B

[TABLE="width: 500"]
<tbody>[TR]
[TD]Products
[/TD]
[TD]Opening Stocks
[/TD]
[TD]Closing Stocks
[/TD]
[/TR]
[TR]
[TD]Silver Hake
[/TD]
[TD]200
[/TD]
[TD]168
[/TD]
[/TR]
[TR]
[TD]Gizzard
[/TD]
[TD]300
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]Tilapia
[/TD]
[TD]600
[/TD]
[TD]567
[/TD]
[/TR]
[TR]
[TD]Beef
[/TD]
[TD]224
[/TD]
[TD]219
[/TD]
[/TR]
</tbody>[/TABLE]


Branch C
[TABLE="width: 500"]
<tbody>[TR]
[TD]Products
[/TD]
[TD]Opening Stocks
[/TD]
[TD]Closing Stocks
[/TD]
[/TR]
[TR]
[TD]Silver Hake
[/TD]
[TD]30
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Tuna
[/TD]
[TD]700
[/TD]
[TD]573
[/TD]
[/TR]
[TR]
[TD]Chicken Back
[/TD]
[TD]399
[/TD]
[TD]278
[/TD]
[/TR]
[TR]
[TD]Sardine F
[/TD]
[TD]400
[/TD]
[TD]387
[/TD]
[/TR]
</tbody>[/TABLE]

Branch D
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product list
[/TD]
[TD]opening stocks
[/TD]
[TD]closing stocks
[/TD]
[/TR]
[TR]
[TD]Horse Mackerel 25+L
[/TD]
[TD]300
[/TD]
[TD]267
[/TD]
[/TR]
[TR]
[TD]Full Chicken (11pcs)
[/TD]
[TD]400
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]Eba
[/TD]
[TD]500
[/TD]
[TD]387
[/TD]
[/TR]
[TR]
[TD]Tuna
[/TD]
[TD]498
[/TD]
[TD]376
[/TD]
[/TR]
</tbody>[/TABLE]

Branch E
[TABLE="width: 500"]
<tbody>[TR]
[TD]product list
[/TD]
[TD]opening stocks
[/TD]
[TD]closing stocks
[/TD]
[/TR]
[TR]
[TD]Tuna
[/TD]
[TD]300
[/TD]
[TD]250
[/TD]
[/TR]
[TR]
[TD]Full Chicken (11pcs)
[/TD]
[TD]400[/TD]
[TD]357
[/TD]
[/TR]
[TR]
[TD]Sardine F
[/TD]
[TD]4766
[/TD]
[TD]1209
[/TD]
[/TR]
[TR]
[TD]Silver Hake
[/TD]
[TD]498
[/TD]
[TD]298
[/TD]
[/TR]
</tbody>[/TABLE]

Master sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]All products List
[/TD]
[TD]Opening Stocks
[/TD]
[TD]Closing stocks
[/TD]
[/TR]
[TR]
[TD]Silver Hake
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sardine F
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full Chicken (11pcs)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Tuna
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eba
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Horse Mackerel 25+L
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicken Back
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gizzard
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tilapia
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beef
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



and in my sheets for i have columns for total cartons sold, quantity Transfer, quantity receive,opening stocks,closing stocks. and formula is : =in closing stock, opening stocks+quantity receive-total cartons sold- quantity transfer and will give me closing stocks for the day now how i accomplish what my boss asked me to do

i have these for all the five sheets representing a branches because a branch will access a sheet base on permission please Brothers and sister i need your urgent help ASAP

thanks and please take your time to read this for me cos i need you seriously.

 

[Mel Smith]

Hi, and welcome to the Board.

How are the stock levels etc. entered for each branch? Are they done daily by the person logging in?

Design a master sheet that shows every product, opening and closing data and list each branch. If the data is entered daily, then it is straightforward to link each branch sheet to the master, which can be saved or printed off daily for monitoring etc.

HTH

Mel

 

[Nelson11]

yes but can get you email so that send the workbook so that you can understand me very detail thank you

 

[Nelson11]

even if you can help me build a dashboard for this master sheet i will be so grateful

 

[Mel Smith]

PM me and I'll try to help. Unfortunately it won't now be until tomorrow as my family have just arrived and we are spending the day together.

I look forward to getting your PM.

Mel

 

[Nelson11]

OK i understand Sir cos it's Christmas i pray you and your live long in the eye the the Lord,
can you send me your email so that tomorrow as your say then you have a look at the workbook

 

[Mel Smith]

See your PM page.

Mel

 

[Nelson11]

the thing is i have five sheets representing five users sheets one for each branch and another five supervisors sheets one for each branch.
now when a user login with their username and password it only opens the user sheet that is assign to the user and the user sheet is linked to the supervisors sheet so when the user enter data in the user sheet and logout and the later the supervisor login there's a columns in the supervisors sheet that is linked with the user's sheet so when supervisor login and drag those columns it automatically updates those columns in the supervisors sheet.
it is the supervisors sheet that contains the total cartons sold,qty received,qty transfer,opening stocks,and closing stocks and the branches sell different products Branch A products is not the same as Branch B likewise C,D,E and the names are on the user sheets and she want now is you and i "she wants us to this done" please Sir even if you can help me build it again using the template and even if you could have the master sheet to have a dashboard for this i know sir that will help me thank you i advance

example

 

[Mel Smith]

OK. Send whatever sheets you feel you need to, so that I can understand what you're trying to achieve. Have you checked your PM page? I have sent you a private message as we do not " publish" email addresses in open forum.

Mel