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

Products List	Opening Stocks	Closing Stocks
Beef	200	150
Tilapia	100	70
Chicken Back	300	280
Gizzard	60	40

<tbody>
</tbody>

Branch B

Products	Opening Stocks	Closing Stocks
Silver Hake	200	168
Gizzard	300	180
Tilapia	600	567
Beef	224	219

<tbody>
</tbody>

Branch C

Products	Opening Stocks	Closing Stocks
Silver Hake	30	12
Tuna	700	573
Chicken Back	399	278
Sardine F	400	387

<tbody>
</tbody>

Branch D

Product list	opening stocks	closing stocks
Horse Mackerel 25+L	300	267
Full Chicken (11pcs)	400	300
Eba	500	387
Tuna	498	376

<tbody>
</tbody>

Branch E

product list	opening stocks	closing stocks
Tuna	300	250
Full Chicken (11pcs)	400	357
Sardine F	4766	1209
Silver Hake	498	298

<tbody>
</tbody>

Master sheet

All products List	Opening Stocks	Closing stocks
Silver Hake
Sardine F
Full Chicken (11pcs)
Tuna <tbody> </tbody>
Eba
Horse Mackerel 25+L
Chicken Back
Gizzard
Tilapia
Beef

<tbody>
</tbody>

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