Another fantastic lesson Randy! I have to keep telling myself that you have tons of experience and you have a pretty good idea what your going to show us before you start, because sometimes working through all the connections and dependencies makes me think my head will explode and you never lose track...lol. I may have a concern to work out as at one point i deleted the last customer, who had contacts, invoices, and work orders which then ended up belonging to the next customer I added new. I'm rethinking the whole 'Delete Customer' thing, but I will work it out. I'm not sue it makes sense to delete invoices etc., or to orphan them when the customer gets deleted. In any event, I am just doing this to learn at this point, and that is working 100%. You are excellent at designing, programming and presenting all this material for our consumption. I highly recommend you and definitely give you 5 stars. Thanks!
Deleting records can be a dangerous thing for applications, especially when there are invoices and payments. Generally when you have dependences you will not want to delete customers, Better to have an Active/Inactive option for large applications. I my Accounting Applications Customers and Vendors cannot be deleted if Invoices, Purchases or Payments have already been recorded.
Hi Randy, I just recently discovered your channel and this is the first tutorial I followed along from start to finish. Thank you so much for your teaching & sharing. I retired last year from my corporate gig and wanted to start my own property maintenance related business this year. I’ve been looking for some guidance on how to build a relational-type database in excel that would handle a contact list and invoicing, and you’ve just provided it to me. My next video to conquer will be Invoicing and then Work Orders. After that will be creating a schedule for the work to be done and an email confirmation to the customer. You have it all so far and more business related stuff that I never even thought of doing in Excel. Because of all the Covid-19 business going on right now, looks like I’ll have to delay my start-up until next year, when I can actually knock on some doors for drumming up some business. But I’ll have time to learn all your tips and tricks, and build a nice little application for next year. Regarding this video, I identified 3 gap items where 2 of them were addressed in today’s download. 1.) The missing Delete Contact code was corrected. Many others commented too. 2.) When an existing Customers Contact detail was updated, that detail wasn’t carried back to the hidden columns (O~R) in the CustomerMgr worksheet. But you added the “LoadContacts 'Reload Contacts” line of code into the Sheet1 Change section. Good job! 3.) If you delete Dexter Bauer from Asia Electric (as one example), the row numbers in Column G do not update accordingly except for last row, where Amy Smith was inserted with the = "=Row()" coding. You may want to run that formula all the way up to Row 4 in column G. 4.) This final item is not necessarily a gap item, but more of a clean-up item. Cust ID’s 1 and 2 were deleted from the Customers table, but not from the other 3 worksheets. Might be a good thing to cover if this is your first one-to-many tutorial. Thoughts? I’m curious about further enhancements, and maybe/probably you’ve covered them in other video’s. If you have, kindly direct me. 1.) The Invoice and Work Order tables are only 10 rows long. Is it possible to add a vertical scroll bar to each table for lists longer than 10 rows? Descending would be nice too. 2.) If you click the “Add Contact” button in error, there’s no elegant way to bail out. An “Abort” button beside the “Save Contact” button might be nice. Rather than reselecting the Customer to restore the data that was cleared out.
After thinking a little more about 4.), maybe the better approach would be to first check to see if there are any valid related records that should be kept. If there are, disallow deletion of the Customer. If there are no valid/active related records, proceed with the cascade delete activity.
Hi Les, thanks so much for your feedback and details. I used to run a maintenance company for 10 years so you will see a lot of similarities in the work. Most of the applications I develop for TH-cam are basic ideas and concepts that show the potential of Excel and are very far from complete, ready-to-use applications. Therefore if you would like to use these in real-world situations a lot more needs to be done with them. I try to fit the training within an hour or so each week, so there are always going to be features to add, and code to write to help debug issues. I hope this helps and thanks very much for the feedback.
Couple of notes... you always show us hot to get the Developer TAB... Just RIGHT Click up in the ribbon area, the second Option is CUSTOMIZE THE RIBBON. Also you do lots of Cell formatting, being an old DOS guy, keyboard shortcuts are my favorite. ALT {H,FN} Brings up the Format Cell Dialog box. Some people like the mouse, some like the keyboard. I know there are plenty of places to find them but just thought maybe someone might like it. Thanks
Thanks Erik, I am going to try to use more keyboard shortcuts, just as you suggested (especially the Format Cell Dialog as I use it 100 times when creating these) ALT {H,FN). This way I will get use to it and I can teach others. I am a 'shortcuts' guy so anytime i can save time, as you see with the automated code via Autohotkey, so this will be helpful. Much appreciated.
4 ปีที่แล้ว +2
OMG. Such a perfect video again. Thanks for teaching us Sir. You are the best mentor....
Thanks so much Ken, I am glad you enjoy the training and the downloads. If you would like to support us, you can get 100 of my applications for just $37.00 right here> www.excelforfreelancers.com/product/100-of-my-best-excel-workbooks Thanks so much.
Dear Randy. Again an excellent professional training video. I really appreciate the effort you put in every time. Stay healthy and keep up the excellent training work.:)
Honestly, you are the King of excel. Thanks a lot, One question if I may. The delete Contact button is nor working, if you could just tell me how to activate it I will really appreciate it. Thanks again! You Rock!
Hi Jermel, thanks so much for your comment. You can right click the button and assign it to the macro to delete contacts. You can also download the updated version using the links in the description which should have this button working. I hope this helps and thanks so much.
Hi Randy, Thank you so much i'v been looking for someone like you for long time..but i had some problems creating my customer manager sheet, the first is the MOD FORMULA and the second is OFFSET FORMULA. thank you again for all your effort and your members behind the screen.
Hey Randy, I thoroughly enjoyed building this from scratch while watching you teach it. I have been wanting to build something like this & now that I have the foundation that I have been looking for, I am on my way to a good & solid start on my application & getting paid for it too lol. 1 question: Where do you recommend good icons that i cane use freely or spend small amount of money on? 2 There was not Cancel button when adding a new contact. So I ventured in creating the button & building the vba for it. As I look at the coding I have to laugh cuz it is somewhat like how many you would write it, but clunky too.
Hi Christine, thanks so much for your feedback and I am really glad you love the training. There is really so much more that can be done with this. To answer your questions: 1. I get a lot of my icons from here: icons8.com/iconizer/ (it is a great site with tons of customizations and content) 2. A cancel button is a great idea with a lot more you can do with it. You can also just select the first contact in the list to Cancel, and if there is no contacts in the list, leave it in the Add New state. I hope this helps and thanks so much
you can also add icons in excel via "Insert" > "Illustrations" and look there for the button with a bird or go via this link to find the way support.microsoft.com/en-us/office/insert-icons-in-microsoft-office-e2459f17-3996-4795-996e-b9a13486fa79
i am downloading your workbooks and watching your teachings and i'm amzesd! didn't knew excel can work magically like this! aside from downloading the templates, i'm learning how to create those programs, and i have a question: does VBA changed their syntax? because when i got to the ".Range(Sheet2.Cells(1, CustCol).Value).Value = Sheet2.Cells(CustRow, CustCol).Value" line, there is a failure that requires debug (and i tried to copy the line too).. aside from that - everything is working greate (again, until it gets to that line)
Hi and thanks very much. During the debug, when the line is highlighted, hover your mouse over CustCol and alco CustRow to make sure both are whole numbers IF they are you would need to check Sheet2.Cells(1, CustCol).Value to make sure it is a Range such as E4 or F4, or whatever. So that means if CustCol is 3, you would check Sheet2, Cell C1 to make sure it contains a cell address. 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
@@ExcelForFreelancers thank you for your answer! it does helpful! took me some time to realize my mistake but with your help - i did! thank you so so much!
Thank you so very much, I really appreciate that. I have a video coming this Tuesday which will show you how to do a One Click Update for this application. You won't want to miss it.
Thank you so very much, I really appreciate that. 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, the buttons are just basic shapes, however, you can check out any of our 'From Scratch' videos in this playlist th-cam.com/play/PLIBeRriXvKzCHVxFYRSMTZu3PmnVn4ujb.html Thank you for your Likes, Shares & Comments. It really helps.
Well ... reading just a few comments and ..... I guess nobody knows what the word "FREELANCER" means. I like these videos because .... 1. I get an idea of what things a business might buy ( great ideas by the way ) 2. I see how he implements stuff ( not always in agreement with his coding or style but always open to new ideas. 3. Eventually or maybe there might be a download to use. But why watch these videos if you're not going to learn how to do it. If you need it for .... School - quit cheating Work ( non IT ) - hope you aren't getting paid to make it as he's done all the work Work ( IT ) - omg .... pay the man for the software Dude since I seen the title for this vid awhile back ... been drooling to hear how you've developed your client base. Will get this watched tonight after din din.
Hi John, thanks very much for the comment. I hope you enjoy the training once you see it (set aside around 90 minutes as each of my trainings are at least an hour) Certainly a lot of people are just here for the templates to download, but so many actually enjoy watching and learning how the process is made so they can learn it themselves. Also it takes some of us time to realize there are 'no shortcuts in life' I am sure when I was younger I thought there was. Thanks so much for the feedback and great comment.
Dear Randy I really learn from your training video and i appreciate the great work that you do but would you mind to tell me which vergen of excel you use ?? I v been searching for a week to no how you wright the macro with auto complete option ....you wright (with sheet )and then it auto complete (end with ) and so on ......many thanks
HI and thanks so much. I am using Excel 2010 however I automate my typing using AutoHotkey. I show you how to do that in this video: th-cam.com/video/gkmiNkbvpkE/w-d-xo.html I hope this helps and thanks so much.
Hi, thanks for all the tutorial and videos. Superb job. Regarding deleting Customer - The contacts related to the customer are not deleted. This can be unfortunate, is for some reason a new customer gets assign an old ID, where contacts are. So can someone provide a code, that deletes all the contacts related to a customer, that is deleted?
Hi and thanks so much for your comment. I have something similar in which we delete an Invoice and then all of the items, associated with that invoice, are deleted. The code is very similar. Here is the training th-cam.com/video/YC-SZlIwj5c/w-d-xo.html I hope this helps and thanks so much.
Hi and thanks. Within the save/Update macro the value of B8 (next customer ID) is transferred to B2, however it is an existing customer it will come directly from the database on customer load. I hope this helps and thanks so much.
Hi Randy, another great tuition saving us time and money 💰. Would it be possible to create an complete sales application in the future? A sales application combining customer manager, inventory and invoices. Where a sales agent could place a customer order depending on the inventory, and print invoices, and customer statements from one application. Many thanms for all your applications and videos, they help a lot.
Hi Robby, thanks so much. I am really glad you enjoyed the training. I am actually doing all of that and a whole lot more in the Accounting application that I am creating in the Mentorship program which includes (Customers, Vendors, Items, Inventory, Invoicing, Purchase Orders, Credit Memos, Multi-user sharing & Sync, Dashboard, Chart Of Accounts, General Ledger, Transactions and full dashboard and reports section. You can learn more about this program here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Hi Randy Thank you for this fantastic lesson. I'm trying to un this macro, but I can't because I receive the error "Run time error 438 object does not support this method" It's happening when I set the criteria.
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.
Hi and thanks. You can download this file using the links in the description above with either your email or Facebook Messenger. I hope this helps and thanks.
Hi Sondan, you can download this file using the links in the description above with either your email or Facebook Messenger. I hope this helps and thanks.
Thank you very very much for this fantastic application.. however i need from you a small favor that how to make the amendment/ change in the contact (list in right side) needs a (update) button next to (add) and (delete) i hope you understand me sir .... I appreciate your help on this sir
HI and thanks, you are welcome to hire a developer from our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 50,000 Members who would love to help you with this. Please mark the post as a PAID JOB. Thanks so much.
Hi Renata and thanks for your comment. You can use multiple criteria in an Advanced filter. I show you how to do this in this video: th-cam.com/video/YuCrEWU2Tm0/w-d-xo.html
Hello po, your video is kinda interesting and i want to create one but i just want to ask how did u create the customer's list? like putting add contacts button etc. from customers sheet to customer manager sheet. thank u!
Hi Randy, I am wondering if you could customize the Customer Manager Data Base to my need and how much would this cost. It is basically all you have in this workbook plus one more worksheet/ Table for " Cost of Materials for each order" Please let me know if you need further details.
HI Moe, Thank you for your message. Thank you for your message. I am really glad you enjoyed the training and I do understand your requirements. Since I am developing courses I am unable to take on any additional work, however I would be happy to refer you to a competent developer who could handle this for you. Just let me know if you would like to proceed and I can forward your details to them. Thank you very much. Randy@ExcelForFreelancers.com
Hi Randy, brilliant video as always however I was wondering if there was a way of having the notes field scrollable, so it can be more of a history of contacts and you can see when they have been added rather than just as a small box Thanks Mark
Hi Mark thanks so much you could add a form control or Active X scrollable box that allows for a larger amount of notes. Thank you for your Likes, Shares & Comments. It really helps.
hello Randy, thank you for this amazing tutorial, however can I know how should I put data in the Invoice and work order sheets, I am having a hard time, since Invoice and workorders contains only the customer ID, how do I enter data in the sheet since I do not know where to get the customer ID's or How will i know the customer ID's. Thanks in advance
Hi and thanks very much. I have a specific video on that to help you here: th-cam.com/video/99xVbVMABJ0/w-d-xo.html I hope this helps and thanks so much.
Hi and thanks so much. You can clear the existing Invoices & Work orders and then run an advanced filter based on the Customer Name or Customer ID and then bring in those results into the range. 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
Hello Randy Thanks indeed for such a good training, I would like to know how can I link the phone numbers in this Customer Manager App to a softphone in order to be able call automatically my customers? Thanks indeed
Hi and thanks very much. You would want to check out the API of the softphone which should allow an import or sync with data in other formats such as Excel. I hope this helps and thanks so much.
HI and thanks very much, you can check out this training here: th-cam.com/video/guqUCV31NBo/w-d-xo.html Thank you for your Likes, Shares & Comments. It really helps.
HI and thanks. This is new security being added in Excel Downloads however it is easy to fix, once and for all, for any of my downloaded applications. I have explained how to do this on this webpage: www.excelforfreelancers.com/how-to-fix-macro-blocking-issue/ I hope this helps and thanks so much.
Hi David, thanks for your comment. You can use a 1-click view to the main Customer List which can hold unlimited invoices. (This list can automatically be filtered by the selected customer) I hope this helps and thanks so much.
HI thanks I have many videos on Inventory which would apply to automobiles so please check those and then you can create one based on my training. Thanks so much.
Hi and thanks for your comment. I show you how to add user security in this video th-cam.com/video/0ahYoy5L3ec/w-d-xo.html I hope this helps and thanks so much.
Hi Randy, What an awesome experience this was for me. Thank you so much, it was an epic presentation, learned a lot. I do have one question though. How do you create a macro to delete the contact information on the contacts sheet by clicking the delete contact button. Please help!
Hi Ryno, thanks very much and I am glad you enjoyed the training. If you download the free application using the links in the description i have included the macro to delete the contacts. I hope this helps and thanks.
Thanks so much for the feedback. This was a few years ago, so hopefully I have slowed down by now. Please check this weeks training, premiering Tuesday, from scratch where i go line by line here: th-cam.com/video/4_WyyX32Ijo/w-d-xo.html I hope this helps and thanks so much.
Hi Tom thanks so much. I am glad you like the lesson and I will try to slow down a bit. I really appreciate the feedback. (Sometimes i have so much to cover in these trainings I tend to speed up a bit) Thanks again.
@@ExcelForFreelancers - No worries it's all good .The time you take to put these together really means a lot to so many people, including me :) Question, Can I integrate the Customer Manager with the Sales and Invoicing ? In other words, can I get the two of them to talk together? Thanks in advance. Tom
Hello Tom, Thanks so much. Yes certainly you can. In fact you may want to create an application with both of these together. In my Mentorship Program I am combining Invoicing, Customer Management, Inventory, Items, Full Accounting, Vendors, Emails & Notifications, Sharing & Sync, Multi-User along with Purchasing and a full Dashboard into a single application. You can learn more about that program here: bit.ly/MentorProgram_YTComment
Hi Randy, Another very good tutorial. I am learning a lot from your tutorials, thank you. In this workbook, it is so clear, I was able to adapt it to my needs, few more fields for the customers and I have doubled the fields for the contacts. I would like to use mapping to save any changes in the customer panel, like for the contact informations, I have tried to use the same methodology you used by adding the code in sheet1, but it gives me an error when I change customers. Can you guide me by telling me where to put the code if it is possible to use the mapping technique??? Thank you Guy
Hi Guy, 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 40,000 Members who would love to help you with this. Thanks so much.
Hi Randy, Thank you for your answer and Best Wishes for the New Year. I found my answers in another one of your great tutorial « Mappable Data Masterclass ». I just had to adjust my flags and criterias. Thanks again. Guy
Hi Randy, As told before, I adapt it and it works very well. However I have another question about the maintenance of the contacts. When I delete a customer, the contacts for that customer are staying on the contacts sheet, should they be remove in order to have a cleaner base?? If so, should I use the information already on the contacts sheet in the filter section, or should I use another method?? Thanks again for your fantastic videos as they are very instructive. Guy
Hi do you have some technique that arranges datas of different columns of cells into one thesame column with condition. Like for example Players are ranked 1 to 3 And their prizes are gold, silver, bronze Is it possible to arranged the players together with their prizes in order in a row using formulas in Excel. Like this format 1st Gold 2nd Silver 3rd Bronze
HI and thanks very much. You can use a custom sort feature for that. I teach you how to do that in this training in Kanban tool where i sorted based on urgency. You can see it here: th-cam.com/video/3qbU5S8ZaEA/w-d-xo.html I hope this helps and thanks so much.
Howdy Randy, Your work is amazing! I built this bad boy and it rocks! I did add one thing to the beginning of the Cust_Delete() function to make it a little harder to delete a customer on accident: ' CONFIRM DELETE WITH MESSAGE BOX Dim messageResult As Long messageResult = MsgBox("ARE YOU SURE YOU WANT TO DELETE A CUSTOMER???", vbYesNo + vbExclamation + vbDefaultButton2) If messageResult = vbNo Then Exit Sub End If Looking forward to learning more from your great videos! Warm regards, Dave
David Nelms after watching the entire video I was going to add to comments but it has already been highlighted in this comment instead , great work by the way
Hi, I haven't seen all your video's so I'm sorry if I'm requesting something that you may have done. It would be nice to see a video on how to make a customer loyalty program so they earn points on their shopping and redeem as they wish.
This is fantastic, I know is an "oldie" but is just awesome to see all those codes at work. Just have a quick question, is there a way to setup a "search" area for customers, let's say i have 500 customers, instead of going down each row on the Select Customer dropdown I can simply type the name of the company. Is this possible? Thank you Semper Fi
Hi Pedro, thanks so much. Yes you can autocomplete the name when you start typing it in. I have a video on that here to show you how. th-cam.com/video/PVEzHbdHf1Y/w-d-xo.html Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers Thank you for your help, I needed this to list cattle ear tags so a list for 3000 heads would be impossible to read, therefore I used the new Dynamic Array Function as a work around, not pretty as having the list dropdown visible, although the list still there but access a different way. Thank you for taking time to respond to me.
HI Mahmoud, thanks so much for your comment. This particular view shows the last 10 however you can customize this to run and advanced filter with unlimited results on a different sheet. 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
Hello. Can you please explain your mentoring program in greater details. So far I understood that I will be building an accounting application for several months.??
Hi Natalia, thanks for your interest. While I am building an accounting application within the program, you are welcome to build any application you like. I will teach you the fundamentals of application design and development as well as how to sell, secure and license your applications. For a limited time, you can join our Mentorship program for just $7 and get the first 5 weekly modules. You can sign up here: bit.ly/7Promo_YTComment Thanks so much
Hello Randy, Very nice course, thanks for sharing all this knowledge and for your time. Is it possible to have the workbook at the beginning, not will all the macros already written, kind of a Before and After thing, so people can follow along, which is very useful for learning. For instance, you imported the buttons at the beginning. Where did you import them from? Can you provide them in the Before file?
Hi and thanks I am pretty much doing that will all of my videos now. In the case of the Customer Manager, all i did was write the macro names, nothing else. My videos are quite long so I try to save time when I can, and still write every line of code. In the customer manager, not one macro was written before, in fact every macro was written during the video. Is this also what you found during the video?
Excel For Freelancers I haven't yet watched all of it, I downloaded the file to follow along and the 'problem' I had was the file was already complete, everything was done, the code, the buttons in place and already assigned to macros, so I stopped and wanted to get the Before file (without the code, with the buttons and all the input data sheets), just like the situation you were in when you decided to create the Customer Manager. If you could provide the files for your videos (past and future), it would be wonderful. Thanks again.
Hi Brittager, All you would have to do is download my file and delete everything so you can start from where i started with. This would be easier then having to download two different versions. I hope this helps and thanks.
@@ExcelForFreelancers Hi Randy. I tried deleting every module from the file. As a consequence, the buttons are still hyperlinks, so there is no way for just selecting them and moving them around for example. As soon as I click on any one of them, I just get the message that the macro they're linked to is not available.
Yes of course, deleting everything, would include deleting the buttons too. If you delete a macro, but do not delete the button connected to to that macro, and try to run the macro, you would get an error.
Hi and thanks very much. You would first want to have a basic understanding of VBA before using or customizing these templates. I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
Hi Randy, very nice video again!! 2 questions: 1. I see in your video that the active cells got highlighted yellow on de rows and column bars. Is this a default setting in Excel or something else? I will like that too ;-)) 2. I was looking on internet for free iconsets to use in Excel applications. Could not find anything usefull. Do you have any suggestions? Thanks in advance! Regards, Jacob.
Hi thanks Kebas, this is a default excel setting on the column and row headers and concerning the icons, I have found a lot of useful ones here: icons8.com/iconizer/ I hope this helps and thanks for your comment.
Hi and thanks I am sure that is possible with some customizing however I have not tried. Perhaps you can post your specific requirement in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
Good but complicated .I downloaded the sheet entered my own datas but one major problem cropped up in invoices section. Across all customer id same invoice detail cropped up
Hi Vija, 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 40,000 Members who would love to help you with this. Thanks so much.
HI and thanks very much. I show you how to do the mobile integration in this training: th-cam.com/video/t4PvToFc1OU/w-d-xo.html I hope this helps and thanks so much.
Thanks Desi, some people love userforms, but not me. :) Sometimes i use them when i want to create or edit data from multiple screens but I find sheet-based data forms easier to create and more flexible with design and functionality. I do have a few videos on 'Automated tab order' please feel free to search my channel on that here: th-cam.com/users/ExcelForFreelancers Thank you for your Likes, Shares & Comments. It really helps.
Thanks for your fast reply! I've watched your video 'How to automatically Set the Tab Order, Save & Load Data' and it worked great! Now putting it to the test in a different file :)
HI Yasir, I have a General Journal you might like here: th-cam.com/video/Js4pQR2ASsM/w-d-xo.html Thank you for your Likes, Shares & Comments. It really helps.
Hi and thanks so much. I show you how to sync with Outlook Calendar reminders in this video: th-cam.com/video/3n-bzYQqJyE/w-d-xo.html I hope this helps and thanks so much.
Everything was going perfect, my template is looking great, but when I ran the 1st ever macro, it is not working. I got "Run-time error '1004': Method 'Range' of object '_Worksheet' failed" And when I click Debug, it selects the range row below: For CustCol = 1 To 13 .Range(Sheet2.Cells(1, CustCol).Value).Value = Sheet2.Cells(CustRow, CustCol).Value Which is exactly the same as yours, how could I fix this? Thanks!
HI and thanks for your comment. When you have the bug, and its highlighted in yellow, hover your mouse over the CustCol variable, and see what number it says. For example, if it says 6, then you would want to go to Sheet 2, row 1, column 6, and make sure that inside that cell it must be a cell range like C3 or B5, or any other Cell. Mostly likely what is in this cell is not a range, or empty. I hope this helps and thanks so much. (Please check all values in row 1 from column 1 to column 13 to make sure they are values) Also hover over CutRow variable and make sure that is a whole number as well. 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 thanks so much. You can just pull the Row of the customer and then delete the entire row. I cover this in my Employee Manager series in which you can find on my channel. th-cam.com/users/ExcelForFreelancers Thanks so much.
Hi Randy. Thank you for this great video. Is it possible to add the same « add, delete, update » functionalities for the invoices and work orders sections in case we want to add invoices or work orders once a customer has been selected and in case we want to update status from open to close. Can the same technique be used? Also it would be nice as well to be able to update contact details directly from the main spreadsheet. Are you planning to make a second part?
HI Jersey. Generally people don't watch 2nd and 3rd part videos so I am trying to do less of them. With that said I have videos to create both Invoices and Work Orders. All you would have to do is combine these into a single application to have a fully functional Customer, Work Order and Invoice application. I hope this helps. Thanks for your comment.
![Learn How To Create This Mini CRM Application In Excel Today [Full Course + Free Download]](http://i.ytimg.com/vi/KMN0nELzcas/mqdefault.jpg)
![Learn How To Create This Mini CRM Application In Excel Today [Full Course + Free Download]](/img/tr.png)
![[UNCUT] "เสรีพิศุทธ์" แฉยับ! งัดหลักฐานใหม่ชั้น 14 ได้นอนคุกสมใจแน่!! I คนดังนั่งเคลียร์ I 2 ก.ย.67](http://i.ytimg.com/vi/O1SSg4p9Izs/mqdefault.jpg)
![Learn How To Create Your Own Monthly Budget Application In Excel From Scratch Today [1 Hour Course]](/img/n.gif)
🔥Download 350 Of The Worlds Best Templates Here ► rebrand.ly/350Wkbks_YTPinnedCom
I've used my COVID time at home wisely by watching and learning from these training videos, thanks again Randy 👍
That is the best way to use the extra time, improving and learning. Fantastic!
I did the same :)
Another fantastic lesson Randy! I have to keep telling myself that you have tons of experience and you have a pretty good idea what your going to show us before you start, because sometimes working through all the connections and dependencies makes me think my head will explode and you never lose track...lol. I may have a concern to work out as at one point i deleted the last customer, who had contacts, invoices, and work orders which then ended up belonging to the next customer I added new. I'm rethinking the whole 'Delete Customer' thing, but I will work it out. I'm not sue it makes sense to delete invoices etc., or to orphan them when the customer gets deleted.
In any event, I am just doing this to learn at this point, and that is working 100%. You are excellent at designing, programming and presenting all this material for our consumption. I highly recommend you and definitely give you 5 stars. Thanks!
Deleting records can be a dangerous thing for applications, especially when there are invoices and payments. Generally when you have dependences you will not want to delete customers, Better to have an Active/Inactive option for large applications. I my Accounting Applications Customers and Vendors cannot be deleted if Invoices, Purchases or Payments have already been recorded.
@@ExcelForFreelancers if i may, it is recommanded not to delete records like this, due to legal\iligal problems it may arise
Hi Randy,
I just recently discovered your channel and this is the first tutorial I followed along from start to finish. Thank you so much for your teaching & sharing.
I retired last year from my corporate gig and wanted to start my own property maintenance related business this year. I’ve been looking for some guidance on how to build a relational-type database in excel that would handle a contact list and invoicing, and you’ve just provided it to me. My next video to conquer will be Invoicing and then Work Orders. After that will be creating a schedule for the work to be done and an email confirmation to the customer. You have it all so far and more business related stuff that I never even thought of doing in Excel. Because of all the Covid-19 business going on right now, looks like I’ll have to delay my start-up until next year, when I can actually knock on some doors for drumming up some business. But I’ll have time to learn all your tips and tricks, and build a nice little application for next year.
Regarding this video, I identified 3 gap items where 2 of them were addressed in today’s download.
1.) The missing Delete Contact code was corrected. Many others commented too.
2.) When an existing Customers Contact detail was updated, that detail wasn’t carried back to the hidden columns (O~R) in the CustomerMgr worksheet. But you added the “LoadContacts 'Reload Contacts” line of code into the Sheet1 Change section. Good job!
3.) If you delete Dexter Bauer from Asia Electric (as one example), the row numbers in Column G do not update accordingly except for last row, where Amy Smith was inserted with the = "=Row()" coding. You may want to run that formula all the way up to Row 4 in column G.
4.) This final item is not necessarily a gap item, but more of a clean-up item. Cust ID’s 1 and 2 were deleted from the Customers table, but not from the other 3 worksheets. Might be a good thing to cover if this is your first one-to-many tutorial. Thoughts?
I’m curious about further enhancements, and maybe/probably you’ve covered them in other video’s. If you have, kindly direct me.
1.) The Invoice and Work Order tables are only 10 rows long. Is it possible to add a vertical scroll bar to each table for lists longer than 10 rows? Descending would be nice too.
2.) If you click the “Add Contact” button in error, there’s no elegant way to bail out. An “Abort” button beside the “Save Contact” button might be nice. Rather than reselecting the Customer to restore the data that was cleared out.
After thinking a little more about 4.), maybe the better approach would be to first check to see if there are any valid related records that should be kept. If there are, disallow deletion of the Customer. If there are no valid/active related records, proceed with the cascade delete activity.
Hi Les, thanks so much for your feedback and details. I used to run a maintenance company for 10 years so you will see a lot of similarities in the work. Most of the applications I develop for TH-cam are basic ideas and concepts that show the potential of Excel and are very far from complete, ready-to-use applications. Therefore if you would like to use these in real-world situations a lot more needs to be done with them. I try to fit the training within an hour or so each week, so there are always going to be features to add, and code to write to help debug issues.
I hope this helps and thanks very much for the feedback.
Couple of notes... you always show us hot to get the Developer TAB... Just RIGHT Click up in the ribbon area, the second Option is CUSTOMIZE THE RIBBON. Also you do lots of Cell formatting, being an old DOS guy, keyboard shortcuts are my favorite. ALT {H,FN} Brings up the Format Cell Dialog box. Some people like the mouse, some like the keyboard. I know there are plenty of places to find them but just thought maybe someone might like it. Thanks
Thanks Erik, I am going to try to use more keyboard shortcuts, just as you suggested (especially the Format Cell Dialog as I use it 100 times when creating these) ALT {H,FN). This way I will get use to it and I can teach others. I am a 'shortcuts' guy so anytime i can save time, as you see with the automated code via Autohotkey, so this will be helpful. Much appreciated.
OMG. Such a perfect video again. Thanks for teaching us Sir. You are the best mentor....
For sure, you are very welcome. Thank you for your Likes, Shares & Comments. It really helps.
I cannot believe am getting all these for free. May the good Lord bless u mightily Randy and this is golden 👌
Thanks so much Ken, I am glad you enjoy the training and the downloads. If you would like to support us, you can get 100 of my applications for just $37.00 right here> www.excelforfreelancers.com/product/100-of-my-best-excel-workbooks
Thanks so much.
I would definitely want to support u. Let me gather some cash then I'll be back hopefully soon
Thank you so much Ken. Much appreciated.
you are the god of excel. thanx for sharing this workbook.
Thank you so very much, I really appreciate that.
Dear Randy. Again an excellent professional training video.
I really appreciate the effort you put in every time. Stay healthy and keep up the excellent training work.:)
Thanks so much Bart, I really appreciate that and take care of yourself as well.
Your video is a nice one. I do have plenty in my channel. Yet, its something i have ever liked to watch the method of teaching.
Thank you very much, I really appreciate that Excel Passionne
Thanks Randy 👍 I'm learning a lot from your tutorial videos
That is great to hear Avie. :)
Excellent video, the best there is on the web!!
Wow, thanks so much Paddy. I do appreciate that.
Honestly, you are the King of excel. Thanks a lot, One question if I may. The delete Contact button is nor working, if you could just tell me how to activate it I will really appreciate it. Thanks again! You Rock!
Hi Jermel, thanks so much for your comment. You can right click the button and assign it to the macro to delete contacts. You can also download the updated version using the links in the description which should have this button working. I hope this helps and thanks so much.
Very good and funny videos bring a great sense of entertainment!
Thank you so very much, I really appreciate that.
Hi Randy, Thank you so much i'v been looking for someone like you for long time..but i had some problems creating my customer manager sheet, the first is the MOD FORMULA and the second is OFFSET FORMULA. thank you again for all your effort and your members behind the screen.
Thank you very much Bilal, I am very glad I could help. Thank you for your Likes, Shares & Comments. It really helps.
Truly appreciate all your sharing. it helps me a lot
I'm so glad! Cam On Em.
I really love your tutorial videos. Excellent
Thank you Arlene, I really appreciate your comments.
Beautiful video
Thank you so very much, I really appreciate that Shahidudl
Hey Randy, I thoroughly enjoyed building this from scratch while watching you teach it. I have been wanting to build something like this & now that I have the foundation that I have been looking for, I am on my way to a good & solid start on my application & getting paid for it too lol. 1 question: Where do you recommend good icons that i cane use freely or spend small amount of money on? 2 There was not Cancel button when adding a new contact. So I ventured in creating the button & building the vba for it. As I look at the coding I have to laugh cuz it is somewhat like how many you would write it, but clunky too.
Hi Christine, thanks so much for your feedback and I am really glad you love the training. There is really so much more that can be done with this. To answer your questions:
1. I get a lot of my icons from here: icons8.com/iconizer/ (it is a great site with tons of customizations and content)
2. A cancel button is a great idea with a lot more you can do with it. You can also just select the first contact in the list to Cancel, and if there is no contacts in the list, leave it in the Add New state. I hope this helps and thanks so much
you can also add icons in excel via "Insert" > "Illustrations" and look there for the button with a bird or go via this link to find the way
support.microsoft.com/en-us/office/insert-icons-in-microsoft-office-e2459f17-3996-4795-996e-b9a13486fa79
This is awesome job Randy,thank you soo much
Thanks so much. I am really glad you enjoyed the training.
Very informative instructions
Thank you so very much, I really appreciate that Mala
i am downloading your workbooks and watching your teachings and i'm amzesd! didn't knew excel can work magically like this!
aside from downloading the templates, i'm learning how to create those programs, and i have a question: does VBA changed their syntax?
because when i got to the ".Range(Sheet2.Cells(1, CustCol).Value).Value = Sheet2.Cells(CustRow, CustCol).Value" line, there is a failure that requires debug (and i tried to copy the line too).. aside from that - everything is working greate (again, until it gets to that line)
Hi and thanks very much. During the debug, when the line is highlighted, hover your mouse over CustCol and alco CustRow to make sure both are whole numbers
IF they are you would need to check Sheet2.Cells(1, CustCol).Value to make sure it is a Range such as E4 or F4, or whatever. So that means if CustCol is 3, you would check Sheet2, Cell C1 to make sure it contains a cell address.
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
@@ExcelForFreelancers thank you for your answer!
it does helpful!
took me some time to realize my mistake but with your help - i did!
thank you so so much!
Great I am glad you were able to locate the issue. Very well done
Awesome as always. Keep it on Randy
Thanks! Will do and I appreciate that.
amazing informative video highly appreciated your way of teaching
Thank you so very much, I really appreciate that. I have a video coming this Tuesday which will show you how to do a One Click Update for this application. You won't want to miss it.
Thank you
For sure, you are very welcome and thanks so much
Dear randy love the way u explained need your help
Thank you so very much, I really appreciate that. 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 Sir! Wonderful tutorial.
For sure, you are very welcome (This Tuesday I have a Restaurant POS application you won't want to miss) Thanks again.
UNLIMITED LIKE AS USAUL
BEST TEACHER
BEST CHANNEL
BEST VIDEOS
THANK YOU SIR
ALL RESPECT
So nice of you and thanks so much Youcef
Where can i find the video to make the buttons? Thanks!
Hi and thanks, the buttons are just basic shapes, however, you can check out any of our 'From Scratch' videos in this playlist th-cam.com/play/PLIBeRriXvKzCHVxFYRSMTZu3PmnVn4ujb.html
Thank you for your Likes, Shares & Comments. It really helps.
Well ... reading just a few comments and .....
I guess nobody knows what the word "FREELANCER" means.
I like these videos because ....
1. I get an idea of what things a business might buy ( great ideas by the way )
2. I see how he implements stuff ( not always in agreement with his coding or style but always open to new ideas.
3. Eventually or maybe there might be a download to use. But why watch these videos if you're not going to learn how to do it.
If you need it for ....
School - quit cheating
Work ( non IT ) - hope you aren't getting paid to make it as he's done all the work
Work ( IT ) - omg .... pay the man for the software
Dude since I seen the title for this vid awhile back ... been drooling to hear how you've developed your client base. Will get this watched tonight after din din.
Hi John, thanks very much for the comment. I hope you enjoy the training once you see it (set aside around 90 minutes as each of my trainings are at least an hour)
Certainly a lot of people are just here for the templates to download, but so many actually enjoy watching and learning how the process is made so they can learn it themselves. Also it takes some of us time to realize there are 'no shortcuts in life' I am sure when I was younger I thought there was.
Thanks so much for the feedback and great comment.
@@ExcelForFreelancers Ugh ... I was more hoping to hear about "HOW" to find customers cold. LOL.
Still like your stuff tho.
Fantastic :) appreciate your time and efforts .
Thank you so very much, I really appreciate that.
Great work on this Excel project. I really learned a lot!
Thank you so much Perry. I am glad I could help.
Thanks you sir
For sure, you are very welcome
Great Information! Thanks
You are very welcome. I am glad you liked the training.
You are really awesome
Thank you so very much, I really appreciate that.
Dear Randy I really learn from your training video and i appreciate the great work that you do
but would you mind to tell me which vergen of excel you use ?? I v been searching for a week to no
how you wright the macro with auto complete option ....you wright (with sheet )and then it auto complete (end with ) and so on ......many thanks
HI and thanks so much. I am using Excel 2010 however I automate my typing using AutoHotkey. I show you how to do that in this video: th-cam.com/video/gkmiNkbvpkE/w-d-xo.html
I hope this helps and thanks so much.
Hi, thanks for all the tutorial and videos. Superb job.
Regarding deleting Customer - The contacts related to the customer are not deleted. This can be unfortunate, is for some reason a new customer gets assign an old ID, where contacts are. So can someone provide a code, that deletes all the contacts related to a customer, that is deleted?
Hi and thanks so much for your comment. I have something similar in which we delete an Invoice and then all of the items, associated with that invoice, are deleted. The code is very similar. Here is the training th-cam.com/video/YC-SZlIwj5c/w-d-xo.html
I hope this helps and thanks so much.
one more question please, how did you connected B2 at sheet1 to the dynemic chneges?
i didn't saw you make a fromula for it..
Hi and thanks. Within the save/Update macro the value of B8 (next customer ID) is transferred to B2, however it is an existing customer it will come directly from the database on customer load. I hope this helps and thanks so much.
Hi Randy, another great tuition saving us time and money 💰.
Would it be possible to create an complete sales application in the future?
A sales application combining customer manager, inventory and invoices.
Where a sales agent could place a customer order depending on the inventory, and print invoices, and customer statements from one application.
Many thanms for all your applications and videos, they help a lot.
Hi Robby, thanks so much. I am really glad you enjoyed the training. I am actually doing all of that and a whole lot more in the Accounting application that I am creating in the Mentorship program which includes (Customers, Vendors, Items, Inventory, Invoicing, Purchase Orders, Credit Memos, Multi-user sharing & Sync, Dashboard, Chart Of Accounts, General Ledger, Transactions and full dashboard and reports section. You can learn more about this program here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
valuable videos
Thanks so much. I am glad you enjoy the training.
Well-Done Man, thanks for the wonderful tutorial.
You're welcome! Aimal, and thanks so much.
Thank you for sharing.
For sure, you are very welcome Nandan
Hi Randy
Thank you for this fantastic lesson.
I'm trying to un this macro, but I can't because I receive the error "Run time error 438 object does not support this method"
It's happening when I set the criteria.
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.
Hey thanks for your answer
Problem solved, I figured out that the sintax of the criteria was not the same as the original headers.
ok very good i am glad you were able to figure it out. Thank you for your Likes, Shares & Comments. It really helps.
super....
i want this formate
Hi and thanks. You can download this file using the links in the description above with either your email or Facebook Messenger. I hope this helps and thanks.
what a lesson ! I like it and want to get a book
Hi Sondan, you can download this file using the links in the description above with either your email or Facebook Messenger. I hope this helps and thanks.
Is there a ready software that can do this with similar interface? Please, help me.
Hi Omar, thanks so much. You are welcome to download this, customize it and use it however you like. I hope this helps and thanks so much.
Hi Randy . Do you have customer manager part 2 tutorial . That shows work order van codes
Hi Yahya this was only a single training. Thank you for your Likes, Shares & Comments. It really helps.
Thank you very very much for this fantastic application.. however i need from you a small favor that how to make the amendment/ change in the contact (list in right side) needs a (update) button next to (add) and (delete) i hope you understand me sir .... I appreciate your help on this sir
HI and thanks, you are welcome to hire a developer from our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 50,000 Members who would love to help you with this. Please mark the post as a PAID JOB. Thanks so much.
Hi!
LOVE your work.
Question...what if I want to use 2 criteria, example...only show the unpaid invoices
Hope you can help
Thank you
Oh my GOD!
I just totally did it. Hope it works. :)
Hi Renata and thanks for your comment. You can use multiple criteria in an Advanced filter. I show you how to do this in this video: th-cam.com/video/YuCrEWU2Tm0/w-d-xo.html
@@ExcelForFreelancers AMAZING Thank you very much
Hello po, your video is kinda interesting and i want to create one but i just want to ask how did u create the customer's list? like putting add contacts button etc. from customers sheet to customer manager sheet. thank u!
Hi and thanks you can use the Add New and Save buttons to add new customers.
I hope this helps and thanks so much.
Hi Randy, I am wondering if you could customize the Customer Manager Data Base to my need and how much would this cost. It is basically all you have in this workbook plus one more worksheet/ Table for " Cost of Materials for each order" Please let me know if you need further details.
HI Moe, Thank you for your message. Thank you for your message. I am really glad you enjoyed the training and I do understand your requirements. Since I am developing courses I am unable to take on any additional work, however I would be happy to refer you to a competent developer who could handle this for you.
Just let me know if you would like to proceed and I can forward your details to them.
Thank you very much.
Randy@ExcelForFreelancers.com
Nice video good video
Thank you so very much, I really appreciate that Ray
I love this.
Great, thanks so very much David
Hi Randy, brilliant video as always however I was wondering if there was a way of having the notes field scrollable, so it can be more of a history of contacts and you can see when they have been added rather than just as a small box
Thanks Mark
Hi Mark thanks so much you could add a form control or Active X scrollable box that allows for a larger amount of notes. Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers Thanks for your reply sounds so obvious now you have said it :)
hello Randy, thank you for this amazing tutorial, however can I know how should I put data in the Invoice and work order sheets, I am having a hard time, since Invoice and workorders contains only the customer ID, how do I enter data in the sheet since I do not know where to get the customer ID's or How will i know the customer ID's. Thanks in advance
Hi and thanks very much. I have a specific video on that to help you here: th-cam.com/video/99xVbVMABJ0/w-d-xo.html
I hope this helps and thanks so much.
hey Randy Great Video.. quick question how do you update the invoices or work order ? I'm not sure if I saw how
Hi and thanks so much. You can clear the existing Invoices & Work orders and then run an advanced filter based on the Customer Name or Customer ID and then bring in those results into the range.
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
Hello Randy Thanks indeed for such a good training, I would like to know how can I link the phone numbers in this Customer Manager App to a softphone in order to be able call automatically my customers? Thanks indeed
Hi and thanks very much. You would want to check out the API of the softphone which should allow an import or sync with data in other formats such as Excel. I hope this helps and thanks so much.
we thank you. Is there a search in closed file?
Hi thanks for your comment. I did not add a search in this one however you are free to customize this to add any feature you like . Thanks again.
Hi may i have the link tutorial for your add customer button? Thank you.
HI and thanks very much, you can check out this training here: th-cam.com/video/guqUCV31NBo/w-d-xo.html
Thank you for your Likes, Shares & Comments. It really helps.
Gr8 lessons. Easy to learn and thank you so much!
My pleasure and I am glad you enjoyed it Armo.
Good video
Thanks so much, very kind of you
Loooooooove when you do it like this 🙏🙏😊 Thank uuuu 😊
Thank you so much Lars. Yes I am trying to do most from scratch and thanks so much.
Thank you buddy but I am unable to run Macros for downloaded excel sheet
HI and thanks. This is new security being added in Excel Downloads however it is easy to fix, once and for all, for any of my downloaded applications.
I have explained how to do this on this webpage: www.excelforfreelancers.com/how-to-fix-macro-blocking-issue/
I hope this helps and thanks so much.
Good Videos
Thank you so very much, I really appreciate that Moriom
Hey! I really appreciate your work. You are doing a great job.
Would you please make a school fee manager excel based worksheet .
Hi Thanks very much This is a larger project so I will do perhaps on a full course but I will try. Thank you.
@@ExcelForFreelancers Thank you for your kind words. You are awesome.
What if there are more than 10 invoices or WO's? Can the use of a scroll bar within the defined area be used?
Hi David, thanks for your comment. You can use a 1-click view to the main Customer List which can hold unlimited invoices. (This list can automatically be filtered by the selected customer)
I hope this helps and thanks so much.
Good Knowledge
Thank you very much. I do appreciate that Julius.
Please make a video automobiles account and inventory, servies, spair parts, and other sir
HI thanks I have many videos on Inventory which would apply to automobiles so please check those and then you can create one based on my training. Thanks so much.
Hi dear randy can't you add password or pin in case someone want to modify or delete Important information?
Hi and thanks for your comment. I show you how to add user security in this video th-cam.com/video/0ahYoy5L3ec/w-d-xo.html
I hope this helps and thanks so much.
Hi Randy, What an awesome experience this was for me. Thank you so much, it was an epic presentation, learned a lot. I do have one question though. How do you create a macro to delete the contact information on the contacts sheet by clicking the delete contact button. Please help!
Hi Ryno, thanks very much and I am glad you enjoyed the training. If you download the free application using the links in the description i have included the macro to delete the contacts. I hope this helps and thanks.
Excellent content Randy. Please slow down a bit. Your delivery is too fast for me. Thanks
Thanks so much for the feedback. This was a few years ago, so hopefully I have slowed down by now. Please check this weeks training, premiering Tuesday, from scratch where i go line by line here: th-cam.com/video/4_WyyX32Ijo/w-d-xo.html
I hope this helps and thanks so much.
Great information Randy, but my brother you sure do talk fast :) Thankfully I found the setting to slow it down as well as pause and start button ;)
Hi Tom thanks so much. I am glad you like the lesson and I will try to slow down a bit. I really appreciate the feedback. (Sometimes i have so much to cover in these trainings I tend to speed up a bit)
Thanks again.
@@ExcelForFreelancers - No worries it's all good .The time you take to put these together really means a lot to so many people, including me :) Question, Can I integrate the Customer Manager with the Sales and Invoicing ? In other words, can I get the two of them to talk together? Thanks in advance. Tom
Hello Tom, Thanks so much. Yes certainly you can. In fact you may want to create an application with both of these together. In my Mentorship Program I am combining Invoicing, Customer Management, Inventory, Items, Full Accounting, Vendors, Emails & Notifications, Sharing & Sync, Multi-User along with Purchasing and a full Dashboard into a single application. You can learn more about that program here: bit.ly/MentorProgram_YTComment
Hi Randy,
Another very good tutorial. I am learning a lot from your tutorials, thank you.
In this workbook, it is so clear, I was able to adapt it to my needs, few more fields for the customers and I have doubled the fields for the contacts.
I would like to use mapping to save any changes in the customer panel, like for the contact informations, I have tried to use the same methodology you used by adding the code in sheet1, but it gives me an error when I change customers.
Can you guide me by telling me where to put the code if it is possible to use the mapping technique???
Thank you
Guy
Hi Guy, 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 40,000 Members who would love to help you with this. Thanks so much.
Hi Randy,
Thank you for your answer and Best Wishes for the New Year.
I found my answers in another one of your great tutorial « Mappable Data Masterclass ».
I just had to adjust my flags and criterias. Thanks again.
Guy
Great to hear that Guy. I am so glad you were able to find the answer :)
Hi Randy,
As told before, I adapt it and it works very well.
However I have another question about the maintenance of the contacts.
When I delete a customer, the contacts for that customer are staying on the contacts sheet, should they be remove in order to have a cleaner base?? If so, should I use the information already on the contacts sheet in the filter section, or should I use another method??
Thanks again for your fantastic videos as they are very instructive.
Guy
great job as always
may you tell me how to get such nice icon or web site plz
HI thanks you can find many great icons right here:
icons8.com/iconizer/
Thank you for your Likes, Shares & Comments. It really helps.
Hi do you have some technique that arranges datas of different columns of cells into one thesame column with condition.
Like for example
Players are ranked 1 to 3
And their prizes are gold, silver, bronze
Is it possible to arranged the players together with their prizes in order in a row using formulas in Excel.
Like this format
1st
Gold
2nd
Silver
3rd
Bronze
How to do this without using too much nested it's :'( ?
HI and thanks very much. You can use a custom sort feature for that. I teach you how to do that in this training in Kanban tool where i sorted based on urgency. You can see it here: th-cam.com/video/3qbU5S8ZaEA/w-d-xo.html
I hope this helps and thanks so much.
Howdy Randy,
Your work is amazing! I built this bad boy and it rocks! I did add one thing to the beginning of the Cust_Delete() function to make it a little harder to delete a customer on accident:
' CONFIRM DELETE WITH MESSAGE BOX
Dim messageResult As Long
messageResult = MsgBox("ARE YOU SURE YOU WANT TO DELETE A CUSTOMER???", vbYesNo + vbExclamation + vbDefaultButton2)
If messageResult = vbNo Then
Exit Sub
End If
Looking forward to learning more from your great videos!
Warm regards,
Dave
Hi David, thanks so much for your comment and additional code. That is a great idea and thanks so much for sharing.
David Nelms after watching the entire video I was going to add to comments but it has already been highlighted in this comment instead , great work by the way
Hi, I haven't seen all your video's so I'm sorry if I'm requesting something that you may have done. It would be nice to see a video on how to make a customer loyalty program so they earn points on their shopping and redeem as they wish.
Hi and thanks so much Dharmesh. I do not have that training however I will add it to my list of videos to create in the near future.
This is fantastic, I know is an "oldie" but is just awesome to see all those codes at work.
Just have a quick question, is there a way to setup a "search" area for customers, let's say i have 500 customers, instead of going down each row on the Select Customer dropdown I can simply type the name of the company. Is this possible?
Thank you
Semper Fi
Hi Pedro, thanks so much. Yes you can autocomplete the name when you start typing it in. I have a video on that here to show you how.
th-cam.com/video/PVEzHbdHf1Y/w-d-xo.html
Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers Thank you for your help, I needed this to list cattle ear tags so a list for 3000 heads would be impossible to read, therefore I used the new Dynamic Array Function as a work around, not pretty as having the list dropdown visible, although the list still there but access a different way. Thank you for taking time to respond to me.
For sure, you are very welcome and I am glad you found a solution Pedro.
Please, when searching for a customer, for example, 50 invoices in the customer manager displays only 10 invoices, why?!
HI Mahmoud, thanks so much for your comment. This particular view shows the last 10 however you can customize this to run and advanced filter with unlimited results on a different sheet. 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
Hello. Can you please explain your mentoring program in greater details. So far I understood that I will be building an accounting application for several months.??
Hi Natalia, thanks for your interest. While I am building an accounting application within the program, you are welcome to build any application you like. I will teach you the fundamentals of application design and development as well as how to sell, secure and license your applications. For a limited time, you can join our Mentorship program for just $7 and get the first 5 weekly modules. You can sign up here: bit.ly/7Promo_YTComment Thanks so much
@@ExcelForFreelancers Thank you. Any other payments will be required in the future?
Hi Natalia, after the 5 weeks, if you want to continue, then its just $47 per month. I hope this helps and thanks so much.
Sir nice work 👌
Thank you very much. I do appreciate that.
Hello Randy,
Very nice course, thanks for sharing all this knowledge and for your time.
Is it possible to have the workbook at the beginning, not will all the macros already written, kind of a Before and After thing, so people can follow along, which is very useful for learning.
For instance, you imported the buttons at the beginning. Where did you import them from? Can you provide them in the Before file?
Hi and thanks I am pretty much doing that will all of my videos now. In the case of the Customer Manager, all i did was write the macro names, nothing else. My videos are quite long so I try to save time when I can, and still write every line of code. In the customer manager, not one macro was written before, in fact every macro was written during the video. Is this also what you found during the video?
Excel For Freelancers I haven't yet watched all of it, I downloaded the file to follow along and the 'problem' I had was the file was already complete, everything was done, the code, the buttons in place and already assigned to macros, so I stopped and wanted to get the Before file (without the code, with the buttons and all the input data sheets), just like the situation you were in when you decided to create the Customer Manager.
If you could provide the files for your videos (past and future), it would be wonderful. Thanks again.
Hi Brittager, All you would have to do is download my file and delete everything so you can start from where i started with. This would be easier then having to download two different versions. I hope this helps and thanks.
@@ExcelForFreelancers Hi Randy. I tried deleting every module from the file. As a consequence, the buttons are still hyperlinks, so there is no way for just selecting them and moving them around for example. As soon as I click on any one of them, I just get the message that the macro they're linked to is not available.
Yes of course, deleting everything, would include deleting the buttons too. If you delete a macro, but do not delete the button connected to to that macro, and try to run the macro, you would get an error.
I'm a front desk office and a total novice when it comes to codes and all...of I purchase your library, will I be able to use it?
Hi and thanks very much. You would first want to have a basic understanding of VBA before using or customizing these templates.
I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
Thank you so much! I'll take the course and come back for your library!
sir, how to invoice and work order details add record
Hi and thank so much. You can watch this video to learn how th-cam.com/video/CkbEQ_S91Y0/w-d-xo.html
Hi Randy, very nice video again!!
2 questions: 1. I see in your video that the active cells got highlighted yellow on de rows and column bars. Is this a default setting in Excel or something else? I will like that too ;-))
2. I was looking on internet for free iconsets to use in Excel applications. Could not find anything usefull. Do you have any suggestions?
Thanks in advance! Regards, Jacob.
Hi thanks Kebas, this is a default excel setting on the column and row headers and concerning the icons, I have found a lot of useful ones here: icons8.com/iconizer/
I hope this helps and thanks for your comment.
@@ExcelForFreelancers Could not find that in Excel 2016. Read it was default in Excel 2013? No problem, was just curious ;-)
The formula for conditional formatting is not working on me? The formula is =Mod (Row 0.2)=0, right?
Hi and thanks try this =Mod(Row(),2)=0
I hope this helps and thanks so much.
Are you able to use this with Power Apps and flow?
Hi and thanks I am sure that is possible with some customizing however I have not tried. Perhaps you can post your specific requirement in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 40,000 Members who would love to help you with this. Thanks so much.
god damn man you rock it
Thank you so very much, I really appreciate that Hellmut
Will this work in sheets?
Hi and thanks for your comment. This is exclusively for Excel and will not work in Google sheets.
Good but complicated .I downloaded the sheet entered my own datas but one major problem cropped up in invoices section. Across all customer id same invoice detail cropped up
Hi Vija, 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 40,000 Members who would love to help you with this. Thanks so much.
Please Make like this sheet for Mobile Supported... Because It is not working as possible in Mobile...
HI and thanks very much. I show you how to do the mobile integration in this training: th-cam.com/video/t4PvToFc1OU/w-d-xo.html
I hope this helps and thanks so much.
Love the video! Much easier then creating UserForms for data entry :) Is it also possible to create a tab order for the cells which require input?
Thanks Desi, some people love userforms, but not me. :) Sometimes i use them when i want to create or edit data from multiple screens but I find sheet-based data forms easier to create and more flexible with design and functionality. I do have a few videos on 'Automated tab order' please feel free to search my channel on that here: th-cam.com/users/ExcelForFreelancers
Thank you for your Likes, Shares & Comments. It really helps.
Thanks for your fast reply! I've watched your video 'How to automatically Set the Tab Order, Save & Load Data' and it worked great! Now putting it to the test in a different file :)
That's great to hear Desi, thanks so much.
Customer Ledger in excel
HI Yasir, I have a General Journal you might like here: th-cam.com/video/Js4pQR2ASsM/w-d-xo.html
Thank you for your Likes, Shares & Comments. It really helps.
How do i now get this linked with a calender for reminders
Hi and thanks so much. I show you how to sync with Outlook Calendar reminders in this video: th-cam.com/video/3n-bzYQqJyE/w-d-xo.html
I hope this helps and thanks so much.
Everything was going perfect, my template is looking great, but when I ran the 1st ever macro, it is not working.
I got "Run-time error '1004':
Method 'Range' of object '_Worksheet' failed"
And when I click Debug, it selects the range row below:
For CustCol = 1 To 13
.Range(Sheet2.Cells(1, CustCol).Value).Value = Sheet2.Cells(CustRow, CustCol).Value
Which is exactly the same as yours, how could I fix this?
Thanks!
HI and thanks for your comment. When you have the bug, and its highlighted in yellow, hover your mouse over the CustCol variable, and see what number it says. For example, if it says 6, then you would want to go to Sheet 2, row 1, column 6, and make sure that inside that cell it must be a cell range like C3 or B5, or any other Cell. Mostly likely what is in this cell is not a range, or empty. I hope this helps and thanks so much. (Please check all values in row 1 from column 1 to column 13 to make sure they are values)
Also hover over CutRow variable and make sure that is a whole number as well.
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
Nice video 😊😊😊
Thank you so very much, I really appreciate that Saiful
Really great content, its amazing what can you do with vba.
Greetings from Brazil.
Thank you very much. I do appreciate that Rafael. I do appreciate that.
Hello, thank you for the video. I have a question: How to delete a contact?
HI thanks so much. You can just pull the Row of the customer and then delete the entire row. I cover this in my Employee Manager series in which you can find on my channel. th-cam.com/users/ExcelForFreelancers Thanks so much.
Good experience. Difficult for me
Thanks so much for your feedback. These things take time to learn. Please keep at it.
Hi Randy. Thank you for this great video. Is it possible to add the same « add, delete, update » functionalities for the invoices and work orders sections in case we want to add invoices or work orders once a customer has been selected and in case we want to update status from open to close. Can the same technique be used? Also it would be nice as well to be able to update contact details directly from the main spreadsheet. Are you planning to make a second part?
HI Jersey. Generally people don't watch 2nd and 3rd part videos so I am trying to do less of them. With that said I have videos to create both Invoices and Work Orders. All you would have to do is combine these into a single application to have a fully functional Customer, Work Order and Invoice application. I hope this helps. Thanks for your comment.
@@ExcelForFreelancers hello, where can we see these videos to add new invoices or edit existing ones?