This training changed my life. I use data mapping all the time now in everything I build. I needed a way to create apps in Excel that end users wouldn't mess up by overwriting formulas and breaking the app. This was my solution. Absolute gamechanger.
Awesome! I am so happy to hear that and so glad you were able to capitalize on that. I also found a way to do Data Mapping with userforms here: ruclips.net/video/Oa480YUaJpw/видео.html I hope this helps and thanks so much.
Thank you for yet another session that one can learn so much from and apply it in practical terms. One question would be, correct me if I am wrong but the column numbers offset could be set as variables within code whereby removing the need for any being on the main sheet/form.
Hi and thanks. Yes there are a few ways to do data mapping. SInce i have created this video I have found a lot of more streamlined options for data mapping. Feel free to check out some of my newer videos. I hope this helps and thanks so much.
Perfect! I am glad I can help. I think you will really enjoy this one. I take my time and move slow, will go step by step. Thank you for your Likes, Shares & Comments. It really helps.
A true wizard, I told you before that all your videos are amazing, easy to understand with all your explanation of what the code is doing and why it's there. Great job. 🙂
Randy, Been watching your tutorials for some time, to me - a revelation! You do know you do a great job, don't you? Actually, you are one of a few who cares for such complex AND complete A-Z solutions. If you happen to have a spare minute, it'd be great to hear an advice from you: whenever you mention a sample of, say, 3 or 4 items/clients to be picked by NAME, there's no problem with that (like: oranges, apples, carrots - a short list, an un-alfabetic order is not a problem here). Problem arises when a customer/ items list gets much longer thus almost impossible to be picked by name, for such a list is not sorted A-Z. Items are fixed to given rows in excel DB, and in a drop-down menu they are presented in such an order (Oranges, Apples,Carrots and not: Apples, Carrots, Oranges). Is there a way to make a 'by-name' sorting/picking more user friendly? Greetings from Poland. A far, far away land. Chris (much Shrek-alike by the looks...)
Hi Chris thanks so much for the kind words. I am really glad you enjoy the training. I do understand about the longer lists. For sorting purposes when a new item is added, or a name is changed you can run a specific macro that uses and advanced filter (without criteria) that will place the names in a Unique list in another location. You can then sort this list (since its not the original data). You can create a dynamic named range based on this list like "Names_Sorted" and it is this list that you will pick your names from. If it is a long list you will want to add the auto-complete feature so that when users start typing in the names, they will automatically appear. I go over that feature in this training here: ruclips.net/video/PVEzHbdHf1Y/видео.html I hope this helps and thanks so much.
Thank you so very much, I really appreciate that. Yes that is correct. Nothing new here but many people wanted me to go slow and start from scratch on data mapping. So I was happy to help with that.
Hi Randy I am Really Learnt Lots of Excel Projects from your Channel , I am a Big Fan of your Excel Work - My Daily Rotuine is watching your Videos atleast 1 per day , as a Data Analyst & MIS handler - Your Videos are Helping me a Lot - Thank you for your Valuable Information sharing through these videos 🤩
Hello, I love the way you are teaching this program. I am in need of setting up a grave registry and this is the best form that I have seen in terms of ease.
Well Randy this video helped me alot in making my reports and formats more quickly as i always struggeled on data mapping but now i feel more comfortable in doing all this especially reports with pictures and attachments. Thank you so much for This :)
Excellent tutorial Randy! Very usefull for me. I often have similar clients/products. In this scenario I would >>> Change the Name and + delete the Product ID = and it makes a copy of all the specifications, but with a new ID and new NAME. What would it look like, to have a separate Button for that "NEW CLIENT". For this I tried the following: End If If .Range("E7").Value = Not Empty Then 'Remove existing ID .Delete End If If .Range("B6").Value = Empty Then 'New Client-ID .Range("E7").Value = .Range("B7").Value 'Get Client-ID ProdRow = DATENBANK.Range("A99999").End(xlUp).Row + 1 DATENBANK.Range("A" & ProdRow).Value = .Range("B7").Value 'Kunden-ID Unfortunately, it does not create a new product/Client. It just changes the NAME of the exixting one. Any ideas? Thanks for all the good work.
Hi Andreas, 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 50,000 Members who would love to help you with this. Thanks so much.
Can be available simple interest calculator in excel like Month date principal amount pri.deposite /wridhal interest balance But interest calculator simple rate but effect of deposited or wridhal calculator in interest
Hi and thanks for your comment. Using named ranges, or tables would be fine. I used named ranges as a preference so that the formulas are easier to read and to teach. I hope this helps and thanks so much.
HI and thanks you can use the Index/Match formula to pull the user ID from any 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
this is amazing you're a Genius. One question my computer says I have a error '438' : Object doesn't support this property or method. I don't know what that means.
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 30,000 Members who would love to help you with this. Thanks so much.
Thanks so much. I have written down your suggestion and will try to get to it in the near future. Much appreciated. Thank you for your Likes, Shares & Comments. It really helps.
Thanks so much. Yes I really don't like it as well. I hope this has helped and look forward to bringing you many more. Thank you for your Likes, Shares & Comments. It really helps.
The front-end development was interesting. Many opportunities to reduce the "hardcoding" of cells and ranges. Is there a reason why a named table wasn't used for the product list?
Hi Redge, thanks very much. I used named table sometimes however I like to use a single column, with my own names so they are easily recognizable in the formulas. I also don't like some of the restrictions when using tables. I hope this helps and thanks so much.
HI Tim, thanks for your comment. Yes there is. I believe I understand what you are asking. I have a Dynamic Userform which maps data dynamically based on selected database sheet here: ruclips.net/video/Oa480YUaJpw/видео.html Also next Tuesday I have a multi-view data sheet that you might be interested in here: ruclips.net/video/5Bw7TZ0IJTo/видео.html I hope this helps and thanks so much.
Thank you for these wonderful tutorials. I have no knowledge of VBA at but when I followed this step it helps me a lot to understand. However, when I add the click the update button it only put the files in the first row. and the product details are not loading the entire product details.
Hi and thanks make sure you have set the correct sheet when assigning the row. 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 yes you can center any picture within columns or rows, by taking the width of the columns, subtracting the width of the picture, dividing it by two then adding it to the left position (Same principal for rows) I show you how to do that in this video ruclips.net/video/-ZP8jandDcs/видео.html I hope this helps and thanks so much.
What do you think about converting the database into excel table. Will it be easier or harder to mapping? Which one is better? How long you've been in Da Nang.. :)
Hi and thanks so much. I have found that the tables are a bit limiting and since users will not see the tables, i just keep them as ranges. I have been in Da Nang for 18 months and really love it here. Thanks so much.
Hi Randy. Looks awesome! Can't wait. I've noodled with your data mapping techniques through some of your other videos. Looking forward to a dedicated class on the subject :)) Thumbs up!!
Thanks so much Wayne. I think you will really love the Masterclass and looking forward to your feedback. Thank you for your Likes, Shares & Comments. It really helps.
Hi and thanks you can use my data import training to help with that here: ruclips.net/video/Xgu8c40VTDU/видео.html&ab_channel=ExcelForFreelancers I hope this helps and thanks so much.
Hi and thanks so much. I am glad you liked the training. I do have a training on AutoHotkey right here: ruclips.net/video/gkmiNkbvpkE/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Hi Randy, thank you so much for this video and for the simple and easy explanation ,now i understand how to use formulas, i have only one problem i did exactly the same example and same code but i always get error message , will you please help mejust tell me where to send you the screen shot .Thanks in advance
Hi Alaa, 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.
Thank you for the amazing class, it helped me a lot I tried to replicate it and I added in the sub to before deleting the row from the "Prod_List"(sheet2), copying the row and paste it into a "DeletedList"(sheet3). When I run it, it deletes the row correctly, however, it doesn't copy the row into the "DeletedList"(sheet3), however, when the line below is not as an observation the data is pasted on the "DeletedList"(sheet3), but it doesn't delete it from the "ProductList"(sheet2) 'DeletedList.Cells(PRodRow, ProdCol).Value = .Range(DeletedList.Cells(1, ProdCol).Value).Value 'add data to product to deleted tab " Any ideas or suggestions on how to fix it?
Hi Caio, 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 50,000 Members who would love to help you with this. Thanks so much.
@@ExcelForFreelancers thank you very much, I sent a request there now and I'm pending join the community. I must say your videos are amazing, thank you
Hi, I am following this tutorial and I'm wondering if its possible to load on save? I have a cell that uses formula to calculate something, and when I update cells that are needed for calculations, the result is only displayed when I save, and then choose item from list again. Can it be done so it will load the new result on save? EDIT: Nevermind, I just realized I only had to add Product_Load at end of Product_SaveUpdate(). Thank you for this amazing tutorial :)
For sure, you are very welcome, and glad you were able to get that figured out. Well done. Thank you for your Likes, Shares & Comments. It really helps.
Hi and thanks so much William, I get most of my icons from here icons8.com/iconizer/ adioma.com/icons www.iconsdb.com/ I do provide all icons, pictures and other resources every week from my Patreon platform here: www.patreon.com/ExcelForFreelancers I hope this helps and thanks so much.
Thank you for your quick reply. Almost to the second that your reply arrived, I was viewing your excellent video, "How To Create Horizontal Tabs", when you mentioned your icon resource. LOL. I have a lot to learn; however, your most excellent videos will greatly help my learning curve. Thank you again. I have become a Patron supporter.
Thank you so very much, I really appreciate that William and great to have you here. Please make sure to comment after new videos, any feature you want to see added on, so that I can get you that feature for the Patreon update. (New video releases are on Sunday (for Patreon Members) and Tuesday (on RUclips). Then every Monday I do an update on Patreon, so you want to make sure to get in your suggestions as soon as possible so i can complete them for you on Patreon. I hope this helps and thanks so much.
@@ExcelForFreelancersI am still a novice. You have already posted so much information that I need first to understand and absorb before asking any questions or making comments. However, one thing for sure, because of your excellent videos "in real time", I find learning Excel much easier than I did Ancient Near Eastern languages.
Awesome, I am really glad to hear that, thanks so very much. If you are new to vba you may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Randy_Shared
Hi and thanks for your comment. I do not do anything to prepare the pictures or icons, I just browse for them and bring them in Excel. I hope this helps and thanks so much.
HI Peter. feel free to eliminate the code that adds the msgbox and the picture or you can comment those out. If you are new to VBA, you may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Discount I hope this helps and thanks so much.
Hi Joe, thanks so much. If you want a one way load when mapping, meaning you want to save the data to the database, but not load it back into the form (becuase of a formula) then you can exclude it from loading. Assume it is in column 4 in the table you can do something like If ProdCol 4 then .Range(ProdDB.Cells(1,ProdCol).value).value = ProdDB.Cells(ProdRow,ProdCol).value I hope this helps and thanks so much.
I love all your videos. I do have one question though. Why do you do screenshots when mapping instead of just opening a new window? It's not like you're editing the duplicate. It's only for viewing. I am just curious if there is a reason why you don't recommend that. Thank you.
Hi Tressa, there is no particular reason. I use screenshots so often in my work, with a few shortcut keys its super fast for me. Windows would work just as well, As long as you can see the cells and headers, then you can use whatever works best for you. Thank you for your Likes, Shares & Comments. It really helps.
I was wondering if you knew how to connect an Excel userform with an access database, with 2 different kind of userforms, 1 distributed to everyone in the business to input data and can be used by multiple users, 1 for the admin to import data from the database to the userform and modify/edit/delete records
Hi and thanks. I am sure that is possible, however i could not explain it in a RUclips comment. Please post your question 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.
Hi and sorry about that. You would have to check with RUclips as to why you did not get the notification. Please make sure to sign up on my email list so I can email you new training and workbooks every week. www.ExcelForFreelancers.com I hope this helps and thanks so much.
Yes certainly can be on a separate application however it would run a bit slower 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
This is really great training for excel and so easy. I want to be able to fill in a word document from the Data Mapping Masterclass" can this be done as easily?
Excellent video! I have a question on will the data mapping work on merged cells? For instance on the form I have merged several cells and I wanted the input in the merged cell mapped to a field in the table.
Hi Demi, thanks so much for your comment. Yes data mapping will work on merged cells, just use the upper left address of the cell for mapping purposes. 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 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.
Question: I want to store the program on a USB with my pictures in a subfolder of where the program is located. Each time I move to another computer the Drive is changed but the subfolder is the same. Could I reference the pictures by something like “./images/picture001.jpg? Would it work? Thanks.
Hi Robert, thanks so much. You can create a single cell in an Admin sheet that allows you to add the file path of a shared folder. Inside this folder you will add your pictures. Then inside the table, you would add just the picture file name. In VBA you would combine the file folder with the picture name for a full file path. This will work on any computer as long as you use a shared folder such as dropbox. I hope this helps and thanks so much.
Hi and thanks, you can use auto-complete, so you don't need to open the drop-down list. Just start typing and it will auto-complete. I show you how to do this in this training: ruclips.net/video/PVEzHbdHf1Y/видео.html I hope this helps and thanks so much.
Hi and thanks so much. You can download this file using the links in the description under the word DOWNLOAD. This will allow you to change any VBA code. I hope this helps and thanks.
Hi and thanks so much for your comment. I am teaching this entire process inside my Mentorship course here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Hi and thanks crashes in this Worksheet change event will happen when it loops out. For example in this worksheet change event we are looking for a change to E5, then we are loading the record. Check your data mapping and make sure there is No other change happening to cell E5 in your macro. You can stop the code and use F8 to go step by step through the code to see where the error is. I discuss this issue in this video: ruclips.net/video/l2F-k79cSzw/видео.html I hope this helps and thanks so much.
Randy, To avoid having to add the extra references (offset cells) to the Products spreadsheet for handling updates to an individual cell , why dont you replace ProdCol = Target.Offset(1, 0).Value 'Product Column with ProdCol = Prod_List.Range("1:1").Find(What:=Replace(Target.Address, "$", "")).Column This way if you ever re-layout your form, you only need to update the cell reference in the appropriate column in row 1 on Prod_list
Hi David, love that idea!. I will do it, for any forms that use automatic updates. I really appreciate that. Very cool :) I really wish I had thought of this. Thanks so much David.
Hi David, please make sure to watch Part 7 of the School Manager here: ruclips.net/video/LxHWRxMGlbM/видео.html Your solution will be implemented. Thanks again for the great idea.
Hi Randy, This is a great video thank you. I have one question, Can we use a Combo Box instead of the Drop Down List in Product Sheet to Select the Product Name? For ProdCol = 2 To 9 Prod_List.Cells(ProdRow, ProdCol).Value = .Range(Prod_List.Cells(1, ProdCol).Value).Value 'Add Data to Product List Next ProdCol .Range("B4").Value = True 'Product Update to true .Range("E5").Value = .Range("E7").Value 'Product Name in Drop down list .Range("B4").Value = False 'Product Update to False End With End Sub Lets say that we get the "E7" Value from a Combo Box ( which Makes it easier to select an item by typing the name), and it is not only "E7" But "E7:F7 " ( as we need two cells (merged to show longer names). Would it still works and how? Please advise.
Hi and yes you can use either a combo box or you can use auto-complete when you start typing in products they will appear. I have a specific training on that. You can search my channel for "Auto Complete". Merged cells will be fine. 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
Awesome addition to your forms tutorials. Can the ID macro be altered to use a prefix? I use a prefix for my project and materials IDs, (Ex. FabricID = F001, F002, etc). Posting this question on FB too.
Hi Valerie, thanks so much for your comment. Yes it can. If you add another column, then you have one column that contains the internal ID (Numbers only) and an External ID, (numbers with Prefix) this way you can have both, and perhaps only the External ID only visible to users. I hope this helps and thanks so much.
I followed the steps for Product Update/Save on a different workbook. The only difference is instead 7 fields my sheet has 17. VBA Codes are exact and it is adding new records to all fields except the first one ( #2) which remains black. Can someone explain what might be the cause please?
Hi and thanks check your Cell Range in row 1 to make sure it is correct. 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 for your comment. Yes, I have that here: ruclips.net/video/2H59thd0W2k/видео.html Thank you for your Likes, Shares & Comments. It really helps.
Great Training! For some reason When I Click the Save/Update button, it creates a record without the product name. If I hit it a second time, then it creates a new record with the name included. Probably something I am overlooking but it's driving me mad! Can anyone share any insight?
Hi Gerard, 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 30,000 Members who would love to help you with this. Thanks so much.
@@ExcelForFreelancers Thanks for replying. I didn't sleep for 3 days until I found the error! JK. I finally figured it out and discovered that there was a problem in a "Data Validation" field. (Cell reference). I don't know why that caused that specific error but I fixed the syntax and it is now working.
Thanks for the helpful video! Can you clarify how to properly declare the 'With' variable at 58min.? It looks like you just referenced With + the worksheet name. However, when I do this in VBA I receive the error code 'variable not properly defined.'
Hi and thanks. You can use With often with sheets or shapes. Declaring variables is different As far as your exact 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 50,000 Members who would love to help you with this. if you are new to VBA You may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Discount Thanks so much.
Following Exactly the same as in video but when i assign macro to button to add image (42:00 min mark) i get a Run-Time error '438 Object doesn't support the property or method. When i click debug it highlight the .Filters.Add Column Please Help im not sure what to do to bring up the file picker dialog box
HI and thanks very much. This specific code works only on PC's. If you are using a Mac you can find code for that here: www.rondebruin.nl/mac.htm 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 David, thanks so much for the feedback and question. I am glad you got it worked out. We also have a great group of Excel developers that can help you here 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.
Here are some of the fields that I need Grave #,Licensee Name (this is the person who is responsible for the grave and payment), address, Phone # & email, payment, deceased name, date of death, date of burial funeral home, can you suggest how I should set this up. This also needs to link into a master data sheet. I would appreciate your assistance. I started my sheet using your Contact Manager sheet but got lost along the way when you started the VBA. LOL!!!!
Hi and thanks, the application is open and you are welcome to customize this to fit your exact requirements, however I am unable to take on any projects of my own. If you would like some help with this, we have a group with 30,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 and thanks so much. Excel is a great product to do just that. You can check out this Point Of Sale training and template here: ruclips.net/video/SbjBgqzbA3w/видео.html I hope this helps and thanks so much.
Hi Cherry, thanks so much. You may want to include it in the loop when you are loading the product, however when saving it, you don't need to add it in the loop (becuase it is only saved for new products) I hope this helps and thanks so much.
@@ExcelForFreelancers I have another question-In the Data Mapping you use a formula to find the next ProdID..However, I have unique ID for each of my Product. So what would my formula be for NextId or do I leave that out?
Hi Cherry, thanks for your comment. It all depends on your unique ID. How is it formatted? How is it unique? Please post your questions 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.
@@ExcelForFreelancers To answer your question: The unique id is. four numerical digits (1023, 3033, and etc) and it is assigned before I receive it. So for each Product is assigned their own four digits each time and it is not repeated. Thanks for your help
Hi sure you can use the Max function such as Max(IDNumb)+1 This will determine the next ID automatically. I show you how to do this in this series bit.ly/SchoolManagerSeries
Great tutorial. I learned a lot. I wonder how to do the product ID based on a specific number, say the year it was listed + number, ex. 2021_001, 2021_002...? Thank you.
Hi Christian, thanks so much and glad you liked the training. To add a special ID you can use two columns. One column (say column A) only contains an incremental number such as 1, 2, 3 (no letters or symbols). The 2nd column contains the alphanumeric ID in which you can build your id based on the unique ID. Such as ="2021_"&A2. I will add this type of training to an upcoming video within the School Manager series here: bit.ly/SchoolManagerSeries I hope this helps and thanks so much.
HI and thanks you can find the definition of that within the Name Manager as it is a named ranged used within the application using the offset formula to make it dynamic. I hope this helps and thanks so much.
Hi Pierre, If you have tried to download this workbook using the links but had a problem please email angeli@ExcelForFreelancers.com . Please supply the email you used along with the workbook you are requesting. Thanks for your patience.
Hi If you have tried to download this workbook using the links but had a problem please email Shane@ExcelForFreelancers.com . Please supply the email you used along with the workbook you are requesting. Thanks for your patience.
Hi and thanks for your feedback. I do understand that this can be tricky. 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/
Hello there! Thank you for the amazing video. I have followed through the tutorial but I kept getting an error message saying Method 'Range' of object'_worksheet' failed. Can anybody help with this error? Thanks Invlist.Cells(InvRow, InvCol).Value = .Range(Invlist.Cells(1, InvCol).Value).Value 'Save Invoice List Data
Hi and thanks for your comment. When you encounter the bug, and you put your mouse over the highlighted line, you want to make sure that the InvCol is a whole # If it is, lets assume it is 3, then inside the Invoice List sheet, check row 1, column 3 (Column C) You want to make sure in this cell it is a cell address such as D4 or G6 or whatever. If this is left blank of if it does not contain an accurate cell address, you will get this error. 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
🔥Download 350 Of The Worlds Best Templates Here ► rebrand.ly/350Wkbks_YTPinnedCom
This training changed my life. I use data mapping all the time now in everything I build. I needed a way to create apps in Excel that end users wouldn't mess up by overwriting formulas and breaking the app. This was my solution. Absolute gamechanger.
Awesome! I am so happy to hear that and so glad you were able to capitalize on that. I also found a way to do Data Mapping with userforms here:
ruclips.net/video/Oa480YUaJpw/видео.html
I hope this helps and thanks so much.
I’ve never used Excel for this … this will work for my company
Awesome. That is great to hear and thanks so much
Thank you for yet another session that one can learn so much from and apply it in practical terms. One question would be, correct me if I am wrong but the column numbers offset could be set as variables within code whereby removing the need for any being on the main sheet/form.
Hi and thanks. Yes there are a few ways to do data mapping. SInce i have created this video I have found a lot of more streamlined options for data mapping. Feel free to check out some of my newer videos. I hope this helps and thanks so much.
I can't believe it. I was always thinking when I am going to see a data mapping training. I woke up this morning, here what I see. Thank you
Perfect! I am glad I can help. I think you will really enjoy this one. I take my time and move slow, will go step by step. Thank you for your Likes, Shares & Comments. It really helps.
A true wizard, I told you before that all your videos are amazing, easy to understand with all your explanation of what the code is doing and why it's there. Great job. 🙂
Thank you so very much, I really appreciate that Michael.
Randy,
Been watching your tutorials for some time, to me - a revelation! You do know you do a great job, don't you? Actually, you are one of a few who cares for such complex AND complete A-Z solutions.
If you happen to have a spare minute, it'd be great to hear an advice from you: whenever you mention a sample of, say, 3 or 4 items/clients to be picked by NAME, there's no problem with that (like: oranges, apples, carrots - a short list, an un-alfabetic order is not a problem here). Problem arises when a customer/ items list gets much longer thus almost impossible to be picked by name, for such a list is not sorted A-Z. Items are fixed to given rows in excel DB, and in a drop-down menu they are presented in such an order (Oranges, Apples,Carrots and not: Apples, Carrots, Oranges).
Is there a way to make a 'by-name' sorting/picking more user friendly?
Greetings from Poland. A far, far away land.
Chris (much Shrek-alike by the looks...)
Hi Chris thanks so much for the kind words. I am really glad you enjoy the training. I do understand about the longer lists. For sorting purposes when a new item is added, or a name is changed you can run a specific macro that uses and advanced filter (without criteria) that will place the names in a Unique list in another location. You can then sort this list (since its not the original data). You can create a dynamic named range based on this list like "Names_Sorted" and it is this list that you will pick your names from.
If it is a long list you will want to add the auto-complete feature so that when users start typing in the names, they will automatically appear. I go over that feature in this training here: ruclips.net/video/PVEzHbdHf1Y/видео.html
I hope this helps and thanks so much.
Wow! All these topics are covered in almost all your weekly videos but this is the most comprehensive and best thing it cleared all issues. THANKS
Thank you so very much, I really appreciate that. Yes that is correct. Nothing new here but many people wanted me to go slow and start from scratch on data mapping. So I was happy to help with that.
Hi Randy
I am Really Learnt Lots of Excel Projects from your Channel , I am a Big Fan of your Excel Work - My Daily Rotuine is watching your Videos atleast 1 per day , as a Data Analyst & MIS handler - Your Videos are Helping me a Lot - Thank you for your Valuable Information sharing through these videos 🤩
Thank you so very much, I really appreciate that Porvik. I am glad I can help and share
Hello,
I love the way you are teaching this program. I am in need of setting up a grave registry and this is the best form that I have seen in terms of ease.
Great and thanks so much. I am so glad you liked it.
This let me create an awesome spreadsheet at work... blew everyone’s mind! Definitely helped get me noticed... thank you!!!
Thank you so very much, I really appreciate that and glad I could help.
Well Randy this video helped me alot in making my reports and formats more quickly as i always struggeled on data mapping but now i feel more comfortable in doing all this especially reports with pictures and attachments. Thank you so much for This :)
Thanks so much Muhammad, I am sure you will really enjoy it. Looking forward to have you watch the entire training.
Excellent tutorial Randy!
Very usefull for me.
I often have similar clients/products.
In this scenario I would >>> Change the Name and + delete the Product ID = and it makes a copy of all the specifications, but with a new ID and new NAME.
What would it look like, to have a separate Button for that "NEW CLIENT".
For this I tried the following:
End If
If .Range("E7").Value = Not Empty Then 'Remove existing ID
.Delete
End If
If .Range("B6").Value = Empty Then 'New Client-ID
.Range("E7").Value = .Range("B7").Value 'Get Client-ID
ProdRow = DATENBANK.Range("A99999").End(xlUp).Row + 1
DATENBANK.Range("A" & ProdRow).Value = .Range("B7").Value 'Kunden-ID
Unfortunately, it does not create a new product/Client.
It just changes the NAME of the exixting one.
Any ideas?
Thanks for all the good work.
Hi Andreas, 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 50,000 Members who would love to help you with this. Thanks so much.
Can be available simple interest calculator in excel like
Month date principal amount pri.deposite /wridhal interest balance
But interest calculator simple rate but effect of deposited or wridhal calculator in interest
HI and thanks for your comment. I do have a Loan Manager to help with this, just search my channel for it. Thanks again.
Just curious if this would work if your data was in a table instead of creating a named range list since the table is already dynamic.
Hi and thanks for your comment. Using named ranges, or tables would be fine. I used named ranges as a preference so that the formulas are easier to read and to teach. I hope this helps and thanks so much.
from userform find data for specified id and get data of specified id from other sheet Please Explain
HI and thanks you can use the Index/Match formula to pull the user ID from any 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
this is amazing you're a Genius.
One question my computer says
I have a error '438' :
Object doesn't support this property or method.
I don't know what that means.
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 30,000 Members who would love to help you with this. Thanks so much.
This is a great real life exercise. Thanks for putting this together.
For sure, you are very welcome, I do appreciate that.
Good job my friend Austin. Kindly think of preparing a fixed asset management tool with features like depreciation, amortization etc
Thanks so much. I have written down your suggestion and will try to get to it in the near future. Much appreciated. Thank you for your Likes, Shares & Comments. It really helps.
Excellent training!!! Gain better understanding on the concept of data mapping 😀
Thank you so very much, I really appreciate that. I am glad this helps.
Realy liked the data maping Thanks mr Randy i hate long Edit or save code wich was without a loop :
Range = Range until N line
Thanks so much. Yes I really don't like it as well. I hope this has helped and look forward to bringing you many more. Thank you for your Likes, Shares & Comments. It really helps.
The front-end development was interesting. Many opportunities to reduce the "hardcoding" of cells and ranges. Is there a reason why a named table wasn't used for the product list?
Hi Redge, thanks very much. I used named table sometimes however I like to use a single column, with my own names so they are easily recognizable in the formulas. I also don't like some of the restrictions when using tables. I hope this helps and thanks so much.
Randy, Thanks for all the training you publish. Is it possible to map data to different locations based on a dropdown list?
HI Tim, thanks for your comment. Yes there is. I believe I understand what you are asking. I have a Dynamic Userform which maps data dynamically based on selected database sheet here: ruclips.net/video/Oa480YUaJpw/видео.html
Also next Tuesday I have a multi-view data sheet that you might be interested in here: ruclips.net/video/5Bw7TZ0IJTo/видео.html
I hope this helps and thanks so much.
Thank you so much Randy. Truly a blessing. And a great concept idea on how to map.
Thanks so much Al, I always appreciate your comments and feedback over the years.
Thank you for these wonderful tutorials. I have no knowledge of VBA at but when I followed this step it helps me a lot to understand. However, when I add the click the update button it only put the files in the first row. and the product details are not loading the entire product details.
Hi and thanks make sure you have set the correct sheet when assigning the row. 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
This is absolutely amazing advice
Thank you so very much, I really appreciate that Mahabub
Hi Randy, I was wondering if it is possible to use autofit selection to insert the picture the range (J:K),
thanks for your help
HI and yes you can center any picture within columns or rows, by taking the width of the columns, subtracting the width of the picture, dividing it by two then adding it to the left position (Same principal for rows) I show you how to do that in this video ruclips.net/video/-ZP8jandDcs/видео.html
I hope this helps and thanks so much.
That's excellent training Randy, learning lots, thanks
Thank you so very much, I really appreciate that Gerard. I am glad I could help.
What do you think about converting the database into excel table. Will it be easier or harder to mapping? Which one is better? How long you've been in Da Nang.. :)
Hi and thanks so much. I have found that the tables are a bit limiting and since users will not see the tables, i just keep them as ranges. I have been in Da Nang for 18 months and really love it here. Thanks so much.
Hi Randy. Looks awesome! Can't wait. I've noodled with your data mapping techniques through some of your other videos. Looking forward to a dedicated class on the subject :)) Thumbs up!!
Thanks so much Wayne. I think you will really love the Masterclass and looking forward to your feedback. Thank you for your Likes, Shares & Comments. It really helps.
How to image to Excel data spell check and mistake spell auto red mark?
Suppose Name, Address, Zip code, State, Phone number etc.
Hi and thanks you can use a 3rd party software such as Grammarly. I hope this helps and thanks so much.
Hi sir how can i enter the data of thousand of product with picture and details description etc
Hi and thanks you can use my data import training to help with that here: ruclips.net/video/Xgu8c40VTDU/видео.html&ab_channel=ExcelForFreelancers
I hope this helps and thanks so much.
Amazing, I learn a lot
Now i need to install Autohotkey and replicate this training
Thanks so much. Yes Autohotkey is a life-saver. I use it many times every day.
Thank you for your Likes, Shares & Comments. It really helps.
so happy and grateful of this video!
Thanks so much Elvij
@@ExcelForFreelancers i'm only an excel user, now i'm learning so much more including VBA! great!
HI and thanks, that is great.
Thank you sir
For sure, you are very welcome Rahul
Great Lesson as always. Thank you. I hope you will do AutoHotKey class in the future!!
Hi and thanks so much. I am glad you liked the training. I do have a training on AutoHotkey right here: ruclips.net/video/gkmiNkbvpkE/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
Hi Randy, thank you so much for this video and for the simple and easy explanation ,now i understand how to use formulas, i have only one problem i did exactly the same example and same code but i always get error message , will you please help mejust tell me where to send you the screen shot .Thanks in advance
Hi Alaa, 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.
Thank you for the amazing class, it helped me a lot
I tried to replicate it and I added in the sub to before deleting the row from the "Prod_List"(sheet2), copying the row and paste it into a "DeletedList"(sheet3).
When I run it, it deletes the row correctly, however, it doesn't copy the row into the "DeletedList"(sheet3), however, when the line below is not as an observation the data is pasted on the "DeletedList"(sheet3), but it doesn't delete it from the "ProductList"(sheet2)
'DeletedList.Cells(PRodRow, ProdCol).Value = .Range(DeletedList.Cells(1, ProdCol).Value).Value 'add data to product to deleted tab "
Any ideas or suggestions on how to fix it?
Hi Caio, 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 50,000 Members who would love to help you with this. Thanks so much.
@@ExcelForFreelancers thank you very much, I sent a request there now and I'm pending join the community. I must say your videos are amazing, thank you
Thanks so much Caio, i do appreciate that
Hi, I am following this tutorial and I'm wondering if its possible to load on save? I have a cell that uses formula to calculate something, and when I update cells that are needed for calculations, the result is only displayed when I save, and then choose item from list again. Can it be done so it will load the new result on save?
EDIT: Nevermind, I just realized I only had to add Product_Load at end of Product_SaveUpdate(). Thank you for this amazing tutorial :)
For sure, you are very welcome, and glad you were able to get that figured out. Well done. Thank you for your Likes, Shares & Comments. It really helps.
This is an excellent video on mapping data. I am just beginning to learn about Excel. Where do you or others obtain your icons?
Hi and thanks so much William, I get most of my icons from here
icons8.com/iconizer/
adioma.com/icons
www.iconsdb.com/
I do provide all icons, pictures and other resources every week from my Patreon platform here: www.patreon.com/ExcelForFreelancers
I hope this helps and thanks so much.
Thank you for your quick reply. Almost to the second that your reply arrived, I was viewing your excellent video, "How To Create Horizontal Tabs", when you mentioned your icon resource. LOL. I have a lot to learn; however, your most excellent videos will greatly help my learning curve. Thank you again. I have become a Patron supporter.
Thank you so very much, I really appreciate that William and great to have you here. Please make sure to comment after new videos, any feature you want to see added on, so that I can get you that feature for the Patreon update. (New video releases are on Sunday (for Patreon Members) and Tuesday (on RUclips). Then every Monday I do an update on Patreon, so you want to make sure to get in your suggestions as soon as possible so i can complete them for you on Patreon. I hope this helps and thanks so much.
@@ExcelForFreelancersI am still a novice. You have already posted so much information that I need first to understand and absorb before asking any questions or making comments. However, one thing for sure, because of your excellent videos "in real time", I find learning Excel much easier than I did Ancient Near Eastern languages.
Awesome, I am really glad to hear that, thanks so very much. If you are new to vba you may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Randy_Shared
really cool!
Thank you very much. So nice of you Ali
Absolutely amazing
Thank you so very much, I really appreciate that Alif
Hello Randy, what are the best tool for preparing the pictures and icon for excel purposes?
Hi and thanks for your comment. I do not do anything to prepare the pictures or icons, I just browse for them and bring them in Excel. I hope this helps and thanks so much.
Does anyone know how they could load the product data without the message box or change in photo?
HI Peter. feel free to eliminate the code that adds the msgbox and the picture or you can comment those out.
If you are new to VBA, you may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Discount
I hope this helps and thanks so much.
you great teacher
Thank you so very much, I really appreciate that.
Best tutorial ever
Thank you so very much, I really appreciate that.
Thank for this amazing videos Randy, it would helped me a lot.
For sure, you are very welcome Aries. I am happy to help.
if adding another cell mapped to a column for 'Profit Margin' how would you set a formula in that cell that doesn't get deleted when you 'Add Product"
Hi Joe, thanks so much. If you want a one way load when mapping, meaning you want to save the data to the database, but not load it back into the form (becuase of a formula) then you can exclude it from loading. Assume it is in column 4 in the table you can do something like
If ProdCol 4 then .Range(ProdDB.Cells(1,ProdCol).value).value = ProdDB.Cells(ProdRow,ProdCol).value
I hope this helps and thanks so much.
I love all your videos. I do have one question though. Why do you do screenshots when mapping instead of just opening a new window? It's not like you're editing the duplicate. It's only for viewing. I am just curious if there is a reason why you don't recommend that. Thank you.
Hi Tressa, there is no particular reason. I use screenshots so often in my work, with a few shortcut keys its super fast for me. Windows would work just as well, As long as you can see the cells and headers, then you can use whatever works best for you. Thank you for your Likes, Shares & Comments. It really helps.
Thank you so much. If it is a matter of preference over functionality, I can relate. Thanks for taking time to do these as well as respond.
For sure, you are very welcome
I was wondering if you knew how to connect an Excel userform with an access database, with 2 different kind of userforms, 1 distributed to everyone in the business to input data and can be used by multiple users, 1 for the admin to import data from the database to the userform and modify/edit/delete records
Hi and thanks. I am sure that is possible, however i could not explain it in a RUclips comment. Please post your question 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.
@@ExcelForFreelancers already done so, thanks
Hi Randy, Hope you are doing well, waiting for your next training, still we didn't receive the premiere notification.
Hi and sorry about that. You would have to check with RUclips as to why you did not get the notification. Please make sure to sign up on my email list so I can email you new training and workbooks every week. www.ExcelForFreelancers.com
I hope this helps and thanks so much.
This is excellent training, thank you!
Glad you liked it Seng, thanks so much
Hi Randy
Can the database be on separate file (not in the same macro file) ?
Yes certainly can be on a separate application however it would run a bit slower
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
This is really great training for excel and so easy. I want to be able to fill in a word document from the Data Mapping Masterclass" can this be done as easily?
HI and thanks so much. You can add details in a Word document from any Excel table using this training: ruclips.net/video/c4WvVG6zESg/видео.html
Excellent video! I have a question on will the data mapping work on merged cells? For instance on the form I have merged several cells and I wanted the input in the merged cell mapped to a field in the table.
Hi Demi, thanks so much for your comment. Yes data mapping will work on merged cells, just use the upper left address of the cell for mapping purposes. 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 video sir but i had a problem when running into it.it says error 438.thanks for reply
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 40,000 Members who would love to help you with this. Thanks so much.
Good information 👍
Thanks so very much
Question: I want to store the program on a USB with my pictures in a subfolder of where the program is located. Each time I move to another computer the Drive is changed but the subfolder is the same. Could I reference the pictures by something like “./images/picture001.jpg? Would it work? Thanks.
Hi Robert, thanks so much. You can create a single cell in an Admin sheet that allows you to add the file path of a shared folder. Inside this folder you will add your pictures. Then inside the table, you would add just the picture file name.
In VBA you would combine the file folder with the picture name for a full file path. This will work on any computer as long as you use a shared folder such as dropbox. I hope this helps and thanks so much.
E5 Drop down list can’t do like google search? Need to scroll down and choose the product
Hi and thanks, you can use auto-complete, so you don't need to open the drop-down list. Just start typing and it will auto-complete. I show you how to do this in this training: ruclips.net/video/PVEzHbdHf1Y/видео.html
I hope this helps and thanks so much.
Thanks
Good video
Glad you enjoyed and thanks so much.
Nice amazing
Thank you so very much, I really appreciate that Shadhin
Sir I need VBA Code to change existing data using update button.
Hi and thanks so much. You can download this file using the links in the description under the word DOWNLOAD. This will allow you to change any VBA code. I hope this helps and thanks.
It looks awesome!
Thank you so very much, I really appreciate that Foumir.
Thank you so much
For sure, you are very welcome
I never expected excel could go this far
Thanks very much. The possibilities are endless
hello how to license an excel file with a hard drive serial number for a specific system . thanks
Hi and thanks so much for your comment. I am teaching this entire process inside my Mentorship course here: www.excelforfreelancers.com/product/excel-for-freelancers-mentorship-program/
Thanks Randy, you are very good with VBA code!
Thank you so very much, I really appreciate that Nick
I am having a problem when I do the worksheet change event load my Excel crashes.Does anyone know why or how to fix it? 1 hour 24 in video.
Hi and thanks crashes in this Worksheet change event will happen when it loops out. For example in this worksheet change event we are looking for a change to E5, then we are loading the record. Check your data mapping and make sure there is No other change happening to cell E5 in your macro. You can stop the code and use F8 to go step by step through the code to see where the error is. I discuss this issue in this video: ruclips.net/video/l2F-k79cSzw/видео.html
I hope this helps and thanks so much.
As always thank you!
Thank you so much Spencer, I appreciate that.
Good
Thank you so very much, I really appreciate that Khabirul
Randy,
To avoid having to add the extra references (offset cells) to the Products spreadsheet for handling updates to an individual cell , why dont you replace
ProdCol = Target.Offset(1, 0).Value 'Product Column
with
ProdCol = Prod_List.Range("1:1").Find(What:=Replace(Target.Address, "$", "")).Column
This way if you ever re-layout your form, you only need to update the cell reference in the appropriate column in row 1 on Prod_list
Hi David, love that idea!. I will do it, for any forms that use automatic updates. I really appreciate that. Very cool :)
I really wish I had thought of this. Thanks so much David.
Hi David, please make sure to watch Part 7 of the School Manager here: ruclips.net/video/LxHWRxMGlbM/видео.html
Your solution will be implemented. Thanks again for the great idea.
You are the best...i love it work
Thank you so very much, I really appreciate that Gach.
Thank you
For sure, you are very welcome Neville
Hi Randy, This is a great video thank you.
I have one question, Can we use a Combo Box instead of the Drop Down List in Product Sheet to Select the Product Name?
For ProdCol = 2 To 9
Prod_List.Cells(ProdRow, ProdCol).Value = .Range(Prod_List.Cells(1, ProdCol).Value).Value 'Add Data to Product List
Next ProdCol
.Range("B4").Value = True 'Product Update to true
.Range("E5").Value = .Range("E7").Value 'Product Name in Drop down list
.Range("B4").Value = False 'Product Update to False
End With
End Sub
Lets say that we get the "E7" Value from a Combo Box ( which Makes it easier to select an item by typing the name), and it is not only "E7" But "E7:F7 "
( as we need two cells (merged to show longer names). Would it still works and how?
Please advise.
Hi and yes you can use either a combo box or you can use auto-complete when you start typing in products they will appear. I have a specific training on that. You can search my channel for "Auto Complete". Merged cells will be fine. 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
Awesome addition to your forms tutorials. Can the ID macro be altered to use a prefix? I use a prefix for my project and materials IDs, (Ex. FabricID = F001, F002, etc). Posting this question on FB too.
Hi Valerie, thanks so much for your comment. Yes it can. If you add another column, then you have one column that contains the internal ID (Numbers only) and an External ID, (numbers with Prefix) this way you can have both, and perhaps only the External ID only visible to users. I hope this helps and thanks so much.
Nice
Thank you so very much, I really appreciate that Rita
I followed the steps for Product Update/Save on a different workbook. The only difference is instead 7 fields my sheet has 17. VBA Codes are exact and it is adding new records to all fields except the first one ( #2) which remains black. Can someone explain what might be the cause please?
Hi and thanks check your Cell Range in row 1 to make sure it is correct. 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
Can be available product costing workbook ?
HI thanks for your comment. Yes, I have that here: ruclips.net/video/2H59thd0W2k/видео.html
Thank you for your Likes, Shares & Comments. It really helps.
Great Training! For some reason When I Click the Save/Update button, it creates a record without the product name. If I hit it a second time, then it creates a new record with the name included. Probably something I am overlooking but it's driving me mad! Can anyone share any insight?
Hi Gerard, 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 30,000 Members who would love to help you with this. Thanks so much.
@@ExcelForFreelancers Thanks for replying. I didn't sleep for 3 days until I found the error! JK. I finally figured it out and discovered that there was a problem in a "Data Validation" field. (Cell reference). I don't know why that caused that specific error but I fixed the syntax and it is now working.
This stuff should be though in the schools. For some reason it is not. Thank you Randy.
Yes that would be great if it was and thanks so much for your continued support.
Thanks for the helpful video! Can you clarify how to properly declare the 'With' variable at 58min.? It looks like you just referenced With + the worksheet name. However, when I do this in VBA I receive the error code 'variable not properly defined.'
Hi and thanks. You can use With often with sheets or shapes. Declaring variables is different
As far as your exact 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 50,000 Members who would love to help you with this.
if you are new to VBA You may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Discount
Thanks so much.
Following Exactly the same as in video but when i assign macro to button to add image (42:00 min mark) i get a Run-Time error '438 Object doesn't support the property or method. When i click debug it highlight the .Filters.Add Column Please Help im not sure what to do to bring up the file picker dialog box
HI and thanks very much. This specific code works only on PC's. If you are using a Mac you can find code for that here: www.rondebruin.nl/mac.htm
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
Can't wait to see
Thanks so much Tobaye
The Message Box doesn't appear for prod_load when I enter an incorrect product. You never tested it on your tutorial.
I figured out why it doesn't work. Because B5 is empty, Prod_Load won't run in change, therefore the MsgBox will never appear.
Hi David, thanks so much for the feedback and question. I am glad you got it worked out. We also have a great group of Excel developers that can help you here 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.
Here are some of the fields that I need Grave #,Licensee Name (this is the person who is responsible for the grave and payment), address, Phone # & email, payment, deceased name, date of death, date of burial funeral home, can you suggest how I should set this up. This also needs to link into a master data sheet. I would appreciate your assistance. I started my sheet using your Contact Manager sheet but got lost along the way when you started the VBA. LOL!!!!
Hi and thanks, the application is open and you are welcome to customize this to fit your exact requirements, however I am unable to take on any projects of my own. If you would like some help with this, we have a group with 30,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'm not computer operator but know something about MS excel I'm a shopkeeper i want to maintain my shop detail in excel
Hi and thanks so much. Excel is a great product to do just that. You can check out this Point Of Sale training and template here: ruclips.net/video/SbjBgqzbA3w/видео.html
I hope this helps and thanks so much.
If I have a unique ID for my data would I include that on my prodcol when I am looping it?
Hi Cherry, thanks so much. You may want to include it in the loop when you are loading the product, however when saving it, you don't need to add it in the loop (becuase it is only saved for new products)
I hope this helps and thanks so much.
@@ExcelForFreelancers I have another question-In the Data Mapping you use a formula to find the next ProdID..However, I have unique ID for each of my Product. So what would my formula be for NextId or do I leave that out?
Hi Cherry, thanks for your comment. It all depends on your unique ID. How is it formatted? How is it unique? Please post your questions 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.
@@ExcelForFreelancers To answer your question: The unique id is. four numerical digits (1023, 3033, and etc) and it is assigned before I receive it. So for each Product is assigned their own four digits each time and it is not repeated. Thanks for your help
Hi sure you can use the Max function such as Max(IDNumb)+1
This will determine the next ID automatically.
I show you how to do this in this series bit.ly/SchoolManagerSeries
Great tutorial. I learned a lot. I wonder how to do the product ID based on a specific number, say the year it was listed + number, ex. 2021_001, 2021_002...? Thank you.
Hi Christian, thanks so much and glad you liked the training. To add a special ID you can use two columns. One column (say column A) only contains an incremental number such as 1, 2, 3 (no letters or symbols). The 2nd column contains the alphanumeric ID in which you can build your id based on the unique ID. Such as ="2021_"&A2.
I will add this type of training to an upcoming video within the School Manager series here: bit.ly/SchoolManagerSeries
I hope this helps and thanks so much.
@@ExcelForFreelancers Wow! Thank you so much for answering my question. I will be looking forward to your video on this. Thanks again!
You are Great
Thank you so very much, I really appreciate that.
Grt Sir!
Thank you so very much, I really appreciate that Abdus.
you didnt mention what us prod_list
HI and thanks you can find the definition of that within the Name Manager as it is a named ranged used within the application using the offset formula to make it dynamic. I hope this helps and thanks so much.
hi my name is alan i have some data i need a dashboard made so i can make sence of it would you be interested
Hi Alan, thanks so much for your comment. I did get your email and have referred you to a developer. Much appreciated.
Hlo sir please make automobiles Excel software
Hi and thanks so much. I have something like that here: ruclips.net/video/VAYo_8ADfEo/видео.html
tx
For sure, you are very welcome
I hiver m'y email but donner received mail for download the file 😟
Hi Pierre, If you have tried to download this workbook using the links but had a problem please email angeli@ExcelForFreelancers.com . Please supply the email you used along with the workbook you are requesting. Thanks for your patience.
Never got the workbook in my email
Hi If you have tried to download this workbook using the links but had a problem please email Shane@ExcelForFreelancers.com . Please supply the email you used along with the workbook you are requesting. Thanks for your patience.
I always thought “Dim” was Declare in Memory. Learn something new!
Same with me for many years. Thanks for your share. I do appreciate that :)
Hello All
Hi and thanks for your comment. Great to have you here.
Too tough to understand
Hi and thanks for your feedback. I do understand that this can be tricky. 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/
Hello there! Thank you for the amazing video. I have followed through the tutorial but I kept getting an error message saying Method 'Range' of object'_worksheet' failed. Can anybody help with this error? Thanks
Invlist.Cells(InvRow, InvCol).Value = .Range(Invlist.Cells(1, InvCol).Value).Value 'Save Invoice List Data
Hi and thanks for your comment. When you encounter the bug, and you put your mouse over the highlighted line, you want to make sure that the InvCol is a whole #
If it is, lets assume it is 3, then inside the Invoice List sheet, check row 1, column 3 (Column C)
You want to make sure in this cell it is a cell address such as D4 or G6 or whatever. If this is left blank of if it does not contain an accurate cell address, you will get this error.
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