The F4 key will let you cycle through absolute and relative ranges, instead of editing and adding the $ symbol. Also add another column so you can have both a cost price and a sales price. That would allow you to calculate profit. If you turn your code column into a named range, you can then create pull downs with data validation for the other tables.
Thank you for this great video. One question/suggestion: to solve the identical names issue, refer to item codes instead of item description and you won't need to change or edit the item descriptions.
I can't tell you how thankful I am for this video! THANK YOU SOOOO MUCH. It helped me lots with a problem I had at work and this worked liked magic. :) Thanks!
wow its a great video. my only issue on this is how it approaches its cost. the price in the inventory changes from time to time. and editing the item list price may affect the history cost.
Thanks alot Chris your tutorial was wonderful. With it I was able to develop a stock inventory for my lab which I believe would be very useful in maintaining our stock. Thank you
This is helpful. I learn alot. I hope you can upload a video for this inventory excel, on how to in and out the product with the use of barcode scanner thanks a lot. ❤😇
such an amazing video, real great of you to do this, can't wait to get mine started. I've literally searched the whole of youtube for something simple like this
Thanks, check this out too, it allows you to add more items coz it's more dynamic and expandable. It has a sample template too that you use right away.
Cris Minas how do I create the in and out on a monthly basis, for jan, feb, mar etc. Do I need to create a new sheet each month and copy everything over. Thanks
Nice example... you would be better off turning all of these into TABLES -- to make dynamic so you can keep adding inventory and sales for hundreds of transactions. Also just a note... to hard to type "$" to lock cells.. when the range is colorized just press the [F4] key to lock cells. just to save time
Patrish Casila I've learned about the vlookup today. It has been a great help because we have a research and watching this made it easy for us. I also learned the importance of the dollar sign, and other functions of the ms excel.
great job, but what happens when the received list is full? we need to delete the input in the row to make place for new one but that changes to quantity in the storage too,right?
Thank you so much for helping us get control of our inventory and making it simple for us. I have a question with regards to the price/item. In reality, the price of an item is a variable. How do we account for it in this system?
Just make the price variable for each purchase/incoming or sales/outgoing. The system is only displaying that value for ease of viewing. It's not required.
Just what I need. However, how do I like make an average of the price... I purchase the same kind of products from different stores (with different purchase price)? Was hoping you could help me out on how to go about it. Thanks
I mean, you can just not include the price in the VLOOKUP code and enter the price you purchase it for as you go. That seems like the easiest solution to me.
Hi Cris, this is an awesome video. What troubled me is my incoming stock has different batch numbers. How do i manage the inventory with the ever changing batch numbers?
Is this possible with changing dates in rows. For example in sheet received goods different rows different dates, automatically filled in different rows in stock sheet? The same way for sales.
Greetings! Can u help me? My problem is the stock inventory is not changing but im already update the received item table. Can i know how to solve it? Thank you
How do you add a different colour for each product, so that when I get a sale and input A1 for example it automatically shows up with the colour I have chosen for it as well? thanks
Good tool Cris, I am really appreciated that but could you please share us how to control the PRICE as we know that same product but may not same price. I am looking to hearing from you soon.
Create pricelist in another sheet and use index function. Buy hey, If you're trying to create your inventory, i have free to download check my new video 😊
how can I automate Code No in stock inventory table? also if i have multiple entries "A1" in purchase table, how do I combine them in Stock inventory table?
This has really helped me but i was wondering how i could add a reorder level so that when stock becomes below a certain amount it would be highlighted? do you know how to do this
I would create a new column prior to the Comments column called ReOrder and that have the target numbers of when you get to that point or below is when you want to re-order product. The cells I reference will differ from the video as a new column has been inserted. Now in the Stocks inventory "table" highlight the Stocks column from $AA$:$AA$14 then on the Home tab, Styles group choose conditional formatting, New Rule. Your range will be what I listed above and your formula is =VLookup($v1,Items,5,0)>=$AA4 then choose whatever fill color or formatting you want. Remember hitting the F4 key will cycle through where the $ symbols can be placed.
I did this and it works! thanks for this.. big help for my small business.. but how about if I add the wholesale price of the products ( so I know how much I gained) ? how will I do it? please help me..thanks
in the last table. you have to add 3 more columns .your receiving price.sold price.balance final profit amount you got..this will not affect the vlook up table.
Also how to link up the customer list with the quality As some item is sold so we write in front of customer details. So the same qty should get deducted from sales qty
In the last stock inventory box why don't you use simple formula like =+k4 in the received field and =+r4 in the sales field? Cause your formula and this simple formula do the same. Please tell why????
Hi Cris Minas one question, Why do i get number -20 in my Stock list when i put number 10 in sales list? Edit: nvm i realized i was on the wrong list. BTW great tutorial i really loved it and really simple im gonna be watching more of your excel tutorials Keep up the good work Cris Minas :D
well tell me if same item i have and when i entry the item there is difftrent size and colours in garms so how i do for the same also why is not showing balce money for the same stock
If you are using table, your formula should be =SUMIF(RECEIVEDproductrange,STOCKS1stproduct,RECEIVEDproductquantity). If not check the placement of the dollar signs. You can also put it fast after selecting the range press Fn+f4
HELLO SIR IM USING YOUR PROGRAM BUT IT COMES TO STOCKS INVENTORY WHY SHOULD NOT link the receiving into stocks inventory when I input the received still zero,please some ideas if there are some other link. thank you
![How to keep Inventory using an Excel Spreadsheet - [create your own Template]](http://i.ytimg.com/vi/msQoTuCsKJ4/mqdefault.jpg)
The F4 key will let you cycle through absolute and relative ranges, instead of editing and adding the $ symbol. Also add another column so you can have both a cost price and a sales price.
That would allow you to calculate profit.
If you turn your code column into a named range, you can then create pull downs with data validation for the other tables.
This is one of the simplest and most powerful inventory management system established on Ms Excel. Thumps up man
Thank you for this tutorial. This is what exactly i am looking for. Simple but effective.
Thank you for the example, I'm going to re-create this to get a better idea of how I want to run my inventory system!
do you know how to add more item ?. I try to add but it only show the previous item list we did. if you know, please share you idea
i have the same doubt .Do u get it ?
This help me a lot in my first Barcode System. I made it in just an hour with more features. Thanks a lots for this gem!!
Thank you for this great video. One question/suggestion: to solve the identical names issue, refer to item codes instead of item description and you won't need to change or edit the item descriptions.
the best explanation ever..very simple n easy to understand..thank you for this efforts..
Thank you thank you thank you for making this straight to the point and easy to understand! This inventory system is amazing I'm saving this video.
I can't tell you how thankful I am for this video! THANK YOU SOOOO MUCH. It helped me lots with a problem I had at work and this worked liked magic. :) Thanks!
Great isnt it!
you're a life saver! i was looking specifically for this.
glad the video helps :)
@@CrisMinas can you send this to me through email plz. its emergency
Maybe its too late for me but this really helps me a lot. Thank you for sharing your knowledge.
wow its a great video. my only issue on this is how it approaches its cost. the price in the inventory changes from time to time. and editing the item list price may affect the history cost.
Thanks alot Chris your tutorial was wonderful. With it I was able to develop a stock inventory for my lab which I believe would be very useful in maintaining our stock.
Thank you
thank you for the tutorial i hope i can use this to my present job as a warehouseman
This is helpful. I learn alot. I hope you can upload a video for this inventory excel, on how to in and out the product with the use of barcode scanner thanks a lot. ❤😇
such an amazing video, real great of you to do this, can't wait to get mine started. I've literally searched the whole of youtube for something simple like this
Thanks, check this out too, it allows you to add more items coz it's more dynamic and expandable. It has a sample template too that you use right away.
Cris Minas how do I create the in and out on a monthly basis, for jan, feb, mar etc. Do I need to create a new sheet each month and copy everything over. Thanks
@@SuperKris1981 create and save as template and every month you can add new . its tiresome agreed
Nice example... you would be better off turning all of these into TABLES -- to make dynamic so you can keep adding inventory and sales for hundreds of transactions. Also just a note... to hard to type "$" to lock cells.. when the range is colorized just press the [F4] key to lock cells. just to save time
Amazingly, I needed this workout today as I'm preparing a new shop inventory system
Patrish Casila
I've learned about the vlookup today. It has been a great help because we have a research and watching this made it easy for us. I also learned the importance of the dollar sign, and other functions of the ms excel.
what is the importance ?
great job, but what happens when the received list is full? we need to delete the input in the row to make place for new one but that changes to quantity in the storage too,right?
Thank you so much for helping us get control of our inventory and making it simple for us. I have a question with regards to the price/item. In reality, the price of an item is a variable. How do we account for it in this system?
Just make the price variable for each purchase/incoming or sales/outgoing. The system is only displaying that value for ease of viewing. It's not required.
i hope you add tutorials like your are speaking at the same time
Will it still work if I put the stock table on another sheet?
Me: Thanks youtube for suggesting me a master piece ..thumps up dude..!!!!
Just what I need. However, how do I like make an average of the price... I purchase the same kind of products from different stores (with different purchase price)? Was hoping you could help me out on how to go about it. Thanks
exactly what im looking for, thank you so much.
I think I finally understand VLOOKUPs after this video!
Love it. I m going to put it in python and mysql database with customer.
this template doesn't consider price changes for one item over time. it is great but only for constant prices over the year for each item
I mean, you can just not include the price in the VLOOKUP code and enter the price you purchase it for as you go. That seems like the easiest solution to me.
Thanks for sharing! What is the best way to add stores, to which I source products for further sale?
Hi Cris, this is an awesome video. What troubled me is my incoming stock has different batch numbers. How do i manage the inventory with the ever changing batch numbers?
Thanks man!These is so great you help me learned this thing in such an easy way...
Is this possible with changing dates in rows. For example in sheet received goods different rows different dates, automatically filled in different rows in stock sheet? The same way for sales.
You shouldve use drop down list on codes and names, because if you have many items it will be hard to memorize all of those codes. :)
Greetings! Can u help me? My problem is the stock inventory is not changing but im already update the received item table. Can i know how to solve it? Thank you
HI THANK YOU FOR SHARING THIS APPLICATION FOR INVENTORY BUT IS NOT WORKING THE CODE UNTIL 200 ITEMS WHEN IN ENTERING THAT CODE WHICH IS NOT AUTOMATIC.
i like your video how find out total stock purchge in my sheet
It's amazing!! Thank you so much for sharing this content
Can you do for aswey management?? Please?? Like how to maintain an asset register
Can this work also with a scanner gun? For example we wanna use barcodes to scan in and out supplies.
How do you add a different colour for each product, so that when I get a sale and input A1 for example it automatically shows up with the colour I have chosen for it as well? thanks
Use conditional Formating if value is equal to
Can you use this setup with a barcode scanner if so how would you intergrade it thank you
Good tool Cris, I am really appreciated that but could you please share us how to control the PRICE as we know that same product but may not same price.
I am looking to hearing from you soon.
Create pricelist in another sheet and use index function. Buy hey, If you're trying to create your inventory, i have free to download check my new video 😊
Thank you dear for such type of video
જોરદાર. બવ સરસ માહિતી છે 🥇.
What did you do actually to Fix the Error i.e. What is -R & -S actually written for? (I think Received and Sale) but how Formula understand this?
how can I automate Code No in stock inventory table?
also if i have multiple entries "A1" in purchase table, how do I combine them in Stock inventory table?
hi may i ask? how to make it timely? Meaning it is updated daily to see the transaction
what if the sales exceeds the received table. on that case how do we insert a function not to enter more quantity than received items ?
your inventory look like easy but I would need to calculate profit and lose at the end of the month ect.
You made working Excel bad ass! 👍
This REALLY helped a lot..thnx
This has really helped me but i was wondering how i could add a reorder level so that when stock becomes below a certain amount it would be highlighted? do you know how to do this
I would create a new column prior to the Comments column called ReOrder and that have the target numbers of when you get to that point or below is when you want to re-order product.
The cells I reference will differ from the video as a new column has been inserted.
Now in the Stocks inventory "table" highlight the Stocks column from $AA$:$AA$14 then on the Home tab, Styles group choose conditional formatting, New Rule.
Your range will be what I listed above and your formula is =VLookup($v1,Items,5,0)>=$AA4 then choose whatever fill color or formatting you want.
Remember hitting the F4 key will cycle through where the $ symbols can be placed.
Robert Harris Great stuff! 👌 Thank you.
its like a Movie....its so entertaining
Can u Help me . quantity is not appear in stock list even in add in stock in.
Thanks you so much for your sharing 🙏🙏🙏
I did this and it works! thanks for this.. big help for my small business.. but how about if I add the wholesale price of the products ( so I know how much I gained) ? how will I do it? please help me..thanks
in the last table. you have to add 3 more columns .your receiving price.sold price.balance final profit amount you got..this will not affect the vlook up table.
@@homahealthcarecenter thank you so much
Thank you Cris
I am facing one issue in this sheet if we have same product or item code at different price how we can deal in this senerio?
Thank u so much. U helped me a lot 👍👍👍
Also how to link up the customer list with the quality
As some item is sold so we write in front of customer details.
So the same qty should get deducted from sales qty
Can we use this for raw material?
In the last stock inventory box why don't you use simple formula like =+k4 in the received field and =+r4 in the sales field? Cause your formula and this simple formula do the same. Please tell why????
Thats great.Simple. Thumps up man.
thank you very much for this video
hi, sorry but how do i expand my items list if ever i want to add products to the list of items i sell?
can help me how to less example 1box=48pcs and i have 10box... what if i less 50pcs... can you help me with the formula?
Great Video .. It would be great if you please upload any video on Double Bin Inventory System in EXCEL
Thank you xoxoxo.U help a lot for my Quiz😂😘
Better to use index match nesting instead of vlook up. It more flexible. 👍
Yet more complex
It looks good and simple, I like it.
Hi Cris Minas one question, Why do i get number -20 in my Stock list when i put number 10 in sales list?
Edit: nvm i realized i was on the wrong list. BTW great tutorial i really loved it and really simple im gonna be watching more of your excel tutorials Keep up the good work Cris Minas :D
Put the vlookup formulae inside an iferror formula and set it to show blank if error. Just a user friendly small mod.
How can I make changes like unprotect category,items and so on
If I want to add new items in item list then it will auto update in sales and stock,, how???
Wow Amazing Tricks
Thank you for sharing 😊
well done, u did a great job. is it possible to have this file ?
Yes, link is in the description.
Thank you so much sir!
well why is happend a1 and a2 item work entry in stock in this video and what u have done with R and S code what was that
Thank you.
Thank you very much for the very good lesson
Tip: You can use the data validation on the CODES column to make it easier
Simple and usefull! Best Regards!
Hey I’m working on this following your steps but my sales are not calculating. Any idea why?
I can make u ma'am/sir
Thanks so much sir
Wow thank you! I ve made my own.
well tell me if same item i have and when i entry the item there is difftrent size and colours in garms so how i do for the same also why is not showing balce money for the same stock
hello, how can i add cost of sales so that i can detect my profit?
Many thanks for sharing these special tips. :)
you are so amazing!
thanks for this video
Very helpful thank u😊
Thank you, so easy and well explained
helps me alot!
itry to follow your step but my vlookup formula always show error dont know why
V4 is a problem, product name is repeated. I think you should choose U4
I updated all the sheet but when I put received qty then in stock inventory isn't update automatic. ..
Why? Plz reply me thanks
If you are using table, your formula should be =SUMIF(RECEIVEDproductrange,STOCKS1stproduct,RECEIVEDproductquantity).
If not check the placement of the dollar signs.
You can also put it fast after selecting the range press Fn+f4
HELLO SIR IM USING YOUR PROGRAM BUT IT COMES TO STOCKS INVENTORY WHY SHOULD NOT link the receiving into stocks inventory when I input the received still zero,please some ideas if there are some other link. thank you
Im using vlookup and sumif formula here kindly check your inventory vlookup formula.
Awesome
i want to add some more materials in items after middle of the year .The "items" expandable ?
ua-cam.com/video/96i_E_JRvL4/v-deo.html
Thanks 🙏🏻