On the next part of the series we can, among other things, insert costs, and conditional formatting to our inventory. Anything else missing? Trick question, I know this is just the beginning, but still, please let me know your wishlist
Mate i got this set up all as you told, is there a way to get a print of each sale individually that way a custumer can be offered a digital recipt with a copy ?
Thank you for making this! I've been frustrated with Google Sheets not cooperating, as I'm trying to create an inventory system for a recently opened medical practice. This tutorial is exactly what I was hoping for as a great starter, which I can add to it to make it work well. And also simple enough that others in the practice can take it over with little confusion! And so much faster to understand than some Udemy courses I signed up for!
Thank You for this. I am a new home base business and needed a format to keep track of everything without having to pay a bookkeeping service. God Bless
Hi. This is a very good series, much to learn. Based my sales and purchases for my startup on this. Works very well. I highly recommend to watch this series for everyone who works with google sheets, this comes near to a database approach and the query formula should be explained soon too :)
Thank you very much. I like the thought you had at the end about creating maybe you said HTML? Sorry newbie but I have a large amount of stock that moves through my store input and output. So I would like to no a cleaner way using less lines to track! Thank you again.
You can check these 3 videos that may give you some ideas ua-cam.com/video/R7sf9Rduz58/v-deo.html ua-cam.com/video/jG5RObEPBq0/v-deo.html ua-cam.com/video/jwGYOYeNKsA/v-deo.html Kind Regards
Extremely helpful, thank you! Can you explain how to can keep track of stock at different locations? Like one master list of everything but sold at multiple cites? :)
It is shown "REF" error code when input the SUMIFS formula and the amount of the stock if following the order of the Product Sheet, It is not following the what we Sort.
I just finished the code at 15:00 my A2 in my stocks page says #N/A and this is my forumla: =FILTER('Input / Output'!A2:A,'Input / Output'!C2:C="Input") What am I doing wrong?!
Thank you for this tutorial! I followed part 1 and have created the sheet and it is working to a point. On Input/Output, where I have added a new purchase of a previously bought item i.e. it is already listed in the Products tab, the issue is that it is duplicating in the Stock tab. How can this be remedied?
I noticed that when a product has only output figures, it doesn't reflect in the stock sheet. What can be done to get around this pls. Thanks for the tutorial
Hello! Yes, I´ve set it up this way to avoid showing products without initial qtys bought If you want to show them, change in the first column of the Inventory sheet, in the second row change this formula =SORT(UNIQUE('Input / Output'!A2:A)) with this one =SORT(UNIQUE('Products'!A2:A)) Regards!
hello, what if the items have sizes and colors? how can we create a more accurate inventory sheet for this? would really appreciate a reply! thank you!
Hi In that case, you dont need the vlookup Just do the dropdown selecting the range where the product names are The stock will work the same, always referencing the column with the names Regards!
hi PS how are you, thank you for your giving us the best knowledge I am from Pakistan, and I have a problem, there are a lot of videos for stock inventory, but I never find Stock inventory for manufacturing units, From raw materials to finish Goods, For example, I have 3 employees I am giving them raw material and they are giving me finish product how can we deduct raw material by finish goods when they give it back, and how to maintain my stock inventory left with my employee and in my warehouse hope you will resolve my issue i am much needed this time
A succinct tutorial. Thank you. Unfortunately I am getting an error in the stock sheet. When "Input" is selected from the drop down of the "Input/Output" sheet for a new purchase, in the "Stock" sheet, the item's total is not updated, rather a new line of the item is added, thus duplicating the item. This does not happen when "Output" is selected. don't know why because I followed the steps outlined. Any idea why this is happening? Thank you once more, really appreciate your tutorials.
I am also getting the same issue. Would be great to know a workaround. EDIT: I nested the FILTER function within a UNIQUE function to get rid of the duplicates.
Hello both You are right! I hadn´t noticed! Thank you for the comment Jerome´s solution is excellent This is the result =UNIQUE(FILTER('Input / Output'!A2:A,'Input / Output'!C2:C="Input")) Another option is to drop the FILTER altogether and just add an UNIQUE, like this =UNIQUE('Input / Output'!A2:A) You could even go one step further and wrap in a sort function =SORT(UNIQUE('Input / Output'!A2:A)) Thanks again for pointing the improvement Regards!
Thank you for the video I have a question tho if I am operating on 1000s of product which I don’t scan ,will I have to input it manually in purchases sheet by selecting reference???
filter funtion is showing multiple value in stock like two reference "ts005" if i input two times with 2 dates. how do i fix it to appear only one value with filter function?
Hello, I am at 17.20 in the video and I am trying to input the formula for the stock sheet for input. When I input the formula it only updates three of my items, the rest say 0. I cannot figure out why when I have units inputted in the input/output sheet. I do not have any items outputted at this time. Do you have a recommendation of what I could be doing wrong? Thank you
Having an Error when doing the formula on Stock page. When i add more than one item the top ones say #REF! Array result was not expanded because it would overwrite data in A3? any ideas?
How can I reduce the redundancy of the products displayed on the Stock tab? I have noticed that when I journal multiple inputs of the name products (on different days) on the Input/Output Tab, those products tend to duplicate on the Stock Tab.
@@practicalsheets If I have 2 rows with the same reference code input, but on different shelf, in the stock sheet I will receive only the first shelf If I use vlookup.What formula should I use to receive in the stock sheet 2 rows with each shelf stock?
Thank you for this ! Is there a way I can susbtract my inventory from a certain location? Let's say I have 10 in my location PROD01 and I want to take 5 from it to put it in another location ... Thank you
Hello, thank you for this! When I enter the formula for the stock sheet, there is an error of 'Wrong number of arguments to SUMIF. Expected between 2 and 3 arguments, but got 5 arguments'. Is there a way to fix this?
Hello! Glad you solved it MAke sure that all ranges go from row 1 up to open For example Range1: A1:A Range2:B1:B Range3:F1:F Regards!
2 роки тому+1
Liked and Subscribed immediately. Great job, I'll be doing this for my store right now. Only if you see this in the future, how would you do this if you sold t-shirts with different sizes? I'll do this REF PRODUCT TS0011 Mastodon Size XS T-Shirt TS0012 Mastodon Size S T-Shirt Is this the right approach or is there a better way? Thanks for your tutorial!!
Hello Julio! I think it is the best approach. What we could do is to have different fields for size so you can group later in your dashborad, but I think it should be handled as separate references Regards!
How do you combine repeat values in Stock such that it shows one added amount? Say for example you had multiple "Input" entries for Mastodon T-shirt, I want to see it as one entry in Stock and the value of Inputs added. How do you do that? Can you please explain
Hello! I think this is how I did it in the video. The process is: First bring unique values with UNIQUE function Then do SUMIF to add all of the input values of that product Regards!
has anyone else gotten an array value error??? i cant seem to fix that no matter how many times ive copied the instructions, even tried googling the issue.... help!!!!
Hello! I think most functionalities work with excel. We would just have to change UNIQUE, although this function is included in the latest 365 versions Regards!
If I just fucking wanted to make a list and take 30 minutes to do it id just write a list. Not useful at all. I need to add every item in a machine shop and this method takes u 21 minutes to put 5 t shirts. Useless method for messing around nothing long term
On the next part of the series we can, among other things, insert costs, and conditional formatting to our inventory. Anything else missing? Trick question, I know this is just the beginning, but still, please let me know your wishlist
Sir when you are going to update the second part ?
Please do it soon
@practicalsheets really want to know how it works
Mate i got this set up all as you told, is there a way to get a print of each sale individually that way a custumer can be offered a digital recipt with a copy ?
Thank you for making this! I've been frustrated with Google Sheets not cooperating, as I'm trying to create an inventory system for a recently opened medical practice. This tutorial is exactly what I was hoping for as a great starter, which I can add to it to make it work well. And also simple enough that others in the practice can take it over with little confusion! And so much faster to understand than some Udemy courses I signed up for!
Thank you for the nice comment!
Thank You for this. I am a new home base business and needed a format to keep track of everything without having to pay a bookkeeping service. God Bless
Hello Danita!
I´m so glad this is helpful.
Please be attentive of new videos wher I will continue to improve it
Regards!
Thank you for the well explained videos. I am just started my business and your videos help alot.
Thank you for the kind message!
Hi. This is a very good series, much to learn. Based my sales and purchases for my startup on this. Works very well. I highly recommend to watch this series for everyone who works with google sheets, this comes near to a database approach and the query formula should be explained soon too :)
Thanks for the kind words!
Yeah, QUERY is the most similar Sheets will behave as a proper database
Regards!
Thank you for a well detailed lesson. It has really enlightened me.
Thanks for the kind comments!
@@practicalsheets I would also want to know how you created the other folder (list) of items you incorporated later.
may you point me to the minute please?
Regards! @@mburukiongo8457
Thank you very much. I like the thought you had at the end about creating maybe you said HTML? Sorry newbie but I have a large amount of stock that moves through my store input and output. So I would like to no a cleaner way using less lines to track! Thank you again.
You can check these 3 videos that may give you some ideas
ua-cam.com/video/R7sf9Rduz58/v-deo.html
ua-cam.com/video/jG5RObEPBq0/v-deo.html
ua-cam.com/video/jwGYOYeNKsA/v-deo.html
Kind Regards
Wow!!! so excited that i found this. Thank you so much
Thank you for commenting!
Great video!! I've created a seperate sheet for both input and output and i'm stuck on how to add or substract the value of input and output !!
Excellente. Were you able to do it?
Regards!
Simple, clear and clever. Thanks !
Thank you so much for your comment!
Extremely helpful, thank you! Can you explain how to can keep track of stock at different locations? Like one master list of everything but sold at multiple cites? :)
Hello!
Yes, of course
here is the video
ua-cam.com/video/kqwymHePHzk/v-deo.html
Let me know how it goes
Kind Regards
It is shown "REF" error code when input the SUMIFS formula and the amount of the stock if following the order of the Product Sheet, It is not following the what we Sort.
Thank you 🙏👍🏼
Is there any way to keep your stock but reset the input/output page so it doesn’t get really long?
Thank you for this, do you have a video showing how to manage stock if you have differents sizes and colors for each product
Hello
Yes, I have the beginning of a sistem for clothing. Here is the link
ua-cam.com/video/G5LzAL3NzMs/v-deo.html
Regards!
Graciaaaas! Sigue haciendo este tipo de videos por favor
Thank you very much for your tutorial. I enjoyed. How to use this same pattern for multiple locations. (Multiple shops)
Hello!
Thx for the suggestion!
WIll include it in future videos
Regards!
Hi, there I am using this formula =FILTER('In - Out'!A2:A7,'In - Out'!C2:C="In") but it says that this is invalid, can you please help me out?
Very Good. If I take input in two different date of same item then stock shows wrong value
I have the same issue. The item is being duplicated on the Stock tab. Hopefully the creator can advise us!
Could you explain why I'm getting this error? No matches found in filter evaluation?
Where are you using FILTER
Kind Regards
I just finished the code at 15:00 my A2 in my stocks page says #N/A and this is my forumla:
=FILTER('Input / Output'!A2:A,'Input / Output'!C2:C="Input")
What am I doing wrong?!
Did you find the error?
Regards!
I like the simplicity of this. One thing I would like is an image of the product. Is this possible?
I think we may do it. I´ll write it for a next part
Regards!
Thank you for this tutorial! I followed part 1 and have created the sheet and it is working to a point. On Input/Output, where I have added a new purchase of a previously bought item i.e. it is already listed in the Products tab, the issue is that it is duplicating in the Stock tab. How can this be remedied?
Do you have UNIQUE as your formula in cell A2 of your stoch tab?
Kind Regards
Thank you. I watched video 2 where you fixed the issue!
I noticed that when a product has only output figures, it doesn't reflect in the stock sheet. What can be done to get around this pls.
Thanks for the tutorial
Hello!
Yes, I´ve set it up this way to avoid showing products without initial qtys bought
If you want to show them, change in the first column of the Inventory sheet, in the second row
change this formula
=SORT(UNIQUE('Input / Output'!A2:A))
with this one
=SORT(UNIQUE('Products'!A2:A))
Regards!
If there is repeated purchase on same item in different date then filter function is not working in stock sheet.
Hey great Video, i would like to add pictures of our Control sheet to the rows. Any idea how to do that
Hello!
Thank for the suggestion. Will add it in future videos!
why other cells are not working once l put a ref they don't automate product l have that error in two cells
Could you explain in more detail the error please?
Regards!
hello, what if the items have sizes and colors? how can we create a more accurate inventory sheet for this? would really appreciate a reply! thank you!
What formula and stuff should we use if we just want to have the product and no ref #?
Hi
In that case, you dont need the vlookup
Just do the dropdown selecting the range where the product names are
The stock will work the same, always referencing the column with the names
Regards!
When adding new product and adding input and when we do some output, the stock is not updating. How can i solve this
hi PS
how are you, thank you for your giving us the best knowledge I am from Pakistan, and I have a problem, there are a lot of videos for stock inventory, but I never find Stock inventory for manufacturing units, From raw materials to finish Goods, For example, I have 3 employees I am giving them raw material and they are giving me finish product how can we deduct raw material by finish goods when they give it back, and how to maintain my stock inventory left with my employee and in my warehouse hope you will resolve my issue i am much needed this time
Hello!
Thx for the kind words!
Have you seen this video?
ua-cam.com/video/IgcVbzy4H1U/v-deo.html
Kind Regards!
Hi this is brilliant
I need help that the script is not working when I click create po nothing reflects on the PO sheet
Can any one guide me please
A succinct tutorial. Thank you. Unfortunately I am getting an error in the stock sheet. When "Input" is selected from the drop down of the "Input/Output" sheet for a new purchase, in the "Stock" sheet, the item's total is not updated, rather a new line of the item is added, thus duplicating the item. This does not happen when "Output" is selected. don't know why because I followed the steps outlined. Any idea why this is happening? Thank you once more, really appreciate your tutorials.
I am also getting the same issue. Would be great to know a workaround. EDIT: I nested the FILTER function within a UNIQUE function to get rid of the duplicates.
Hello both
You are right!
I hadn´t noticed!
Thank you for the comment
Jerome´s solution is excellent
This is the result
=UNIQUE(FILTER('Input / Output'!A2:A,'Input / Output'!C2:C="Input"))
Another option is to drop the FILTER altogether and just add an UNIQUE, like this
=UNIQUE('Input / Output'!A2:A)
You could even go one step further and wrap in a sort function
=SORT(UNIQUE('Input / Output'!A2:A))
Thanks again for pointing the improvement
Regards!
Thank you for the video I have a question tho if I am operating on 1000s of product which I don’t scan ,will I have to input it manually in purchases sheet by selecting reference???
you should paste first all the references (just the whole column), and then the quantities
Kind Regards
Dear sir
I facing a problem realted this sheet ,whene we put put same ref. In two different date Than it show me at both place with same added value'
Hello!
Can I see how you´ve set up the formulas? It may be an error there
Regards!
You're amazing!
Thank you!
HI when i click in data vaidation my pop up screen doest look like yours dont know if you could tell me why hope u answer me thanks
Hello Miguel
The thing is that the interfase of Google has changed a lot in these 2 years
I need to re-do the video
Regards!
filter funtion is showing multiple value in stock like two reference "ts005" if i input two times with 2 dates. how do i fix it to appear only one value with filter function?
Hello!
Did you change it to UNIQUE?
Regards!
In the inputs and out put section when i select a ref number more than once the name doesn't show up again, how do i fix this.
You need to drag down the VLOOKUP formula up until the last row
Regards!
Thank you sir, I got a problem. I follow your step at 15:02 and when I drop down to copy the cell formular I got "Array result was not expended"
I'm having the same issue. It's a shame he did not respond to you.
Hello, I am at 17.20 in the video and I am trying to input the formula for the stock sheet for input. When I input the formula it only updates three of my items, the rest say 0. I cannot figure out why when I have units inputted in the input/output sheet. I do not have any items outputted at this time. Do you have a recommendation of what I could be doing wrong? Thank you
So sorry for the late response Samantha
We´re you able to fix it?
Regards!
Really good ❤
Thank you!
Thanks bro
Thank you for commenting!
Having an Error when doing the formula on Stock page. When i add more than one item the top ones say #REF! Array result was not expanded because it would overwrite data in A3? any ideas?
Delete everything below A2
Regards!
hi in input output sheet when we input same product twice then in stock sheet it also two times fix this???]
Use the function UNIQUE in the first column of the stock to fix
Regards!
How can I reduce the redundancy of the products displayed on the Stock tab? I have noticed that when I journal multiple inputs of the name products (on different days) on the Input/Output Tab, those products tend to duplicate on the Stock Tab.
Nevermind, I found the answer in the Part 2 Video. Thank you!
I´m glad you solved it!
Regards!
Hi.. Can you please explain . if the same "Ref" has multiple times as "input" how can we avoid repeating it on Stock sheet ?.. Thanks..
Hello!
Using the UNIQUE function
Regards!
@@practicalsheets I tried using Unique but it doesn't work.. Could you please help me to write the formula here ?
@@amilapereragtaenergy Can you show me the formula you are using and in which cell?
Regards!
Mine is not accepting the 0, keep getting errors in the formula?
I had something similar but it is getting very slow as i record more and more data, can you please help ?
Hello Nasih!
How many rows are we talking about
Regards!
Hi! Nice work and explanation. Could you kindly help me in integrating a FIFO method in this inventory management app. thnx
Hello
I´m working on it. At least I´m trying to ;) Hope it helps!
Regards!
can you show us how we can insert a form, for example if operators want to introduce when they take something from the stock?
Hello!
Do you mean a form in the same Google Sheet, or in a web page?
Will they do it from their phone or in a computer?
Regards!
@@practicalsheets most of the time from their phone
@@andreitarba5202 So we would need to address this with a google form or a webapp. Hope to do a video soon. Regards!
@@practicalsheets any news?
@@practicalsheets If I have 2 rows with the same reference code input, but on different shelf, in the stock sheet I will receive only the first shelf If I use vlookup.What formula should I use to receive in the stock sheet 2 rows with each shelf stock?
Thank you for this ! Is there a way I can susbtract my inventory from a certain location? Let's say I have 10 in my location PROD01 and I want to take 5 from it to put it in another location ... Thank you
Later in the series, I do a video on warehouses, hope it helps you
Regards!
Sir why my stock page same product enters multiple times row ?
Hello!
Make sure you have the UNIQUE formula in place
Regards!
I followed every step and I do not why but my stock is duplicated, I did it twice and I got the same result.
Use the formula UNIQUE() in cell a2 of your stock sheet
Kind Regards
FILTER('INPUT/OUTPUT'!A2:A;'INPUT/OUTPUT'!C2;C="INPUT") ..rong aara he
Hello, thank you for this! When I enter the formula for the stock sheet, there is an error of 'Wrong number of arguments to SUMIF. Expected between 2 and 3 arguments, but got 5 arguments'. Is there a way to fix this?
I have fixed this issue now thank you. However now I am getting an error of 'Array arguments to SUMIFS are of different size.'
Hello
Thx for commenting. You need to differ between SUMIF and SUMIFS
Regards!
Hello!
Glad you solved it
MAke sure that all ranges go from row 1 up to open
For example
Range1: A1:A
Range2:B1:B
Range3:F1:F
Regards!
Liked and Subscribed immediately. Great job, I'll be doing this for my store right now. Only if you see this in the future, how would you do this if you sold t-shirts with different sizes?
I'll do this
REF PRODUCT
TS0011 Mastodon Size XS T-Shirt
TS0012 Mastodon Size S T-Shirt
Is this the right approach or is there a better way? Thanks for your tutorial!!
Hello Julio!
I think it is the best approach. What we could do is to have different fields for size so you can group later in your dashborad, but I think it should be handled as separate references
Regards!
Thanks a lot boss, please help me with the template
Hello!
Thank you
You'll find it in the patreon page (of all the more than 10-part series)
Kind Regards
How do you combine repeat values in Stock such that it shows one added amount? Say for example you had multiple "Input" entries for Mastodon T-shirt, I want to see it as one entry in Stock and the value of Inputs added. How do you do that? Can you please explain
Hello!
I think this is how I did it in the video. The process is:
First bring unique values with UNIQUE function
Then do SUMIF to add all of the input values of that product
Regards!
has anyone else gotten an array value error??? i cant seem to fix that no matter how many times ive copied the instructions, even tried googling the issue.... help!!!!
I want to have part 2 please
Excellent!
Do you mean thi?
ua-cam.com/video/S6I3TEfNY_o/v-deo.html
Regards!
Hi, can the Google sheet to be “excel-conform” in order to guarantee full functionality?
Hello!
I think most functionalities work with excel. We would just have to change UNIQUE, although this function is included in the latest 365 versions
Regards!
I have an issue doing vlookup in another sheets it says !#value error
Can I see your vlookup?
Regards!
@@practicalsheets I just copied what you did in the video and I used vlookup from a different sheet and still error
@@aaaaaaaaaaaaaaaa7498 Can you show me the vlookup?
Regards!
Data validantion in not work
Hello!
The data validation has changed a bit in the past 2 years, but it should work. I will do an updated version
Regards!
Useless
Thanks for commenting. I´m sure there are plenty other videos that may be of better use for you
Regards!
If I just fucking wanted to make a list and take 30 minutes to do it id just write a list. Not useful at all.
I need to add every item in a machine shop and this method takes u 21 minutes to put 5 t shirts. Useless method for messing around nothing long term
Do not do it then gtfy
But what if my product have different sizes?
You can see this video
ua-cam.com/video/G5LzAL3NzMs/v-deo.html
Regards!