Thank you for providing such helpful content. I'm currently working on modifying the template, but I've run into some difficulties. Could you please recommend any specific videos or resources where I can learn more about making modifications? My main issue arises after the 36:18 mark, as the worksheet design includes multiple sheets and I need to delete certain columns in both the customers and suppliers sheets.
Hi and thanks very much. If you delete columns, this requires modifications to the code, formulas, formats and functions across many macros within the code, so there would not be a single video to show you how to do this. You may want to hide the columns. To learn more about VBA I have a beginners series here ua-cam.com/play/PLIBeRriXvKzDTzNp5IME4-oXXCrIjvV4z.html If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet) You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project. I hope this helps and thanks so much.
hello sir, When I was learning excel, I tried creating a system to take the input of students data and create a profitability chart. Your videos are very inspiring.
I just completed my first week at a new job in the warehouse and the weekly report in nuts. I spent all day cutting a pasting from one work book to another, then adding things up. It's nuts... I feel like I'm wasting so much time doing the same thing over and over. My goal is to somehow automate this report from what I've already put on excel. Wish me luck...
I am sure you are going to do great. If you need help at any time, or jus looking for a straight answer to a question, feel free to join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
hi I am really happy about your work, Quick question, How can I reduce and add quantity to the product for every purchase and sales i have? so If I purchase items, quantity should add to the product, and if I have sales quantity of the product should be deducted. how can you do it?
Hi and thanks very much. I show you how to do that in my recent Userform Point Of Sale training here: ua-cam.com/video/9biCBLwEngM/v-deo.html I hope this helps and thanks so much.
Hi and thanks so much. Essentially the Total Amount Purchased for a given item - The Total Amount Sold for a given item is the Quantity On Hand. I hope this helps and thanks so much.
HI and thanks for your comment. I am glad you were able to get it. As an extra security measure, Microsoft disables macros from workbooks downloaded over the internet. You can fix this once and for all by following the steps I have outlined on this page www.excelforfreelancers.com/how-to-fix-macro-blocking-issue/ I hope this helps and thanks so much.
Hi Randy, thank you for the work here. on the Customers sheet, how did you get the asterisks into the criteria tables? and how do you create these criteria tables?
Hi and thanks for your comment. The Asterisk are the wildcard characters used in the advanced filters. So you would manually type them in along with the value of the searched cell. These are common in Advanced filters. I have a training on that here: ua-cam.com/video/YuCrEWU2Tm0/v-deo.html I hope this helps and thanks so much.
This is my first time studying macros. This is so informative, and it helps me a lot. I hope you can answer what I can do if I need more colums in purchases, sales and products. Upon adding colums on the format itself, what should I do in VBA? Sorry. I'm new to this. (Freshly graduate, colleges don't teach this. 😢) Again thank you so so so much!
HI and thanks for your comment and I am glad you enjoy the training. Any changes to columns or rows will require multiple changes to the VBA Code, functions, formulas and formats so its more than I can answer in a UA-cam comment. You may want to check out my Beginners VBA Series here ua-cam.com/play/PLIBeRriXvKzDTzNp5IME4-oXXCrIjvV4z.html I hope this helps and thanks so much.
I really enjoy the quality of your videos. This is a great system for a small starting out company. I do have a question. In some states there are two different sales taxes, one for groceries, and a second for all other purchases. Have you created a video covering how to apply different sales taxes based on the category of the item? Here in Tennessee we have two different sales tax rates.
HI and thanks very much. If you want to add additional sales tax, you can add one additional row and also one additional tax settings in the Admin screen. I hope this helps and thanks so much.
This video is amazing. Are you able to integrate a pick slip, packing slip/despatching option where once despatched this reflects it in the stock on hand.??
Hi and thanks. Yes sure, anything is possible. Please feel free to download the free template and customize however you like. If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet) You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project. I hope this helps and thanks so much.
I have enjoyed it following each step but got stuck when it comes to the rules part at 30:44 because there's an error apparently it says it must have a value... but i did enjoy trying to follow the steps. is there any add ons that needs to be added to excel?
Hi and thanks so much. I am glad you enjoyed the training. The formula would be something like this =and($D8"",Mod(Row(),2)=0) Feel free to download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Hi and thanks. Can you provide more details of the issue or challenge you are facing since I am not quite sure what the problem is. I hope this helps and thanks so much.
@@ExcelForFreelancers for example like this, I have an item called a motorbike footrest and it has different brands. I want to make the product name in the sales order section management inventory, row 9 column D the product name and in the description I just have to select the brand. Thank you and sorry if my English is still a bit lacking✌️✌️ Update: i did fix the issue, but now im facing a problem with the stock because the product name bassicaly contain a duplicate lolz Update 2: Finally i fix it. Thanks for the Tutorial Man it means a lot
Hi and thanks very much. You will want to create one single product (not multiple) then inside that product you can have a drop down list and select the brand. I hope this helps and thanks so much.
Thanks Randy for that new video, it's awesome what can be done with Excel ! Just a small question, does it work with older Excel versions (2019, 2016,...) ?
Hello, I haven't watched the entire thing yet but I just wanted to know ahead if this is possible to be done in a way that multiple people can interact with the inventory system and update in real time? I'm new to excel and I'm not sure if it can do that besides just downloading the file and passing to to others. I was also thinking if this is doable in Google Sheets instead?
Hi and thanks. Yes for sure its possible. You can use my Share & Sync addin here: ua-cam.com/video/G9VqF7yN0Oo/v-deo.html I hope this helps and thanks so much.
Im in the process of working through this video now. Making some changes because the supplier menu doesn't matter to me, but being able to capture my recipes to get a total cost of ingredients does. But, i am curious if you think this DB can be translated to Google Sheets? Im not a Sheets person, but was wondering because of the anywhere availability of Sheets.
Hi and thanks very much. If you want to be able to use your data anywhere, you can follow my training on this. To share and sync your macro-enabled Excel workbook, please watch this training: ua-cam.com/video/G9VqF7yN0Oo/v-deo.html I hope this helps and thanks so much.
Hi and thanks for the Selected Item Row I use the selection change event and I bring over the item ID The row is generated from a formula such as =Iferrror(Match($B$2,Order_ID,0)+3,"") Please update the ranges and check the cells for hte actual formula. You can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
hello, thank you so much for this useful video! i wanted to ask, when writing the formula for the Total Sales in Sales by Month, shouldn’t the ending part in EOMONTH function be: ROW()-2,1),0)) instead of -1, since by then putting 0 we are staying in the current month? ive been trying to figure that out and would greatly appreciate your help to understand😁
Hi and thanks so much. If we want to find the end the month, then yes, it should be 0, a negative number would be months before and a positive # would be months after. I do not remember this exact instance however I will check on it. I hope this helps and thanks so much.
@@ExcelForFreelancers thank you so much for the swift answer. so if with 0 in the EOMONTH function we are staying in current month (january), i think it should be ROW()-2 instead of ROW()-1at 2:09 since ROW()-1 gives us february instead of january. i also checked with the order total in order list for those months and the given sum seems incorrect. or am i getting this completely wrong haha
Hello Randy, Thank you for the Inventory Management System. I have a question, do you know where is a problem when I add new Supplier and save it for sure, I can see it was added in the suppliers sheet but when I want to make an Purchase order, it will not add all infos for new supplier only the name but not the street etc. It is working for first 13 suppliers but it is not working for new I added. I dont have this problem with Customers btw. Can You help please?
Hi Barb, thanks for your comment, and I looked into this issue and I believe I found the issue. If you go into the VBA code into the InvMgr (Inventory Manager) sheet, you can find a row that says Range("E7").Value = Suppliers.Range("D" & NameRow).Value & ", " &Customers.Range("E" & NameRow).Value & ", " & Customers.Range("F" & NameRow).Value It should be changed to Range("E7").Value = Suppliers.Range("D" & NameRow).Value & ", " & Suppliers.Range("E" & NameRow).Value & ", " & Suppliers.Range("F" & NameRow).Value I will also update the uploaded version as well. Basically it was looking in the Customer sheet for the address and details instead of the supplier sheet. I hope this helps and thanks so much.
Hi and thanks so much. I made all resources, (icons, pictures, and all other items) available every week on our Patreon platform here: Https://www.patreon.com/ExcelForFreelancers I hope this helps and thanks so much.
Good Day Randy, I am pretty challenged with the Replace function on the shapes. Have you extensively discussed this in another video? If not, please help me out because I keep getting a mismatch error 13. I have tried to comment out sections of the code but i get a missing object error. I need your help. thanks
Hi and thanks. This is an area I know extremely well since so many of my videos i have worked a ton with shapes. If you can share the part of your code you are having trouble with I can try to help here in the UA-cam comments. If you want to share screenshots then please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
@@ExcelForFreelancers Sub Menu_Select() Dim MenuNumb As Long, MenuItem As Long With InvMgr MenuNumb = Replace(Application.Caller, Left(Application.Caller, 8), "") .Range("B2").Value = .Shapes("MenuItem" & MenuNumb).TextFrame2.TextRange.Text
With .Shapes("IMSGroup") For MenuItem = 1 To 6 .GroupItems("MenuItem" & MenuItem).Fill.ForeColor.RGB = RGB(75, 52, 206) Next MenuItem .GroupItems("MenuItem" & MenuNumb).Fill.ForeColor.RGB = RGB(41, 28, 118)
HI and thanks for the code. The first thing to remember is that when you run this code, it must always be from a shape. If you try to run this using F5 or the play button it will always return an error because there is no shape that called it" When you have a but, make sure that he "MenuNumb" variable is a whole number Make sure you have a sheet code named "InvMgr" Make sure you have a group shape named "IMSGroup" Also make sure you have a shape in your sheet that contains "MenuItem" and the MenuNumb when you have a bug, click debug and hover your mouse over the variables to check them all I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
HI and thanks for the additional code You will want to make sure that when you run this code it is from a shape. Not using the Play button or F5 inside VBA editor. Because it uses Application.Caller, this is the name of the shape that called if. If you try to use the F5 or the play button, there is no shape that called the macro Make sure you have a sheet code name InvMgr Also insure that you have a Group name InvMgr and inside this group you have the right shape names During the debug, hover your mouse over the variables to check their values. MenuNumb must be a whole number I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
THank you for this! One question though, I didn't get the part on how the rows column were removed. Could you let me know how it was done? also is your class offered even outside your country?
Hi and thanks, the columns are hidden using the .Hidden = True property. Feel free to download the template and inspect the code using the links in the description. I hope this helps and thanks so much.
Hi and thanks so much. Yes, in this training I will be designing the worksheet from scratch, during the training, so you can follow along. I hope this helps and thanks so much.
Hi and thanks you can link the workbook in the admin screen with the file path, however i do recommend having all of the data in the same workbook for speed and ease of use. I hope this helps and thanks so much.
Thank you very much for this Inventory Management System. I really love the free template as it would be a great way for me to track my sales, customers, purchases etc. But I'm not really a techy person and I tried editing the free template but I keep running into bugs. Please how can you help me? Thank you. I run a small business and it would really help with my limited resources.
Hi and thank you for your comment. If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet) You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project. I hope this helps and thanks so much.
Do you have any templates or demos on any inventory management/POS excel based systems? Looking to track bulk inventory moving in and out from a warehouse. Would like to have it automatically update the system when removing or adding to the overall inventory. An added plus would be to have barcode scanner capability. Thanks!
HI and thanks so much. I have an entire playlist of POS Systems here: ua-cam.com/play/PLIBeRriXvKzBohiJA68HSrtetepIR4Nna.html I hope this helps and thanks so much.
Question, you've got it setup to show 108 rows, what I want to know is there a way to have it show I say groups of 30 and then you have to hit a button to go to the next 30?
Hi and thanks so much. Yes it is certainly possible to add pagination to a range. I will try to add this in an upcoming training. Thank you for your Likes, Shares & Comments. It really helps.
Let me try this I have found it when searching for an excel sheet that can help me manage my small business. It's really a small shop so I want to try and am sure it will help me even with my accounting and audit
Hi and thanks there could have been a temporary issue we were working on however it should be fine. Can you clear your cache and try again or another browser? www.ExcelForFreelancers.com I hope this helps and thanks so much.
awesome for excel programmers (or whatever you call them). This is really free training. Grateful for your content. but for people like me, I'd rather buy the finish product :D
Hi and thanks so much. I am glad to help. If you want I can have this customized and secured for you. Please feel free to email your requirements Randy@ExcelForFreelancers.com I hope this helps and thanks so much.
I am trying to help this district hospital create a manageable pharmacy inventory. Love all your tutorials. Trying to work around to create one. ❤❤❤love all your work. Cherrs.
Hi Randy, please help with the way you came up with the criteria formula you deployed in the advanced filters. eg ="*"&'Inventory Management'!R4&"*". I have done some reading research but don't seem have it documented anywhere not even textbooks. I have a deaf friend who really is an enthusiast of excel, how I wish you could find him a transcription. He can read but impaired with hearing.
Hi and thanks so much. I have Closed Caption, in 13 languages, including English, on every video. I also have specific videos on Advanced Filters. I hope this helps and thanks so much.
Hello, thank you for this priceless piece of work. I am currently at 37:30 Please how did you get the Order_ID that match? Though I didnt see you show how you listed those functions in the name manager, i tried it but mine didnt pop up when i typed order_id Please can you show how you created the names in the name manager? Thanks
Hi and thanks, yes i created the order id with a Named range such as =OFFSET(Orders!$A$3,1,,COUNTA(Orders!$A$3:$A$997)-1,1) I cover named ranges in this video: ua-cam.com/video/yMz8S5ypBmg/v-deo.html I hope this helps and thanks so much.
Hi and thanks very much. I will be creating a new training with this feature in the near future. Thank you for your Likes, Shares & Comments. It really helps.
Hi and thanks for your comment. I am not sure what you mean by 'all the data that you have put'? The data I use in these templates is just sample data. You can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Hi Randy, your teachings are great however, they are more advanced. I think maybe the missing part of these trainings is only two things otherwise we could have the detailed training. I don't mind if these trainings can take 2 or three hours as long as it is something that someone can follow without asking so many questions and be able to develop something from your programs Here are my Suggestions: 1. Losing the link on the name managers and applying it to the formulars. This is missing more detailed on how it is done and where it is extracting from. This part is very important! 2. Prepopulated the VBA codes. It would be good to start all your codes on the same template you have created from the beginning. We get lost on the middle of the way and we ended up not completing these trainings because you explain code and not making it with us. Unless you have any other class where we can learn this that will be good since we are new babies on these codes. However, the finish products of your work are brilliant always.
HI and thanks so much for your comment and feedback. I do appreciate that. When it comes to basic VBA, each Saturday I create a Basic VBA Training and I have 36 VBA Basic trainings here ua-cam.com/play/PLIBeRriXvKzDTzNp5IME4-oXXCrIjvV4z.html Concerning writing code and design. On any basic template, I can create it from scratch during the video. I have an entire series of 'from scratch' training you may like here: ua-cam.com/play/PLIBeRriXvKzCHVxFYRSMTZu3PmnVn4ujb.html Please also keep in mind that the template I use for training is available for free using the link in the description. You may also want to check out our School Management series in which I show you how you can create your own Excel applications, from scratch, in an amazing series right here: bit.ly/SchoolManagerSeries
Hi Randy I need help, I do have a Excel Stock Control Sheet of my Pesticides, Fertilizers and Seed but know I want help on the following: On each block on the lands we uses pesticides, fertilizers, and seed, what I want to do is if I fill in the information onto my blocks how can it pull through to my stock control sheet automatically. Hope you understand
Hi Sonja, Thank you for your comment. If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet) You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project. I hope this helps and thanks so much.
Hey Randy thanks for all of this so glad i found your channel right whn i needed it. So having to ho through a crash course of excel as we lost our guru. These have been awsome to learn new stuff by building. But i have an issue i have not been able to figure out how you did it. Aroun an hour and eight mins into the video you go change all of your tables and update them to not include the column of row to be displayed. There has to be an easy way to do that without messing all of the tables and formats up. I can see removing column adding column reshaping box format can be done for each. I just wondered what approach you did so if i can fix mine i am building step by step. And i can fix in the future if i encounter it again. Thanks
Hi and thanks very much. I am glad you liked the video. Anytime I bring over data from an original table, if i have to edit that data, I generally bring over the database row. I will put that in an adjacent column. To hide this, there are 3 methods. (Hiding the column, matching the font color with the background color, or in this case I used a custom format with either two semicolons ;; if its only numbers, or 3 semicolons ;;; if it is a mix of numbers and letters. This is a custom cell format. Please feel free to download the sample workbook. I hope this helps and thanks so much.
Hi Randy! I really appreciate this video! Unfortunately I'm stuck at the minute 30:44 I literally rewrote the formula =and($D8"",Mod(Row(),2)=0), but it keeps telling me there's a problem. I'm really frustrated and I'm only at the beginning of the video :(. Please I need help because I don't know what I'm doing wrong
HI and thanks for your comment. The formula should be =and($D8"",Mod(Row(),2)=0) However with some language pages it will use a semicolon instead of a comma. I hope this helps and thanks so much.
Just love your work. I've downloaded the sheet and started customizing it for our Not For Profit club. Learning a lot as I go along. I'm not sure if it was pointed out previously, but in the version I downloaded the Suppliers kept showing the Customers address. I looked back over the video at 1:42:41 and you have left the E6 and E7 values as Customers.Range instead of Suppliers.Range. Sorry if it has already been picked up. Cheers, Barry from Tassie Australia
Hi Barry, thanks so much. I did see that and fixed the issue, and updated it for downloads. I also made other updates and added features on our Patreon platform update here: www.patreon.com/ExcelForFreelancers Thanks so much for your continued support
Hi @barrtmcki8489. Were you able to modify it for a non profit? can you share amended excel as I need it for a non profit foundation I'm volunteering for? it will be of great help.
HI and thanks. yes for sure. To share and sync your macro-enabled Excel workbook, please watch this training: ua-cam.com/video/f_1HsR9305I/v-deo.html I hope this helps and thanks so much.
Good Job Sir. I been thru th worksheet but i was challenged trynna enter data products and purchase but the qty does not show up in the "stock in" section on PRODUCTS sheet. Would you help me out Sir?
Hi, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
It is very hard to do jare, some formulas are not calling and it's frustrating, can you please tell us the worksheet you downloaded to get this done please sir.
Hi and thanks so much. You can use any version of Excel from 2007 to present, so its no problem at all. Thank you for your Likes, Shares & Comments. It really helps.
HI and thanks so much. USB Bar Code Readers are simply a keyboard interface and act as keyboard and automatically pressing Enter or Tab. So any field that can be use with a bar code reader. I hope this helps and thanks so much.
Very helpful. I run a small fashion business. And I would like to take stocks with my barcode scanner. How can I include details of each product into the excel system through the barcode scan without manual input
Hi and thanks very much, on the Work Order, Purchase Order or Sales Order items can be entered using a bar code scanner. Thank you for your Likes, Shares & Comments. It really helps.
Hello there. I am following this training but at the part where we should link formulas from name manager I cant continue. My name manager is empty while yours is full. Is there a toutorial how to fill it properly?
Hi and thanks please feel free to download my template for free so you can see all of the named ranges. You can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Thanks you so much. It’s very helpful tutorial for me . I watch complete video and done every step. Plz tell me how to change currency in whole application . Thanks again❤❤❤
HI and thanks. This is not set up for multiple users however you can use my add-in here if you want to set it up for multiple users: ua-cam.com/video/G9VqF7yN0Oo/v-deo.html Or you can use the features in this training: To share and sync your macro-enabled Excel workbook, please watch this training: ua-cam.com/video/f_1HsR9305I/v-deo.html I hope this helps and thanks so much.
hello sir this is Abdul Shakoor Hashimi from Kabul Afghanistan, this video is a lot of help to so much people like me, loved this video of yours. I have a small computer business so could you please make an inventory management system for that. it will be so much help from you sir
thanks. found it. but after copying the data to my own sheets, I found out the name manager isn't displaying like yours in the video. could you shed some light on what I should do right@@ExcelForFreelancers
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific issue you are running into with that named range. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Thank you so very much, I really appreciate that Trang and I am so happy to create them for you. Thank you for your Likes, Shares & Comments. It really helps.
We're a crystal store that creates products like jewelry and home decor. I am looking for a workbook in which I can break down the costs, do markup, and split profit with partners. Are you able to help?
HI , thanks for the information. There is no doubt we can help you with that. While I am unable to take on any projects I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com Thanks so much.
HI randy. Are all your application designed to run as 'standalone' ( desktoop), as many of the feature woudl appeear that do not work when we opent the excel file on the web?. How does someone use your application interactively ( collaborative mode) with my colleagues ?). Kindly advise. Many Thanks Regards, Martin ( south africa)
Hi and thanks very much for your question Martin. Yes, that is correct they only work on the desktop and not the web (macros do not run in the web) However I have come up with a great solution to allow you to share and sync your macro-enabled workbooks with anyone in the world here To share and sync your macro-enabled Excel workbook, please watch this training: ua-cam.com/video/f_1HsR9305I/v-deo.html The basic idea is that you use a shared folder (Dropbox is my favorite, however there are several) Each person gets their OWN workbook and only the data is synced between workbooks. I am sure you will love this amazing solution. I hope this helps and thanks so much.
Yes for sure it does, The Sales Order, Purchase Order and Work Order features contain print functions. Please make sure you watch the entire training as this is included within the training. I hope this helps and thanks so much.
Hi and thanks. What you are describing is a Bill Of Materials, (BOM) and I have a training on that here: ua-cam.com/video/dfMchM3sTm4/v-deo.html I hope this helps and thanks so much.
Hi Randy. Thank you for an awesome lecture. Is it possible to get the formulas for each as we go along. I'm stuck at the first formula 🙈🙈=and$D8**,Mod(Row(),2)=0) and can't continue 😫😩. Not sure if I can see the formulas so clearly on the screen. Thank you very much.
Hi and thanks so much. the formula is =and($D8"",Mod(Row(),2)=0). However, you can download this file for free using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Thanks. I assume all data is stored locally, right. Is it possible to make a version that stores data in the cloud so several users can work from several locations at once?
Hi and thanks very much. Yes, absolutely. To share and sync your macro-enabled Excel workbook with anyone in the world, please watch this training: ua-cam.com/video/f_1HsR9305I/v-deo.html I hope this helps and thanks so much.
Hi and thanks so much. I do have a Restaurant Manager here: ua-cam.com/video/p_ATIFfsRjU/v-deo.html I will also do a Produce Management System which will help with date-based Inventory very soon. I hope this helps and thanks so much.
Hi and thanks, this should never happen in Excel if it has been programmed correctly and the data is properly handled . I hope this helps and thanks so much.
Thank you for sharing such a great system with a detailed description.. I have two requests:- 1. I am wondering if we can calculate an average cost for the inventory (as purchases amount may change) for each item? 2. Can we add a calculation for the inventory report to calculate reorder stock (if we assume we need 2 months stock for each item - defined with specific quantities according to sales patterns) Thank you again .. your system helped me a lot and just keep me wondering what more support you will provide in your paid website?
Hi and thanks very much. When it comes to calculating the average cost, you can see this training here: ua-cam.com/video/0aOVX2F2Ffk/v-deo.html Also, to determine reordering, I have another video on this here: ua-cam.com/video/3BrscsEln7k/v-deo.html If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet) You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project. I hope this helps and thanks so much.
Thank you very much for your reply. I tried the system and I faced with 2 issues: 1. As per 1:12:38 there is an issue with the data validation also happens to me how to resolve it?? 2. How to protect the system so no one could change any things especially the IMSGroup, where the blue buttons to be deleted or moved around?? Thank you alot for your knowledge sharing.
Hi Abdel, For the protection options, you can see this video: ua-cam.com/video/NXNA2uyCyJo/v-deo.html As far as the data validation issue, It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Great! @@ExcelForFreelancers I just want to know if I build the application and I want to download it to my customer PC! Can I use such application downloader as downloading all codes like an add-in?
Hi and thanks for your question. Yes, you can have your excel application downloaded and include add-ins as well. I hope this helps and thanks so much.
Hi sir! Thank you for the great training video, really helpful! I just have a question. Which part of the code updates the qty of the products whenever an order (purchase/sale) is made? Thank you very much!
Hi and thanks very much. We have several Qty's if you mean the Qty On Hand, that would be with a formula based on the total purchases - total sales, however the Qty Purchased or Qty Sold is updated within the Save/Update macros. I hope this helps and thanks so much.
Hi Randy, everytime i add a new product the "in stock" value dosnt reflect in the products worksheet. it remains 0 in products worksheet which causes "value on hand" to remain blank until i manually change it in the products worksheet only then will it do the math for "value on hand". any idea how i can fix this
Hi and thanks very much. The 'in stock' is a calculated value based on the # of purchases minus the number sold. To set an initial value, you can create a purchase for this item which will set that value (If I were going to make an update for this application, I would add a table of 'Inventory Starting Quantities' and then take the values into consideration as well) I had to keep it simple for this training. You would not want to change the value manually in the database since this is a formula. I hope this helps and thanks so much.
Thank you i did download your file & it opens allowing me to view everything. butcan i change things like $ to other signs also replace datawith my own. I did try but it will not update or change anything.
Hi and thanks so much. yes you can change any part of the file. To Change the currently please go to Formats and update the currency to anything you like. I hope this helps and thanks so much.
I’ve done research and this is definitely one of the best if not the best inventory management spreadsheet. But Is there something wrong with the "Add new product" I'm getting frustrated here because I created my own spreadsheet from scratch & following all directions. But when I add the new product, the Product ID does not come up correctly. It repeats two same numbers. Even downloading the template I see the same problem. Please help. It is difficult for me to fix this as I’m not familiar with all the formulas and macros.
Hi and thanks. I had a look at this and I found a conflict due to the Inventory Database being treated as a table. If you convert this table to a range it will work fine. I have also updated the download with this fix as well. Thanks again
Thanks so much. Yes, and thanks. Excel is more powerful then we realize. Even I am surprised by Excel's power as I stretch its boundaries each week Thank you for your Likes, Shares and comments. It really helps.
I have a legacy access file from over 11 years ago that everyone uses... and it's slowly lost its functionality over time. Ive spent (to date), 50-60 hrs trying to learn how to fix it and playing with it trying to fix it and have been completely unsuccessful. But im also not versed in vba at all. So im now walking down this road because half the bottlenecks i deal with are vba related, or solved by vba
HI and thanks for your comment. It sounds like a nice project. I am unable to tak on any work, however, If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet) You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project. I hope this helps and thanks so much.
Hi.. I tried this.. into my excel. It is good at first, but when the data come to 50 above sales.. the calculation start slowing down the entry process. Currently the files become 157MB. Original files is 17MB. Any suggestion to reduce files size? Approx now is 2.8MB per Sales order.
Hi and thanks very much. That seems quite high. You can limit the named ranges and cell formats to just the used area. On the database sheets you can remove all formats as well. You can also remove unnecessary pictures on the dashboard. The size seems quite high as data itself should not be that much. Formatting can take up a lot of space so you may also want to limit the conditional formatting. I hope this helps and thanks so much.
I purchased the IMS, I am trying to add a new product with an inventory of 2 via the Inventory Management tab. It all seems good there. However, If I go to the products tab the inventory still shows 0...????
Hi and thanks very much. When you make a purchase of a product, the formula will consider this a new item and add it to the Qty. Once it is sold it will be deducted. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi and thanks I added 'Locations' onto my recent Sore Management training on Patreon here: www.patreon.com/ExcelForFreelancers I hope this helps and thanks so much.
Great, thanks so much. I will be doing a lot more from scratch. I will try to keep them not so long, but some will be longer since its from scratch. Thanks for your continued support.
For sure, you are very welcome and I am happy to help and share Tobaye. Lots of new tips and tricks in this training. Thank you for your Likes, Shares & Comments. It really helps.
How can I get the average cost of same product if I bought it from multiple vendors? Like from one vendor it cost me 5 from second vendor it cost me 6 from third it cost me 3. How can i get the accurate average cost for this product and how can manage it in this system
Hi and thanks so much. You can add the total purchased Amount (all vendors) divided by the total purchased Qty (all venders) to get the average price. I go over this specifically in this training: ua-cam.com/video/yFFVX0eMlEQ/v-deo.html I hope this helps and thanks so much.
HI and thanks. Yes. I use AutoHotkey for that. I have a video on there here: ua-cam.com/video/gkmiNkbvpkE/v-deo.html And another one on how to automate typing with an AutoHotkey library coming out in a week
📅Excel Calendar Pro: Your Solution to Stress-Free Scheduling 👉 rebrand.ly/CalendarPro_PinnedComm
HI where is the link for excel sheet ??
Is this report available for free download ?
Hi Randy, is there a Patron upgrade version of this Inventory Management system ?
I wish I can buy only one that suits my requirement. A Pharmacy Inventory for a public hospital.
You are a GENIUS ❤❤❤
hi
Thank you for providing such helpful content. I'm currently working on modifying the template, but I've run into some difficulties. Could you please recommend any specific videos or resources where I can learn more about making modifications? My main issue arises after the 36:18 mark, as the worksheet design includes multiple sheets and I need to delete certain columns in both the customers and suppliers sheets.
Hi and thanks very much. If you delete columns, this requires modifications to the code, formulas, formats and functions across many macros within the code, so there would not be a single video to show you how to do this. You may want to hide the columns. To learn more about VBA I have a beginners series here ua-cam.com/play/PLIBeRriXvKzDTzNp5IME4-oXXCrIjvV4z.html
If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet)
You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project.
I hope this helps and thanks so much.
@@ExcelForFreelancers thank you
This is so detailed, so complex, so... so... fascinating. Thank you. I love it.
Thank you so very much, I really appreciate that and so glad you enjoyed the training
hello sir,
When I was learning excel, I tried creating a system to take the input of students data and create a profitability chart. Your videos are very inspiring.
That's great to hear. Thanks so much for sharing and I am glad you enjoy this training
I just completed my first week at a new job in the warehouse and the weekly report in nuts. I spent all day cutting a pasting from one work book to another, then adding things up. It's nuts... I feel like I'm wasting so much time doing the same thing over and over. My goal is to somehow automate this report from what I've already put on excel. Wish me luck...
I am sure you are going to do great. If you need help at any time, or jus looking for a straight answer to a question, feel free to join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 60,000 Members who would love to help you with this. Thanks so much.
Best of luck Bro..... Can you please update with your story how did you manage all that
hi I am really happy about your work, Quick question, How can I reduce and add quantity to the product for every purchase and sales i have?
so If I purchase items, quantity should add to the product, and if I have sales quantity of the product should be deducted.
how can you do it?
Hi and thanks very much. I show you how to do that in my recent Userform Point Of Sale training here: ua-cam.com/video/9biCBLwEngM/v-deo.html
I hope this helps and thanks so much.
Hello Good day. Great tutorial. How the "In Stock" works? just tried your file. and how i know how many stock left?
Hi and thanks so much. Essentially the Total Amount Purchased for a given item - The Total Amount Sold for a given item is the Quantity On Hand.
I hope this helps and thanks so much.
Hi Randy! Thanks for the IMS template! How to get rid of the pub inside? Thx in advance!
Ok! I got it...
HI and thanks for your comment. I am glad you were able to get it. As an extra security measure, Microsoft disables macros from workbooks downloaded over the internet. You can fix this once and for all by following the steps I have outlined on this page www.excelforfreelancers.com/how-to-fix-macro-blocking-issue/ I hope this helps and thanks so much.
Hi Randy, thank you for the work here. on the Customers sheet, how did you get the asterisks into the criteria tables? and how do you create these criteria tables?
Hi and thanks for your comment. The Asterisk are the wildcard characters used in the advanced filters. So you would manually type them in along with the value of the searched cell. These are common in Advanced filters. I have a training on that here: ua-cam.com/video/YuCrEWU2Tm0/v-deo.html
I hope this helps and thanks so much.
This is my first time studying macros. This is so informative, and it helps me a lot.
I hope you can answer what I can do if I need more colums in purchases, sales and products. Upon adding colums on the format itself, what should I do in VBA?
Sorry. I'm new to this. (Freshly graduate, colleges don't teach this. 😢)
Again thank you so so so much!
HI and thanks for your comment and I am glad you enjoy the training. Any changes to columns or rows will require multiple changes to the VBA Code, functions, formulas and formats so its more than I can answer in a UA-cam comment. You may want to check out my Beginners VBA Series here ua-cam.com/play/PLIBeRriXvKzDTzNp5IME4-oXXCrIjvV4z.html
I hope this helps and thanks so much.
@ExcelForFreelancers Thank you for the link!! I will study those first. Thank you so much!! I hope you are blessed with the best things in life!! 🥰
For sure, you are very welcome and happy to help
I really enjoy the quality of your videos. This is a great system for a small starting out company. I do have a question. In some states there are two different sales taxes, one for groceries, and a second for all other purchases. Have you created a video covering how to apply different sales taxes based on the category of the item? Here in Tennessee we have two different sales tax rates.
HI and thanks very much. If you want to add additional sales tax, you can add one additional row and also one additional tax settings in the Admin screen. I hope this helps and thanks so much.
This video is amazing. Are you able to integrate a pick slip, packing slip/despatching option where once despatched this reflects it in the stock on hand.??
Hi and thanks. Yes sure, anything is possible. Please feel free to download the free template and customize however you like. If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet)
You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project.
I hope this helps and thanks so much.
I have enjoyed it following each step but got stuck when it comes to the rules part at 30:44 because there's an error apparently it says it must have a value... but i did enjoy trying to follow the steps. is there any add ons that needs to be added to excel?
Hi and thanks so much. I am glad you enjoyed the training. The formula would be something like this
=and($D8"",Mod(Row(),2)=0)
Feel free to download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Thanks for the tutorial man, that help me a lot. Quick question, so if got a single type product but the type is different what should i do?
Hi and thanks. Can you provide more details of the issue or challenge you are facing since I am not quite sure what the problem is.
I hope this helps and thanks so much.
@@ExcelForFreelancers for example like this, I have an item called a motorbike footrest and it has different brands. I want to make the product name in the sales order section management inventory, row 9 column D the product name and in the description I just have to select the brand. Thank you and sorry if my English is still a bit lacking✌️✌️
Update: i did fix the issue, but now im facing a problem with the stock because the product name bassicaly contain a duplicate lolz
Update 2: Finally i fix it. Thanks for the Tutorial Man it means a lot
Hi and thanks very much. You will want to create one single product (not multiple) then inside that product you can have a drop down list and select the brand.
I hope this helps and thanks so much.
Thanks Randy for that new video, it's awesome what can be done with Excel ! Just a small question, does it work with older Excel versions (2019, 2016,...) ?
Hi and thanks so much. Yes for sure, this will work with any version of excel on, or after 2007. I hope this helps and thanks so much.
@@ExcelForFreelancers thanks for your answer ! 👍
Hello, I haven't watched the entire thing yet but I just wanted to know ahead if this is possible to be done in a way that multiple people can interact with the inventory system and update in real time?
I'm new to excel and I'm not sure if it can do that besides just downloading the file and passing to to others.
I was also thinking if this is doable in Google Sheets instead?
Hi and thanks. Yes for sure its possible. You can use my Share & Sync addin here: ua-cam.com/video/G9VqF7yN0Oo/v-deo.html
I hope this helps and thanks so much.
Im in the process of working through this video now. Making some changes because the supplier menu doesn't matter to me, but being able to capture my recipes to get a total cost of ingredients does. But, i am curious if you think this DB can be translated to Google Sheets? Im not a Sheets person, but was wondering because of the anywhere availability of Sheets.
Hi and thanks very much. If you want to be able to use your data anywhere, you can follow my training on this. To share and sync your macro-enabled Excel workbook, please watch this training: ua-cam.com/video/G9VqF7yN0Oo/v-deo.html
I hope this helps and thanks so much.
Hi, thanks for this video! Which formula do you use for the Item Row in tab Order List?
Hi and thanks for the Selected Item Row I use the selection change event and I bring over the item ID
The row is generated from a formula such as
=Iferrror(Match($B$2,Order_ID,0)+3,"")
Please update the ranges and check the cells for hte actual formula. You can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
hello, thank you so much for this useful video! i wanted to ask, when writing the formula for the Total Sales in Sales by Month, shouldn’t the ending part in EOMONTH function be: ROW()-2,1),0)) instead of -1, since by then putting 0 we are staying in the current month? ive been trying to figure that out and would greatly appreciate your help to understand😁
Hi and thanks so much. If we want to find the end the month, then yes, it should be 0, a negative number would be months before and a positive # would be months after. I do not remember this exact instance however I will check on it. I hope this helps and thanks so much.
@@ExcelForFreelancers thank you so much for the swift answer. so if with 0 in the EOMONTH function we are staying in current month (january), i think it should be ROW()-2 instead of ROW()-1at 2:09 since ROW()-1 gives us february instead of january. i also checked with the order total in order list for those months and the given sum seems incorrect. or am i getting this completely wrong haha
Hi and thanks in that case you want to change Row()-1 to Row()-2 so that it reduces an additional month. I hope this helps and thanks so much.
Hello Randy, Thank you for the Inventory Management System. I have a question, do you know where is a problem when I add new Supplier and save it for sure, I can see it was added in the suppliers sheet but when I want to make an Purchase order, it will not add all infos for new supplier only the name but not the street etc. It is working for first 13 suppliers but it is not working for new I added. I dont have this problem with Customers btw. Can You help please?
Hi Barb, thanks for your comment, and I looked into this issue and I believe I found the issue.
If you go into the VBA code into the InvMgr (Inventory Manager) sheet, you can find a row that says
Range("E7").Value = Suppliers.Range("D" & NameRow).Value & ", " &Customers.Range("E" & NameRow).Value & ", " & Customers.Range("F" & NameRow).Value
It should be changed to
Range("E7").Value = Suppliers.Range("D" & NameRow).Value & ", " & Suppliers.Range("E" & NameRow).Value & ", " & Suppliers.Range("F" & NameRow).Value
I will also update the uploaded version as well. Basically it was looking in the Customer sheet for the address and details instead of the supplier sheet.
I hope this helps and thanks so much.
thank you so much, I´am learning many things here...
Can you let available every items you used on this video?
such as background, icones and so on..
Hi and thanks so much. I made all resources, (icons, pictures, and all other items) available every week on our Patreon platform here: Https://www.patreon.com/ExcelForFreelancers
I hope this helps and thanks so much.
Good Day Randy, I am pretty challenged with the Replace function on the shapes. Have you extensively discussed this in another video? If not, please help me out because I keep getting a mismatch error 13. I have tried to comment out sections of the code but i get a missing object error. I need your help. thanks
Hi and thanks. This is an area I know extremely well since so many of my videos i have worked a ton with shapes. If you can share the part of your code you are having trouble with I can try to help here in the UA-cam comments.
If you want to share screenshots then please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 60,000 Members who would love to help you with this. Thanks so much.
@@ExcelForFreelancers
Sub Menu_Select()
Dim MenuNumb As Long, MenuItem As Long
With InvMgr
MenuNumb = Replace(Application.Caller, Left(Application.Caller, 8), "")
.Range("B2").Value = .Shapes("MenuItem" & MenuNumb).TextFrame2.TextRange.Text
With .Shapes("IMSGroup")
For MenuItem = 1 To 6
.GroupItems("MenuItem" & MenuItem).Fill.ForeColor.RGB = RGB(75, 52, 206)
Next MenuItem
.GroupItems("MenuItem" & MenuNumb).Fill.ForeColor.RGB = RGB(41, 28, 118)
End With
End With
End Sub
HI and thanks for the code. The first thing to remember is that when you run this code, it must always be from a shape. If you try to run this using F5 or the play button it will always return an error because there is no shape that called it"
When you have a but, make sure that he "MenuNumb" variable is a whole number
Make sure you have a sheet code named "InvMgr"
Make sure you have a group shape named "IMSGroup"
Also make sure you have a shape in your sheet that contains "MenuItem" and the MenuNumb
when you have a bug, click debug and hover your mouse over the variables to check them all
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
HI and thanks for the additional code
You will want to make sure that when you run this code it is from a shape. Not using the Play button or F5 inside VBA editor. Because it uses Application.Caller, this is the name of the shape that called if. If you try to use the F5 or the play button, there is no shape that called the macro
Make sure you have a sheet code name InvMgr
Also insure that you have a Group name InvMgr and inside this group you have the right shape names
During the debug, hover your mouse over the variables to check their values. MenuNumb must be a whole number
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
We created a research tool to input recall data and pull a recalled item from the database. This video is brilliant.
Thank you so very much, I really appreciate that :)
THank you for this! One question though, I didn't get the part on how the rows column were removed. Could you let me know how it was done? also is your class offered even outside your country?
Hi and thanks, the columns are hidden using the .Hidden = True property.
Feel free to download the template and inspect the code using the links in the description.
I hope this helps and thanks so much.
Its sounds great! I'm really excited. Pls make this training as much more details as possible. Thks you!
Hi and thanks so much. Yes, in this training I will be designing the worksheet from scratch, during the training, so you can follow along. I hope this helps and thanks so much.
Awesome app but quick question. Wanting to use this for work, but how would I go about adding an order from a seperate excel workbook?
Hi and thanks you can link the workbook in the admin screen with the file path, however i do recommend having all of the data in the same workbook for speed and ease of use.
I hope this helps and thanks so much.
Thank you very much for this Inventory Management System. I really love the free template as it would be a great way for me to track my sales, customers, purchases etc. But I'm not really a techy person and I tried editing the free template but I keep running into bugs. Please how can you help me? Thank you. I run a small business and it would really help with my limited resources.
Hi and thank you for your comment. If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet)
You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project.
I hope this helps and thanks so much.
You just got a subscriber. Thanks for sharing! 🙏
That is great to hear. Thanks so much. I really appreciate that :)
Hey Randy AUSTIN, thanks so much for your mind-blowing tutorials.
You don't know how life-changers your courses are to me !!!!
Thank you so very much, I really appreciate that Keita, I am really happy to help and share
Waiting….
For more.
I can’t find a word to thank you ❤❤❤❤🎉🎉🎉🎉
Great, thanks so much. I am so happy you enjoyed this training. Thank you for your Likes, Shares & Comments. It really helps.
Do you have any templates or demos on any inventory management/POS excel based systems? Looking to track bulk inventory moving in and out from a warehouse. Would like to have it automatically update the system when removing or adding to the overall inventory. An added plus would be to have barcode scanner capability. Thanks!
HI and thanks so much. I have an entire playlist of POS Systems here: ua-cam.com/play/PLIBeRriXvKzBohiJA68HSrtetepIR4Nna.html
I hope this helps and thanks so much.
Question, you've got it setup to show 108 rows, what I want to know is there a way to have it show I say groups of 30 and then you have to hit a button to go to the next 30?
Hi and thanks so much. Yes it is certainly possible to add pagination to a range. I will try to add this in an upcoming training. Thank you for your Likes, Shares & Comments. It really helps.
Let me try this I have found it when searching for an excel sheet that can help me manage my small business. It's really a small shop so I want to try and am sure it will help me even with my accounting and audit
Thank you so very much, I really appreciate that and I am happy to help and share
fascinating to see people who know how this works
Great and thanks so much. I am so glad you enjoyed the training
Hi Randy I love your videos and wanted to go to your website but to no avail i could not is there something wrong please
Hi and thanks there could have been a temporary issue we were working on however it should be fine. Can you clear your cache and try again or another browser?
www.ExcelForFreelancers.com
I hope this helps and thanks so much.
awesome for excel programmers (or whatever you call them). This is really free training. Grateful for your content. but for people like me, I'd rather buy the finish product :D
Hi and thanks so much. I am glad to help. If you want I can have this customized and secured for you. Please feel free to email your requirements Randy@ExcelForFreelancers.com
I hope this helps and thanks so much.
Hi, I have a question. Will this system works on excel 2013 and below? Thank you.
Hi and thanks, yes this will work on any version including 2007 and after. I hope this helps and thanks so much.
I am trying to help this district hospital create a manageable pharmacy inventory. Love all your tutorials. Trying to work around to create one. ❤❤❤love all your work. Cherrs.
HI and thanks so much. Please check out my Pharmacy POS here: ua-cam.com/video/lpJ6_ynPuU4/v-deo.html
I hope this helps and thanks so much.
Hi Randy, please help with the way you came up with the criteria formula you deployed in the advanced filters. eg ="*"&'Inventory Management'!R4&"*". I have done some reading research but don't seem have it documented anywhere not even textbooks. I have a deaf friend who really is an enthusiast of excel, how I wish you could find him a transcription. He can read but impaired with hearing.
Hi and thanks so much. I have Closed Caption, in 13 languages, including English, on every video. I also have specific videos on Advanced Filters. I hope this helps and thanks so much.
Hello, thank you for this priceless piece of work.
I am currently at 37:30
Please how did you get the Order_ID that match?
Though I didnt see you show how you listed those functions in the name manager, i tried it but mine didnt pop up when i typed order_id
Please can you show how you created the names in the name manager?
Thanks
Hi and thanks, yes i created the order id with a Named range such as
=OFFSET(Orders!$A$3,1,,COUNTA(Orders!$A$3:$A$997)-1,1)
I cover named ranges in this video: ua-cam.com/video/yMz8S5ypBmg/v-deo.html
I hope this helps and thanks so much.
I have watched all the ads in your video to repay your generosity to share this video!
Thank you so very much, I really appreciate that. Very kind of you :)
Could you please demonstrate, what will happen with formating of chart when month and year will change?
Hi and thanks very much. I will be creating a new training with this feature in the near future. Thank you for your Likes, Shares & Comments. It really helps.
can you please suggest your videos for all the data that you have put, I'm having problem after 35:00 in understanding other sheets workflow.
Hi and thanks for your comment. I am not sure what you mean by 'all the data that you have put'? The data I use in these templates is just sample data. You can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Thank you for your great tutorials. It is really helpful for us. Much appreciated.
For sure, you are very welcome and I am happy to help and share
Thank you Randy for sharing this whole new level of awesomeness with us. God Bless you.
Thank you so very much for your continued support Syed :)
کیا اسے ڈاؤن لوڈ کیا جا سکتا ھے؟
جی بالکل کیا جاسکتا ہے۔ آپ کیا کرنا چاہتے ہیں ڈاؤن لوڈ کر کے؟@@saeedahmed1261
Hi Randy, your teachings are great however, they are more advanced. I think maybe the missing part of these trainings is only two things otherwise we could have the detailed training. I don't mind if these trainings can take 2 or three hours as long as it is something that someone can follow without asking so many questions and be able to develop something from your programs
Here are my Suggestions:
1. Losing the link on the name managers and applying it to the formulars. This is missing more detailed on how it is done and where it is extracting from. This part is very important!
2. Prepopulated the VBA codes. It would be good to start all your codes on the same template you have created from the beginning. We get lost on the middle of the way and we ended up not completing these trainings because you explain code and not making it with us. Unless you have any other class where we can learn this that will be good since we are new babies on these codes. However, the finish products of your work are brilliant always.
HI and thanks so much for your comment and feedback. I do appreciate that.
When it comes to basic VBA, each Saturday I create a Basic VBA Training and I have 36 VBA Basic trainings here ua-cam.com/play/PLIBeRriXvKzDTzNp5IME4-oXXCrIjvV4z.html
Concerning writing code and design. On any basic template, I can create it from scratch during the video. I have an entire series of 'from scratch' training you may like here:
ua-cam.com/play/PLIBeRriXvKzCHVxFYRSMTZu3PmnVn4ujb.html
Please also keep in mind that the template I use for training is available for free using the link in the description.
You may also want to check out our School Management series in which I show you how you can create your own Excel applications, from scratch, in an amazing series right here: bit.ly/SchoolManagerSeries
Hi Randy I need help, I do have a Excel Stock Control Sheet of my Pesticides, Fertilizers and Seed but know I want help on the following: On each block on the lands we uses pesticides, fertilizers, and seed, what I want to do is if I fill in the information onto my blocks how can it pull through to my stock control sheet automatically. Hope you understand
Hi Sonja,
Thank you for your comment. If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet)
You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project.
I hope this helps and thanks so much.
Hey Randy thanks for all of this so glad i found your channel right whn i needed it. So having to ho through a crash course of excel as we lost our guru. These have been awsome to learn new stuff by building. But i have an issue i have not been able to figure out how you did it. Aroun an hour and eight mins into the video you go change all of your tables and update them to not include the column of row to be displayed.
There has to be an easy way to do that without messing all of the tables and formats up.
I can see removing column adding column reshaping box format can be done for each. I just wondered what approach you did so if i can fix mine i am building step by step. And i can fix in the future if i encounter it again.
Thanks
Hi and thanks very much. I am glad you liked the video. Anytime I bring over data from an original table, if i have to edit that data, I generally bring over the database row. I will put that in an adjacent column. To hide this, there are 3 methods. (Hiding the column, matching the font color with the background color, or in this case I used a custom format with either two semicolons ;; if its only numbers, or 3 semicolons ;;; if it is a mix of numbers and letters. This is a custom cell format.
Please feel free to download the sample workbook.
I hope this helps and thanks so much.
Your skills have the potential to enhance my work significantly.🙂🙏
That is great to hear and I am very happy to help and share
Hi Randy! I really appreciate this video!
Unfortunately I'm stuck at the minute 30:44 I literally rewrote the formula =and($D8"",Mod(Row(),2)=0), but it keeps telling me there's a problem. I'm really frustrated and I'm only at the beginning of the video :(. Please I need help because I don't know what I'm doing wrong
HI and thanks for your comment. The formula should be
=and($D8"",Mod(Row(),2)=0)
However with some language pages it will use a semicolon instead of a comma.
I hope this helps and thanks so much.
Just love your work. I've downloaded the sheet and started customizing it for our Not For Profit club. Learning a lot as I go along. I'm not sure if it was pointed out previously, but in the version I downloaded the Suppliers kept showing the Customers address. I looked back over the video at 1:42:41 and you have left the E6 and E7 values as Customers.Range instead of Suppliers.Range. Sorry if it has already been picked up. Cheers, Barry from Tassie Australia
Hi Barry, thanks so much. I did see that and fixed the issue, and updated it for downloads. I also made other updates and added features on our Patreon platform update here: www.patreon.com/ExcelForFreelancers
Thanks so much for your continued support
Hi @barrtmcki8489. Were you able to modify it for a non profit? can you share amended excel as I need it for a non profit foundation I'm volunteering for? it will be of great help.
Hi can this be used by multiple users and multiple machines at the same time?
HI and thanks. yes for sure. To share and sync your macro-enabled Excel workbook, please watch this training: ua-cam.com/video/f_1HsR9305I/v-deo.html
I hope this helps and thanks so much.
Good Job Sir. I been thru th worksheet but i was challenged trynna enter data products and purchase but the qty does not show up in the "stock in" section on PRODUCTS sheet. Would you help me out Sir?
Hi, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
It is very hard to do jare, some formulas are not calling and it's frustrating, can you please tell us the worksheet you downloaded to get this done please sir.
Hi Darasimi, you can download this file for free using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
This would be great for my construction company I used to own !
Thank you. Which versions of Excel are supported by this tutorial?
Hi and thanks so much. You can use any version of Excel from 2007 to present, so its no problem at all.
Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers Thank you.
Great Job Randy. How can I implement a barcode scan function to their whole process.
HI and thanks so much. USB Bar Code Readers are simply a keyboard interface and act as keyboard and automatically pressing Enter or Tab. So any field that can be use with a bar code reader. I hope this helps and thanks so much.
Very helpful. I run a small fashion business. And I would like to take stocks with my barcode scanner. How can I include details of each product into the excel system through the barcode scan without manual input
Hi and thanks very much, on the Work Order, Purchase Order or Sales Order items can be entered using a bar code scanner. Thank you for your Likes, Shares & Comments. It really helps.
Hello there. I am following this training but at the part where we should link formulas from name manager I cant continue. My name manager is empty while yours is full. Is there a toutorial how to fill it properly?
Hi and thanks please feel free to download my template for free so you can see all of the named ranges. You can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Thanks you so much. It’s very helpful tutorial for me . I watch complete video and done every step. Plz tell me how to change currency in whole application . Thanks again❤❤❤
Hi and thanks so much. You can format any cell using the "Number" option on the Home menu to any currency you like
so currency cant be changed for the entire workbook at once? @@ExcelForFreelancers
Is this template working when multiple users update in the same time?
HI and thanks. This is not set up for multiple users however you can use my add-in here if you want to set it up for multiple users: ua-cam.com/video/G9VqF7yN0Oo/v-deo.html
Or you can use the features in this training: To share and sync your macro-enabled Excel workbook, please watch this training: ua-cam.com/video/f_1HsR9305I/v-deo.html
I hope this helps and thanks so much.
hello sir this is Abdul Shakoor Hashimi from Kabul Afghanistan, this video is a lot of help to so much people like me, loved this video of yours.
I have a small computer business so could you please make an inventory management system for that.
it will be so much help from you sir
HI and thanks very much. I do have that already here: ua-cam.com/video/dfMchM3sTm4/v-deo.html
I hope this helps and thanks so much.
helping with the datasets used for this exercise will be useful. thanks in anticipation admin
Hi, you can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
thanks. found it. but after copying the data to my own sheets, I found out the name manager isn't displaying like yours in the video. could you shed some light on what I should do right@@ExcelForFreelancers
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific issue you are running into with that named range. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Many thanks for your hard work ! These are actually invaluable assets to have in Excel 💯
Thank you so very much, I really appreciate that Trang and I am so happy to create them for you. Thank you for your Likes, Shares & Comments. It really helps.
We're a crystal store that creates products like jewelry and home decor. I am looking for a workbook in which I can break down the costs, do markup, and split profit with partners. Are you able to help?
HI , thanks for the information. There is no doubt we can help you with that. While I am unable to take on any projects I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com
Thanks so much.
HI randy. Are all your application designed to run as 'standalone' ( desktoop), as many of the feature woudl appeear that do not work when we opent the excel file on the web?. How does someone use your application interactively ( collaborative mode) with my colleagues ?). Kindly advise. Many Thanks Regards, Martin ( south africa)
Hi and thanks very much for your question Martin. Yes, that is correct they only work on the desktop and not the web (macros do not run in the web) However I have come up with a great solution to allow you to share and sync your macro-enabled workbooks with anyone in the world here
To share and sync your macro-enabled Excel workbook, please watch this training: ua-cam.com/video/f_1HsR9305I/v-deo.html
The basic idea is that you use a shared folder (Dropbox is my favorite, however there are several) Each person gets their OWN workbook and only the data is synced between workbooks.
I am sure you will love this amazing solution.
I hope this helps and thanks so much.
@@ExcelForFreelancers I will definitively try it. Thanks very much randy
This is one of those powerful tools, sharing and syncing. I have done so much with it.
Absolutely @slminc. One of my favorites
Does this template has capability to run standalone without excel sir ?? And will it be possible to upload to another pc??
HI and thanks so much. A desktop version of Excel is required for every one of my templates to run. I hope this helps and thanks so much.
Also, does the application has a print function for sales receipt generation? Thanks in advance
Yes for sure it does, The Sales Order, Purchase Order and Work Order features contain print functions. Please make sure you watch the entire training as this is included within the training. I hope this helps and thanks so much.
Can you provide a source for where you got those stock images if possible?
Hi and thanks I create the images with Midjourney: www.midjourney.com
I hope this helps and thanks so much.
@@ExcelForFreelancers thanks it does
Thanks
Hi! amazing tutorial, where can i download it from?
Hi and thanks very much, you can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Hello Randy i am trying to make an inventory system but my inventory is very complex we use parts to make another part this can be done with this?
Hi and thanks. What you are describing is a Bill Of Materials, (BOM) and I have a training on that here: ua-cam.com/video/dfMchM3sTm4/v-deo.html
I hope this helps and thanks so much.
Hi Randy. Thank you for an awesome lecture. Is it possible to get the formulas for each as we go along. I'm stuck at the first formula 🙈🙈=and$D8**,Mod(Row(),2)=0) and can't continue 😫😩. Not sure if I can see the formulas so clearly on the screen. Thank you very much.
Hi and thanks so much. the formula is =and($D8"",Mod(Row(),2)=0). However, you can download this file for free using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
@@ExcelForFreelancers Thank you very much. I want to try to create it myself. I want to learn more.
Yes of course, I understand. Its a great way to learn and thanks so much
Thanks. I assume all data is stored locally, right. Is it possible to make a version that stores data in the cloud so several users can work from several locations at once?
Hi and thanks very much. Yes, absolutely. To share and sync your macro-enabled Excel workbook with anyone in the world, please watch this training: ua-cam.com/video/f_1HsR9305I/v-deo.html
I hope this helps and thanks so much.
I love the question
great tutorial!! i wonder if you gonna do one for food costing for restaurants
Hi and thanks so much. I do have a Restaurant Manager here: ua-cam.com/video/p_ATIFfsRjU/v-deo.html
I will also do a Produce Management System which will help with date-based Inventory very soon.
I hope this helps and thanks so much.
Heeey boss
How do we display the sales and purchases of other months on the dashboard?
Hi and thanks. I added this feature for the Patreon members here: www.patreon.com/ExcelForFreelancers
I hope this helps and thanks so much.
Hello love that video! Do you have something similar for bar inventory? Thank you so much!
Hi and thanks so much. I do have a bar code inventory system here ua-cam.com/video/4hZUTWnMWaE/v-deo.html
I hope this helps and thanks so much.
Is it wise to do this in excel? Because after some time, it will become very slow and crash often
Hi and thanks, this should never happen in Excel if it has been programmed correctly and the data is properly handled .
I hope this helps and thanks so much.
Thank you for sharing such a great system with a detailed description..
I have two requests:-
1. I am wondering if we can calculate an average cost for the inventory (as purchases amount may change) for each item?
2. Can we add a calculation for the inventory report to calculate reorder stock (if we assume we need 2 months stock for each item - defined with specific quantities according to sales patterns)
Thank you again .. your system helped me a lot and just keep me wondering what more support you will provide in your paid website?
Hi and thanks very much. When it comes to calculating the average cost, you can see this training here: ua-cam.com/video/0aOVX2F2Ffk/v-deo.html
Also, to determine reordering, I have another video on this here: ua-cam.com/video/3BrscsEln7k/v-deo.html
If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet)
You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project.
I hope this helps and thanks so much.
Thank you very much for your reply.
I tried the system and I faced with 2 issues:
1. As per 1:12:38 there is an issue with the data validation also happens to me how to resolve it??
2. How to protect the system so no one could change any things especially the IMSGroup, where the blue buttons to be deleted or moved around??
Thank you alot for your knowledge sharing.
Hi Abdel, For the protection options, you can see this video: ua-cam.com/video/NXNA2uyCyJo/v-deo.html
As far as the data validation issue, It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Great! @@ExcelForFreelancers
I just want to know if I build the application and I want to download it to my customer PC! Can I use such application downloader as downloading all codes like an add-in?
Hi and thanks for your question. Yes, you can have your excel application downloaded and include add-ins as well. I hope this helps and thanks so much.
Hi sir! Thank you for the great training video, really helpful! I just have a question. Which part of the code updates the qty of the products whenever an order (purchase/sale) is made? Thank you very much!
Hi and thanks very much. We have several Qty's if you mean the Qty On Hand, that would be with a formula based on the total purchases - total sales, however the Qty Purchased or Qty Sold is updated within the Save/Update macros.
I hope this helps and thanks so much.
How can I go back to previous months and see the monthly sales and monthly profit. Like in the dashboard
Hi and thanks within the dashboard you can set any range you want with custom dates. I hope this helps and thanks so much.
Hi Randy, everytime i add a new product the "in stock" value dosnt reflect in the products worksheet. it remains 0 in products worksheet which causes "value on hand" to remain blank until i manually change it in the products worksheet only then will it do the math for "value on hand". any idea how i can fix this
Hi and thanks very much. The 'in stock' is a calculated value based on the # of purchases minus the number sold. To set an initial value, you can create a purchase for this item which will set that value (If I were going to make an update for this application, I would add a table of 'Inventory Starting Quantities' and then take the values into consideration as well) I had to keep it simple for this training. You would not want to change the value manually in the database since this is a formula. I hope this helps and thanks so much.
Thank you i did download your file & it opens allowing me to view everything. butcan i change things like $ to other signs also replace datawith my own. I did try but it will not update or change anything.
Hi and thanks so much. yes you can change any part of the file. To Change the currently please go to Formats and update the currency to anything you like. I hope this helps and thanks so much.
I’ve done research and this is definitely one of the best if not the best inventory management spreadsheet. But Is there something wrong with the "Add new product" I'm getting frustrated here because I created my own spreadsheet from scratch & following all directions. But when I add the new product, the Product ID does not come up correctly. It repeats two same numbers. Even downloading the template I see the same problem. Please help. It is difficult for me to fix this as I’m not familiar with all the formulas and macros.
Hi and thanks. I had a look at this and I found a conflict due to the Inventory Database being treated as a table. If you convert this table to a range it will work fine. I have also updated the download with this fix as well. Thanks again
@@ExcelForFreelancersGreat! Thank you
You are the Boss!
Thank you so very much, I really appreciate that
Wow, valuable assets. I didn't know that in excel you could do this big thing. Thanks.
Thanks so much. Yes, and thanks. Excel is more powerful then we realize. Even I am surprised by Excel's power as I stretch its boundaries each week
Thank you for your Likes, Shares and comments. It really helps.
U got a sub. Excel and vba is really hard to find decent help with. Thanks
Thank you so very much, I really appreciate that and great to have you here
@@ExcelForFreelancers do you do access/vba? If i had a file and i sent it, could you modify it? Like, as a service?
I have a legacy access file from over 11 years ago that everyone uses... and it's slowly lost its functionality over time. Ive spent (to date), 50-60 hrs trying to learn how to fix it and playing with it trying to fix it and have been completely unsuccessful. But im also not versed in vba at all. So im now walking down this road because half the bottlenecks i deal with are vba related, or solved by vba
HI and thanks for your comment. It sounds like a nice project. I am unable to tak on any work, however, If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet)
You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project.
I hope this helps and thanks so much.
Interesting indeed, moving away from Access to Excel is quite a challenging appetite 😊
I missed the beginning of this training. I will have to rewatch that again
No problem and thanks so much. I really appreciate your support. You can watch this video as many times as you like.
Hi.. I tried this.. into my excel. It is good at first, but when the data come to 50 above sales.. the calculation start slowing down the entry process. Currently the files become 157MB. Original files is 17MB. Any suggestion to reduce files size? Approx now is 2.8MB per Sales order.
Hi and thanks very much. That seems quite high. You can limit the named ranges and cell formats to just the used area. On the database sheets you can remove all formats as well. You can also remove unnecessary pictures on the dashboard. The size seems quite high as data itself should not be that much.
Formatting can take up a lot of space so you may also want to limit the conditional formatting.
I hope this helps and thanks so much.
real so help full to me thanks lot for you nice and practical training for inventory managements
For sure, you are very welcome and happy to help and share
I purchased the IMS, I am trying to add a new product with an inventory of 2 via the Inventory Management tab. It all seems good there. However, If I go to the products tab the inventory still shows 0...????
Hi and thanks very much. When you make a purchase of a product, the formula will consider this a new item and add it to the Qty. Once it is sold it will be deducted.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thanks teacher ❤
How ca we Get multi warehouse inventory management
Hi and thanks I added 'Locations' onto my recent Sore Management training on Patreon here: www.patreon.com/ExcelForFreelancers
I hope this helps and thanks so much.
just awesome sir and thank you
Thank you so very much, I really appreciate that Saddam
🥰
thanks for useful videos .
For sure, you are very welcome Osama. Try to join us for the Tuesday premier on this one. Thanks again
Now this is what we talking about, 3 days wait seems extremely long
Great, thanks so much. I will be doing a lot more from scratch. I will try to keep them not so long, but some will be longer since its from scratch. Thanks for your continued support.
Hi Randy thanks for sharing
For sure, you are very welcome and I am happy to help and share Tobaye. Lots of new tips and tricks in this training. Thank you for your Likes, Shares & Comments. It really helps.
Hello dear sir
I appreciate your work a lot .
How can I get this template?
Hi, you can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Thank you Sir. How to download template 🎉❤
Hi, you can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Hi, Randy. Hope you the best and thanks a lot for all the efforts you provide.
For sure, you are very welcome and so happy to share this with you. Thank you for your Likes, Shares & Comments. It really helps.
Thank you and love you brother from India😊😊😊
For sure, you are very welcome and I am happy to help and share
How can I get the average cost of same product if I bought it from multiple vendors? Like from one vendor it cost me 5 from second vendor it cost me 6 from third it cost me 3. How can i get the accurate average cost for this product and how can manage it in this system
Hi and thanks so much. You can add the total purchased Amount (all vendors) divided by the total purchased Qty (all venders) to get the average price. I go over this specifically in this training: ua-cam.com/video/yFFVX0eMlEQ/v-deo.html
I hope this helps and thanks so much.
The Bob Ross of Excel? 🙂 Awesome stuff
Hahah thanks very much. I have heard that before. I do appreciate it
30:37 how did you suddenly come to mod(row2), please explain if there is a shotcut
HI and thanks. Yes. I use AutoHotkey for that. I have a video on there here: ua-cam.com/video/gkmiNkbvpkE/v-deo.html
And another one on how to automate typing with an AutoHotkey library coming out in a week