Great video Randy! I have a question, when I add a new supplier and payment invoices etc..I do not get a plus on show invoices. How do I overcome the issue? Thanks for your time.
Hi thanks for your comment and I am glad you like the video. It would be hard to know your specific issue unless we see your code. 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
I know this 4 years old but u have been looking for a "tree" method for an inventory based on location in excel for a long time. Access has too much of a learning curve for some. I cannot believe I found this now.
Hi Mark, 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 very much. You can pull specific Outlook Calendar, which is considered a subfolder, using the details from this site: stackoverflow.com/questions/13713266/excel-create-an-outlook-calendar-event I hope this helps and thanks so much.
HI and thanks so much. You can assign a macro to any button, and inside that macro you can add a code such as Sheet1.Range("A1").select This will go to cell A1 in the sheet. Just make sure you are already on that 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
HI and thanks, make sure you step through the code and also make sure its not looping inside the selection change event. 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 very much, I do appreciate that. This video should show you how to set a background with two colors: ruclips.net/video/YHYsnHBqkfU/видео.html
Hi and thanks very much. I am glad you were able to find the issue. If you need assistance please feel free to visit 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.
Hi and thanks for your comment. You can use a hidden column or helper column for that. 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
I'd class myself as an advanced excel user and daily VBA user, but I have to admit I'm very impressed with this idea. The concept is excellent, and not something I'd have thought of. I'd have tidied it up and created a function (or at least a Sub with parameters) that meant not having to write the code twice (just the ranges, sheet names and line id - e.g. "I" for invoice as parameters passed to the routine). I'd have probably used named ranges instead of actual ranges, "rows.count" instead of the E999999 for the last row, I'd have copied the range from the "sub-tables" including the headers instead of having them hanging around the original sheet and I've definitely only deleted and inserted the range that intersected the table rather than the entire row. However, all this fanciness is irrelevant, as I'd never of thought of your idea in the first place! Good work sir.
Hi! Randy. Excelent your file about Drill Down Data but there are some mistakes. If you filter de first customer John Abners, invoices numbers are right but invoices numbers in payments are wrong. Invoice number 2488 for John Abners does not exist in payments. This number belongs to another customers, like Barry Yeager and Jimmy Dean but it is shown as John's payment. Thanks!
Hi and thanks make sure to check your filter range and formulas to make sure the data. Please also share your screenshots and code 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.
There are a few ways to group and un-group in Excel however they hide rows, which can be an issue. This is a new way which is more dynamic and can use additional conditional formatting and dynamic and does not require the visible grouping on the left side. Thanks so much for your feedback.
HI Randy, your videos have helped me so much!! Please can you show me how to add another line to track a serial numbers to a specific client related to a specific invoice🙏🏻🙏🏻🙏🏻
Hi and thanks for your comment Juliet. Modifications require updates to the code, formulas, sheet, conditional formats and possible named ranges, so it would not be something I could show you. If you would like help in this, I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com Thanks so much.
Hi, it is really great! I was playing around and trying a lot of things. One was to change the payment ammount, to correct it. I have to do that not only in Payments (as I expected), but also in Invoices. That's asking for problems. How can you make sure your data is integer? KR Willem
Hi WIllem, Thanks very much for your comments. The way that i Protect my source data is that in most problems it is hidden so users cannot edit it. I provide multiple ways to update payments, however it always updates the single database that I have. I usually hide database sheets from users and have them make updates from other screens. You can see a sample of how this would work in one of my product tutorials here (this is on another channel and is a tutorial, not a training) ruclips.net/video/1a0grkUXy8A/видео.html
This is verry cool. But ı have a question. if you click on the plus, there is opening just 3 invoices or just 3 payments rows. What will go on if the invoices ore the payments more than 3? Sorry for my bad english, ı hope you understand me and thank you very much.
Hi thanks so much Tolga, you can have as many invoices or payments as you want since the filtered # is first counted, and then that # of rows are inserted below. So the # of rows are dynamic based on the count of invoices or payments. I hope this helps and thanks so much.
This type of videos shows what separate knower vs Innovator. It is IDEAS which make thingss amazing, usable and problem solution centric. Knowing the tool is not enough it is IDEAS application which will provide solution and make things better , interesting and efficient
Thank's so much, that is what I focus on because I really love Excel. Please check out all of my videos, I think you will see some extremely creative uses of Excel, including this weeks multi-part File Manager here: ruclips.net/video/GHVhfgN7gig/видео.html
Thanks for a great video. One question I have is in regard to the conditional formatting. When I setup a rule and specify a certain range, it subsequently changes as I expand rows in my spread sheet. Is there a way of making the 'Applies To' range permanent?
Your conditional formatting will usually expand with the cells, however what i did was just for those rows that were expanded i used the Clear Ex; Sheet1.Range("D4:M10").clear. This clears ALL formatting, then you can copy and paste new formatting (that you have stored in another location) within the inserted cells. This allows you to use two different Conditional formats that will not cause problems for each other. 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 with code screenshots here:bit.ly/groupexcel
Hi Great Video. Have a similar question which seems other people suffered the same problem on previous comments. How to add new costumers as my excel knowledge below standards ?
Hi thanks so much. You can update the database a number of different ways including adding a sheet-based form or userform. I have a video on how to create a customer information application right here: ruclips.net/video/Ry3PYTMbskg/видео.html
@@ExcelForFreelancers , I watched entire video what you suggested however your response not relevant to my question. After 15th costumer plus (+) sign does now work.
Can you please upload screenshots of both your worksheets, and code in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 25,000 Members who would love to help you with this. Thanks so much.
You need to go into the conditional format and expand the range for the customer information. *Ensure that the cell in the hidden column for the conditional format relating to the customer information has the 'C' in it.* Also, go into the VBA code and amend the code. I would advise you to watch the video, as he builds the code, because he explained the logic to each code there. I build a list with 300+ customers and it worked for me. My colleagues and clients love it.
I was thinking that because we require a unique key for the invoice & customer numbers we can use conditional formatting to check if there are identical keys... Can you think of a way to ascertain in VBA that there is / are cells with the conditional format, to either message the user, or to disallow the key?
You can use the Match formula (in cell formula or in vba) to test if there is a Match or not. If there is, then its not unique, if there is not, then it is unique. We have a group of Excel experts on Facebook who can help you with your questions. please feel free to join us here: bit.ly/groupexcel
It is a nice system. I wish it was able to have the drill down data tab to be the main tab, where you can add the data. and then it is shipped to a database tab. Add a new invoice in drill down, give it a number/name, set the date amount etc. cause then I could convert it to something that would be useful for our teachers to keep track of student progress in one visible list. In 1 module there are like 7 courses, but we want to see if the student is done with the whole module, If the student is not done then the ability to drill down to easily see where he is stuck. The teachers can use the system to fill in the points/pass/fails.
Hi and thanks i am glad you enjoyed the training .If you would like to have a customized version please write up a scope and post a PAID JOB 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.
Great videos as usual. I’m trying to accomplish this without deleting rows or hiding rows…because I want to have input fields to the right side, deleting or hiding rows won’t work. Essentially I would need to rebuild/load the data every time a row is expanded or collapsed. Like a Treeview control works. I have watched all your videos and do not recall seeing you do this in the manner I would like it to work. Any suggestions?
Hi Brent, thanks for your comment. To start you want to make sure to separate the sheet in which you are displaying or hiding rows and you original data. Secondly you can use advanced filters to hide/show data based on your requirements. You can rebuild it each time the macro is run. I hope this helps and thanks so much.
Great Video... For performance purposes, wouldn't this work better using arrays and dictionaries to read and write in code, then release the data? Instead of relying on Excel Filters and functions?
Certainly it is possible, however for training purposes, advanced filters provide a more visual way to learn as we can follow it, step by step. Thanks for the following and feedback.
Nice job. I used it right away in my Excel data. I included another line when '+' or '-' is clicked. When the '+' is clicked, I can't click the '-' to compress, because there is no SelectionChange. So after expanding and compressing I offset one column in the same row with (ActiveCell.Offset(0,-1).Select
HI Bert, thanks so much and I am glad you like the training. The best way to handle that issue is after both macros run + or -, just select another cell in vba before the macro ends like "Sheet1.Range("A1").select", but i also like your idea as well using offset to select. Both options solve that problem (Another option, instead of Selection change would be to use it on Before Double Click) this would work when a user double clicks a cell which is also a nice alternative and fixes the selection change issue.
I 'm trying to use It in different way with hyperlink but the code (.Range("I" & ActRow + 2 & ":K" & ActRow + 1 + FILE).Value = Sheet3.Range("AD4:AF" & LastFiltRow).Value) needs to be changed to copy the hyperlink but I can't fix it
Hello Mohamed, thanks for your comment. It's hard to know the exact cause of your issue by looking at you can try this: .Range("I" & ActRow + 2 & ":K" & ActRow + 1 + FILE).Value = Sheet3.Range("AD4:AF" & LastFiltRow).Value Perhaps your variables have an incorrect value. 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
Yes sure you can use Autofit on any range. just place the function after you fill the range with your drill down data. Such as Worksheets("Sheet1").Columns("A:I").AutoFit I hope this helps and thanks so much.
Can you help me understand how can I add few columns instead of range? For example sheet3.range(“BR6:BV” & lastrow).value in this I am asking it to select range from BR6 to BV lastrow but I am looking that few columns like BR6 to BT, BV and few more
Thanks for this valuable tool ! After drill down, we are unable to click the same cell (without moving to any other cell) and drill up. Is it possible ?
HI Emmanuel, yes it is possible, if you select another cell inside the code. In fact, I have another video coming out in just 14 hours that will show you how to drill up & drill down and select the same cell over and over again here: ruclips.net/video/iQirWGGck9Q/видео.html
I am wondering if it is possible to remove the rows without deleting entire row? I’d like the drill down bit be on the left side of the spreadsheet and on the right i have some data
Hi Timur, thanks for your comment. You don't necessarily need to delete the rows however you will want to make sure to clear out all of the data, and formats, (especially the conditional formatting which will get duplicated if not cleared) So if you want to maintain the data on the right you can do that, just clear all values and formats below and add the data again. (of course your source data must be kept elsewhere) 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 message. I have assigned a macro to a button. You can assign any macro to any shape in Excel. We are happy to help you with questions when you join our Excel For Freelancers Facebook Page here: bit.ly/groupexcel
Thanks so much Ben. I am glad you enjoy the training. The Conditional Formatting can make it difficult. The trick is to completely clear the formatting before pasting in your sub-category information, otherwise it will start duplicating the CF. I am so glad you enjoyed this older training.
This is great! very useful! Is there a way that I can chage the data inside the drilled down table and it will also change in the invoices/payments table?
HI yes sure you can as long as you also copy over the original database row. This way any changes that are made, you can use that row to also update the original data. I hope this helps and thanks so much.
Hello Sir Very nice and useful video. and as usual very clearly explained. One little question/suggestion please: For customers where there is no invoice or payments, can there be a different sign other than "+" ? Thank you very much..
Hi yes for sure, that is a nice idea. You could use any character you want. Once you run the advanced filter, and you find that there are no records that match, you could then replace the + to any character you like. Thanks for the nice idea. 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 with code screenshots here:bit.ly/groupexcel
Hi, Great work. I love all your uploads learned a lot. It may help me. I have a doubt! Shall I drill down even large amount of data? For example. State wise, city wise, village wise_ sales, it would include many products.. Shall I? Thanks.
I think you could drill down 50 times and it would not be a problem as long as your databases were proper. Meaning a separate database for each table and there must be a Linked Column.... meaning Each City must have a State. Each State much have a Country. Each Product Must have a city. So there must be a linking item in each, then it would work great.
LOL. I have solved that "issue" in all of my later videos. Of "OK" > 3 then Range("OK").Delete. If i catch myself, I edit them out now. Check out any of the newer videos, I have kept the "OK's" down to a minimum. :)
Thank you for your videos and body of work. I have noticed that the cell focus needs to change in order for this VBA program to work. Is there a way for the focus to remain and detect a mouse click event to bring the opposite state?
Hi you can use the event Double Click instead of Single click, This way even if the focus remains on the cell, double click would still work. 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
Question - the invoice/payments drill down - if the invoices/payments are not in numerical order is there a way to sort the order when the drill down is revealed?
Hi Sonja, thanks for your comment. Yes sure, you can sort the results of the data before bringing them into the current sheet. They can be sorted by any column in the data. Thanks so much for the great question.
Hi you could do this for sure, however when running the Sort Macro, it should start by Collapsing all expanded sections first. That would help maintain the table integrity.
Hi Abhisek, thanks for your comment. You can download the FREE workbook and follow along here: Using Your Email: bit.ly/Drill_Down_Data_Wb With Facebook Messenger: bit.ly/DrillDownData_FbDl
Hi sorry about that. Can you please email Angeli@ExcelForFreelancers.com, and notate that you are looking for the Drill Down Data workbook, so she can check on why you did not get that? (Also please check your spam folder as well) Thank you.
Hi thanks the values in the sample application were not calculated. The training was focused on how to implement a drill down feature in Excel. Future videos may have accurate calculations if they pertain to the core training. Thanks very much for your comment.
Do you think that it is possible to make this this drill-down feature compatible with filtering? When filtering a column and trying to collapse an expanded field, I get the following error: "This won't work because it would move cells in a table on your worksheet". I assume the problem is that, after applying a filter, certain rows in your worksheet are hidden, screwing with the Hide Sub (.Range(lActRow + 1 & ":" & lLastOrdRow).EntireRow.Delete). Your worksheets has the same issues when filtering.
The idea is to put all original data on other sheets. Once you have that, you can do nearly Anything, with the data on your current sheet, including drill down, sorting and filtering. Make sure you are not using Excel's "Table" which has lots of limitations. I never use tables because of these limitations (My ranges just 'look like' tables because of the alternating row colors. When you have all data in other sheets, there is no need to hide any rows, simple write the code to clear and re-write the data based on your filters. 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 with code screenshots here:bit.ly/groupexcel
@@ExcelForFreelancers Thanks for sharing this, but if in Our main data we need to do filter then the tool is copying the data on incorrect cells. How can we resolve that? Can you please provide a solution for this
Thank you so much for this. It helped me present lots of information on a single sheet. But I had a problem when I turned on the filter on the table. The drill down information on level 2 and level 3 spilled over and replaced the level 1 information. Any advise on how to resolve this problem, please?
Hi Tony, thanks so much for your comment. You would not want to run a filter on this table. This table should not include your original data. To filter, use an advanced filter on your original data sheet, then bring over only the filtered data into this drill down 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
Amazing video . I tried to replicate with greater number of invoice lines (>100). It works well however for the last couple of lines the drill down does not happen. I have used the same codes. Is there a limitation on number of lines ?
Hi there should be no limitations at all. There is probably something in your code that is limiting that number. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
I think I’m trying to be too clever in combining this training (without needing the filtering) with the Shared Workbook training (without having a remote file...yet) and then customising. 🤯 It’s going to be line by line testing. 🙃
I am sure it will be great. Just make sure to keep your original data separate when doing drill down. It gets risky and complicated if you are working with your original data table. I'm sure you will do great.
Excel For Freelancers Yes, all data is kept in a set of data tables that I will one day move to a shared directory. And although it seems a little obvious, line by line testing or setting a stop point within the VBA, it improves understanding as you can see what each line does. Solved a couple of issues really quickly yesterday doing that. 👍🏻
Hi for sure. I will make a basic free video and a very comprehensive course on Invoices. I have that on my list so make sure you follow my facebook closely here: facebook.com/ExcelForFreelancers/
Hi Harun, 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.
Loved the Drop-Down video. I did find that if you expand an invoice or payment by selecting the + and try to just shrink the area without moving to another cell first (select the - cell) the shrink macro does not work
HI I am so glad you liked it. You can fix this small issue after the expand, inside that macro just select any other cell, then you can click it again, for example at the end of the macro you can add Sheet1.Range("A1").select (or any other cell) and then it will work by clicking it again (also instead of single click you can use double click as well. Thanks for your comment.
I noticed you have to click off of a cell to re click the cell again. kinda annoying is there a fix to this? Can I do it with a shape? Thanks for the Video I learned a lot!
Hi Deathium, there are two ways to fix this, You can either use Double Click (instead of single click) to open or at the end of the macro on single click just automatically select any other cell in VBA, which will force the selection to another cell such as .Range("A1").select 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 Yeah So that what I did I moved the select after I click the cell. "ActiveCell.Offset(0, -1).Select" So I would love to have buttons or shapes. is there a way You could click a button and have the macro find what cell the shape is on? that would be awesome. Thanks Again!
I generally like the ability to click a cell and have a button appear on a row or column, This way you can use the same button/shape to appear exactly where you want it. You can see samples of this in my video here: ruclips.net/video/xOWJIlG8XEE/видео.html
Hi you would right click any cell, go to Format Cells, click the Fill Tab, click Fill Effects, then just choose the two colors you want and then the shading styles below. I hope this helps.
Hi thanks I have this video which I go over slowly. If you download the workbook, you can follow the code and hopefully that will help you. 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 you can update the currency symbol in the cell format. 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 Sisco, 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.
Hello, thanks a lot for this and other videos. Wonder if it is possible to drill down every position at once ? i Tried to do loop that select every "+" but after first its stopping. Will be grateful for any tips
Hi and thanks. If you are looking to an Expand All group, the easiest way would be to run a loop from the last row to the first row (reverse order) and select each cell in which the plus is in. This will automatically expand each group. (Make sure you go in reverse order) 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
How are you expanding without using hidden rows? Your explanation in the video doesn't clearly say - you only say what it is not, but not what it is? I guess it has to do with advanced filtering to expand?
THe table that you see, is not the original data, which is stored on separate sheets. When i want to expand the rows i simply copy down the data the # of rows that have returned in my advanced filter, clear out those rows that are duplicate and insert the data from my advanced filter. I have a course coming out in a few months that will explain in more detail however the concept will be the same, so you may have to watch this a few times. Also feel free to download the free workbook where you can see exactly how I did this.
Please make HR dashboard..where I can add employees with there pics...leaves n salary making, along with salary slip printing with there code or names..and also tracking sales....state wise.. product wise...help doing making this
That's a great idea. This sounds like it would be perfect for a comprehensive paid course that would include a master workbook and perhaps 8 - 15 hours course, since there is a lot i could include including Time Click, Employee Info, Scheduling, Payroll Export, Time & Earnings history, etc.
Eric Wang That is not a problem, you can just add one line of code to select a different cell after the code is run such as Range("A1").Select Then you can click on the same cell again and again without having to select another cell first. I hope this helps
@@ExcelForFreelancersI need your help. I am responsible for 3 warehouses. My work is to get the balance for the serviceable and damage between 10 types of ULD's (units loading device to load cargo or passenger baggage ) for many airlines at the airport, follow up the ULD's in and out process and handling with each flight and so With the repair workshops and scrap, I tried on many Excel files but I was not satisfied with it and I will upload to you the last file
Hi Mohamed. Thank you for your comment. In our Excel For Freelancers Facebook Group here: bit.ly/groupexcel we have 15,000 Excel experts who can help you with this. Just create a post and mark it as PAID JOB, so that a qualified Excel Developer can contact you with a fair price to complete this project for you. Thanks so much
The Runtime error rate with this advanced filter is huge :(. I got it working then i added more information to the invoice, adjustet the range for the filter, adjusted the header to insert. Code wise it should work but insted it throws me an runtime error. Somehow the filter is not working and it's this line that makes trouble: Tabelle4.Range("A3:L" & LastInvRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Tabelle4.Range("AA1:AL2"), CopyToRange:=Tabelle4.Range("AA3:AL3"), Unique:=True
Most run time errors on advanced filter are because of incorrect headers. The headers of all 3 (original table, advanced filter, and results should all match exactly the same. Errors happen when the column headers are different, even just 1 character difference will cause an error. I hope this helps. Please feel free to join and post the issue in our fb group with screen shots if you like, you can join here bit.ly/groupexcel
Hi there are two ways to download the workbook. Using your email or with Facebook Messenger. Using Your Email: bit.ly/Drill_Down_Data_Wb With Facebook Messenger: bit.ly/DrillDownData_FbDl
Hi I have a unique data of 2000 is this works, I work in developers I generate electricity bill for 2000 flats . So I need to know how to track payment for rack months
Sure you can do that, by making adjustments to the formulas and code. If you have a specific question perhaps you can join our Facebook group as there are thousands of Excel experts to help you right in our group: bit.ly/groupexcel
Hi...can u make it four level drill down....1st is region...then location...then branch...the sales relationship manager name..example...when click region like Delhi...then come to Delhi's location...then will come to branch name then will come sales relationship manager name then will his total month on month business....if u made it...it will help us. Thanku...
![How To Create Dynamic & Dependent Drop Down Lists In Excel [Free Download + Training]](http://i.ytimg.com/vi/ToXa_3Gk0lc/mqdefault.jpg)
📅Excel Calendar Pro: Your Solution to Stress-Free Scheduling 👉 rebrand.ly/CalendarPro_PinnedComm
Great coding. I use Google sheets for my personal needs, even though the Excel is much more powerful with great features.
For sure, you are very welcome. Thank you for your Likes, Shares & Comments. It really helps.
Great video Randy! I have a question, when I add a new supplier and payment invoices etc..I do not get a plus on show invoices. How do I overcome the issue?
Thanks for your time.
Hi thanks for your comment and I am glad you like the video. It would be hard to know your specific issue unless we see your code. 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
Great video and file! congrats!
Thank you so very much, I really appreciate that
You are truly Legend!!!!
All your videos are so helpful. Really appreciate your effort of knowledge sharing.
Thank's so much, your comment was so nice. I do appreciate that and happy to create these videos for you.
I know this 4 years old but u have been looking for a "tree" method for an inventory based on location in excel for a long time.
Access has too much of a learning curve for some.
I cannot believe I found this now.
Oh great I am so happy you liked it. You may also enjoy this Bill Of Materials training here: ruclips.net/video/dfMchM3sTm4/видео.html
You are a master! I will apply this to organize my equipment in lines at my factory
Thanks so much. I think it's a great idea to apply this to equipment.
Great Training, but The Workbook can't seem to download through all the channels provided.
Hi Mark, 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.
Hello,
I'm looking to be able to choose an Outlook calendar other than the default one to insert appointments from Excel
Thanks
Hi and thanks very much. You can pull specific Outlook Calendar, which is considered a subfolder, using the details from this site: stackoverflow.com/questions/13713266/excel-create-an-outlook-calendar-event
I hope this helps and thanks so much.
PLZ INFORM HOW TO CHANGE EXCEL SETTING FOR GO TO LINKED CELL DESTINATION BY USING MOUSE SINGLE CLICK INSTEAD OF USING KEY BOARD SHORT CUT CTRL+[ .
HI and thanks so much. You can assign a macro to any button, and inside that macro you can add a code such as Sheet1.Range("A1").select
This will go to cell A1 in the sheet. Just make sure you are already on that 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
How can I install the plus sign in the payment rows, because it disappears as soon as I return to the main row
HI and thanks, make sure you step through the code and also make sure its not looping inside the selection change event.
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
Great Tutorial, very helpful, thank you. Really appreciate the comments on each line of VBA
Thanks very much, I appreciate your comment. :)
You're really great on Excel that I do need to help with my point. Thank for sharing this video. Can you help me to set background with two color?
Hi thanks very much, I do appreciate that. This video should show you how to set a background with two colors: ruclips.net/video/YHYsnHBqkfU/видео.html
Thank you for the video. How can I activate the macro so when I click + or - it will activate to run the macro?
Please disregard. I have found my error. Thanks
Hi and thanks very much. I am glad you were able to find the issue. If you need assistance please feel free to visit 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.
I'm a noob here, thanks for the great channel. Question: How do you create the drop down column with + symbols, and hidden un-numbered rows?
Hi and thanks for your comment. You can use a hidden column or helper column for that. 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
Sir great imagination, your videos ultimate sir
Thank you so very much, I really appreciate that and happy to help and share
I'd class myself as an advanced excel user and daily VBA user, but I have to admit I'm very impressed with this idea. The concept is excellent, and not something I'd have thought of. I'd have tidied it up and created a function (or at least a Sub with parameters) that meant not having to write the code twice (just the ranges, sheet names and line id - e.g. "I" for invoice as parameters passed to the routine). I'd have probably used named ranges instead of actual ranges, "rows.count" instead of the E999999 for the last row, I'd have copied the range from the "sub-tables" including the headers instead of having them hanging around the original sheet and I've definitely only deleted and inserted the range that intersected the table rather than the entire row. However, all this fanciness is irrelevant, as I'd never of thought of your idea in the first place! Good work sir.
Thanks very much for your great suggestions. I am glad you liked the video and look forward to making many more.
تدريبا رائع ومشوق
.🌟🌟🌟🌟🌟🌟🌟
Thank you so very much, I really appreciate that
Hi! Randy. Excelent your file about Drill Down Data but there are some mistakes. If you filter de first customer John Abners, invoices numbers are right but invoices numbers in payments are wrong. Invoice number 2488 for John Abners does not exist in payments. This number belongs to another customers, like Barry Yeager and Jimmy Dean but it is shown as John's payment. Thanks!
Hi and thanks make sure to check your filter range and formulas to make sure the data. Please also share your screenshots and code 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.
Can we not do this with the inbulit subtotal option (group).. It's available under data atab
There are a few ways to group and un-group in Excel however they hide rows, which can be an issue. This is a new way which is more dynamic and can use additional conditional formatting and dynamic and does not require the visible grouping on the left side. Thanks so much for your feedback.
HI Randy, your videos have helped me so much!! Please can you show me how to add another line to track a serial numbers to a specific client related to a specific invoice🙏🏻🙏🏻🙏🏻
Hi and thanks for your comment Juliet. Modifications require updates to the code, formulas, sheet, conditional formats and possible named ranges, so it would not be something I could show you. If you would like help in this, I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com
Thanks so much.
Hi, it is really great!
I was playing around and trying a lot of things.
One was to change the payment ammount, to correct it.
I have to do that not only in Payments (as I expected), but also in Invoices.
That's asking for problems.
How can you make sure your data is integer?
KR
Willem
Hi WIllem, Thanks very much for your comments. The way that i Protect my source data is that in most problems it is hidden so users cannot edit it. I provide multiple ways to update payments, however it always updates the single database that I have. I usually hide database sheets from users and have them make updates from other screens. You can see a sample of how this would work in one of my product tutorials here (this is on another channel and is a tutorial, not a training) ruclips.net/video/1a0grkUXy8A/видео.html
This is verry cool. But ı have a question. if you click on the plus, there is opening just 3 invoices or just 3 payments rows. What will go on if the invoices ore the payments more than 3? Sorry for my bad english, ı hope you understand me and thank you very much.
Hi thanks so much Tolga, you can have as many invoices or payments as you want since the filtered # is first counted, and then that # of rows are inserted below. So the # of rows are dynamic based on the count of invoices or payments. I hope this helps and thanks so much.
This type of videos shows what separate knower vs Innovator. It is IDEAS which make thingss amazing, usable and problem solution centric. Knowing the tool is not enough it is IDEAS application which will provide solution and make things better , interesting and efficient
Thank's so much, that is what I focus on because I really love Excel. Please check out all of my videos, I think you will see some extremely creative uses of Excel, including this weeks multi-part File Manager here: ruclips.net/video/GHVhfgN7gig/видео.html
Thanks for a great video. One question I have is in regard to the conditional formatting. When I setup a rule and specify a certain range, it subsequently changes as I expand rows in my spread sheet. Is there a way of making the 'Applies To' range permanent?
Your conditional formatting will usually expand with the cells, however what i did was just for those rows that were expanded i used the Clear Ex; Sheet1.Range("D4:M10").clear.
This clears ALL formatting, then you can copy and paste new formatting (that you have stored in another location) within the inserted cells. This allows you to use two different Conditional formats that will not cause problems for each other.
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 with code screenshots here:bit.ly/groupexcel
Hi Great Video. Have a similar question which seems other people suffered the same problem on previous comments. How to add new costumers as my excel knowledge below standards ?
Hi thanks so much. You can update the database a number of different ways including adding a sheet-based form or userform. I have a video on how to create a customer information application right here: ruclips.net/video/Ry3PYTMbskg/видео.html
@@ExcelForFreelancers , I watched entire video what you suggested however your response not relevant to my question. After 15th costumer plus (+) sign does now work.
Can you please upload screenshots of both your worksheets, and code in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 25,000 Members who would love to help you with this. Thanks so much.
You need to go into the conditional format and expand the range for the customer information. *Ensure that the cell in the hidden column for the conditional format relating to the customer information has the 'C' in it.*
Also, go into the VBA code and amend the code. I would advise you to watch the video, as he builds the code, because he explained the logic to each code there.
I build a list with 300+ customers and it worked for me. My colleagues and clients love it.
I am really enjoying your series of videos. Gives me really great ideas and snippets of code for a lazy coder ;0)
Thanks very much Les. Glad I can help.
I was thinking that because we require a unique key for the invoice & customer numbers we can use conditional formatting to check if there are identical keys... Can you think of a way to ascertain in VBA that there is / are cells with the conditional format, to either message the user, or to disallow the key?
You can use the Match formula (in cell formula or in vba) to test if there is a Match or not. If there is, then its not unique, if there is not, then it is unique. We have a group of Excel experts on Facebook who can help you with your questions. please feel free to join us here: bit.ly/groupexcel
It is a nice system.
I wish it was able to have the drill down data tab to be the main tab, where you can add the data.
and then it is shipped to a database tab.
Add a new invoice in drill down, give it a number/name,
set the date amount etc.
cause then I could convert it to something that would be useful for our teachers to keep track of student progress in one visible list.
In 1 module there are like 7 courses, but we want to see if the student is done with the whole module,
If the student is not done then the ability to drill down to easily see where he is stuck.
The teachers can use the system to fill in the points/pass/fails.
Hi and thanks i am glad you enjoyed the training .If you would like to have a customized version please write up a scope and post a PAID JOB 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.
Great videos as usual.
I’m trying to accomplish this without deleting rows or hiding rows…because I want to have input fields to the right side, deleting or hiding rows won’t work.
Essentially I would need to rebuild/load the data every time a row is expanded or collapsed. Like a Treeview control works.
I have watched all your videos and do not recall seeing you do this in the manner I would like it to work.
Any suggestions?
Hi Brent, thanks for your comment. To start you want to make sure to separate the sheet in which you are displaying or hiding rows and you original data. Secondly you can use advanced filters to hide/show data based on your requirements. You can rebuild it each time the macro is run. I hope this helps and thanks so much.
Great Video... For performance purposes, wouldn't this work better using arrays and dictionaries to read and write in code, then release the data? Instead of relying on Excel Filters and functions?
Certainly it is possible, however for training purposes, advanced filters provide a more visual way to learn as we can follow it, step by step. Thanks for the following and feedback.
Nice job. I used it right away in my Excel data. I included another line when '+' or '-' is clicked. When the '+' is clicked, I can't click the '-' to compress, because there is no SelectionChange. So after expanding and compressing I offset one column in the same row with (ActiveCell.Offset(0,-1).Select
HI Bert, thanks so much and I am glad you like the training. The best way to handle that issue is after both macros run + or -, just select another cell in vba before the macro ends like "Sheet1.Range("A1").select", but i also like your idea as well using offset to select. Both options solve that problem (Another option, instead of Selection change would be to use it on Before Double Click) this would work when a user double clicks a cell which is also a nice alternative and fixes the selection change issue.
Moving th Macro to a Double Click function and including Cancel = true is a cleaner method
I 'm trying to use It in different way with hyperlink
but the code (.Range("I" & ActRow + 2 & ":K" & ActRow + 1 + FILE).Value = Sheet3.Range("AD4:AF" & LastFiltRow).Value) needs to be changed to copy the hyperlink but I can't fix it
Hello Mohamed, thanks for your comment. It's hard to know the exact cause of your issue by looking at you can try this:
.Range("I" & ActRow + 2 & ":K" & ActRow + 1 + FILE).Value = Sheet3.Range("AD4:AF" & LastFiltRow).Value
Perhaps your variables have an incorrect value. 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
Does this works in google sheets?
Hi and thanks. This only works for Excel. Thank you for your Likes, Shares & Comments. It really helps.
Is there any way to use autofit function on drill down rows? To look it better
Yes sure you can use Autofit on any range. just place the function after you fill the range with your drill down data. Such as
Worksheets("Sheet1").Columns("A:I").AutoFit
I hope this helps and thanks so much.
Can you help me understand how can I add few columns instead of range? For example sheet3.range(“BR6:BV” & lastrow).value in this I am asking it to select range from BR6 to BV lastrow but I am looking that few columns like BR6 to BT, BV and few more
@@ExcelForFreelancers this is impacting hidden rows. How can we fix that?
This is amazing - thank you for sharing so much info!
For sure, you are very welcome Shelley
Thanks for this valuable tool ! After drill down, we are unable to click the same cell (without moving to any other cell) and drill up. Is it possible ?
HI Emmanuel, yes it is possible, if you select another cell inside the code. In fact, I have another video coming out in just 14 hours that will show you how to drill up & drill down and select the same cell over and over again here: ruclips.net/video/iQirWGGck9Q/видео.html
I am wondering if it is possible to remove the rows without deleting entire row? I’d like the drill down bit be on the left side of the spreadsheet and on the right i have some data
Hi Timur, thanks for your comment. You don't necessarily need to delete the rows however you will want to make sure to clear out all of the data, and formats, (especially the conditional formatting which will get duplicated if not cleared)
So if you want to maintain the data on the right you can do that, just clear all values and formats below and add the data again. (of course your source data must be kept elsewhere)
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
I need this type of excel sheet but without using VBA may that is possible kindly give me an advice .Thank you sir
HI Govinda, I just use VBA for this solution. Thanks for your comment.
How to add new coustemer ?
HI and thanks you can add new customers in the Customer List sheet. I hope this helps and thanks so much.
I'm a 'NOVICE' and am having trouble with this fabulous idea. What triggers the Macro?
I have to go into the 'Developer' and click run.
Thanks!
Hi thanks for your message. I have assigned a macro to a button. You can assign any macro to any shape in Excel. We are happy to help you with questions when you join our Excel For Freelancers Facebook Page here: bit.ly/groupexcel
Respect to you sir, Request to make a Excel video on Data consolidation from different workbooks & worksheet through VBA.
Hello Ali, that is a nice Idea. Thanks so much. I will add it to my list.
Having fun revisiting this now that I have a broader mental VBA map.
Elegant and simple solution.
Thanks so much Ben. I am glad you enjoy the training. The Conditional Formatting can make it difficult. The trick is to completely clear the formatting before pasting in your sub-category information, otherwise it will start duplicating the CF. I am so glad you enjoyed this older training.
This is great! very useful!
Is there a way that I can chage the data inside the drilled down table and it will also change in the invoices/payments table?
HI yes sure you can as long as you also copy over the original database row. This way any changes that are made, you can use that row to also update the original data. I hope this helps and thanks so much.
Hello Sir
Very nice and useful video. and as usual very clearly explained. One little question/suggestion please:
For customers where there is no invoice or payments, can there be a different sign other than "+" ?
Thank you very much..
Hi yes for sure, that is a nice idea. You could use any character you want. Once you run the advanced filter, and you find that there are no records that match, you could then replace the + to any character you like. Thanks for the nice idea. 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 with code screenshots here:bit.ly/groupexcel
Hi, Great work. I love all your uploads learned a lot. It may help me. I have a doubt! Shall I drill down even large amount of data? For example. State wise, city wise, village wise_ sales, it would include many products.. Shall I?
Thanks.
I think you could drill down 50 times and it would not be a problem as long as your databases were proper. Meaning a separate database for each table and there must be a Linked Column.... meaning Each City must have a State. Each State much have a Country. Each Product Must have a city. So there must be a linking item in each, then it would work great.
For I = 1 to 850,000 "I'm OK and I'm Alright". Next i
LOL. I have solved that "issue" in all of my later videos. Of "OK" > 3 then Range("OK").Delete. If i catch myself, I edit them out now. Check out any of the newer videos, I have kept the "OK's" down to a minimum. :)
Excel For Freelancers Glad you didn’t take offence. Your videos and vba codes are excellent.
Thank you for your videos and body of work. I have noticed that the cell focus needs to change in order for this VBA program to work. Is there a way for the focus to remain and detect a mouse click event to bring the opposite state?
Hi you can use the event Double Click instead of Single click, This way even if the focus remains on the cell, double click would still work. 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
Question - the invoice/payments drill down - if the invoices/payments are not in numerical order is there a way to sort the order when the drill down is revealed?
Hi Sonja, thanks for your comment. Yes sure, you can sort the results of the data before bringing them into the current sheet. They can be sorted by any column in the data. Thanks so much for the great question.
Excel For Freelancers Thank you so much.
Great, wonderful, which I was searching from several years
Thank you so very much, I really appreciate that Syed.
It's wonderful, thankyou so much
For sure, you are very welcome. Cam on ban. :)
Good video ❤️❤️❤️
Thank you for your Likes, Shares & Comments. It really helps Rofiqul
Hi. Awesome. Thinking to apply the one click sorting and the inline filter to the main table
Hi you could do this for sure, however when running the Sort Macro, it should start by Collapsing all expanded sections first. That would help maintain the table integrity.
There is no excel format provided for practice. Unable to get the link
Hi Abhisek, thanks for your comment. You can download the FREE workbook and follow along here:
Using Your Email: bit.ly/Drill_Down_Data_Wb
With Facebook Messenger: bit.ly/DrillDownData_FbDl
Thank you
Such great video
Thank you once again for video
Thank you very much. I do appreciate that Rahul
amazing thanks
For sure, you are very welcome Thanks so much.
requested the workbook to follow along, never received in email.
Hi sorry about that. Can you please email Angeli@ExcelForFreelancers.com, and notate that you are looking for the Drill Down Data workbook, so she can check on why you did not get that? (Also please check your spam folder as well) Thank you.
Good Tutorial but "Total Sales" column doesn`t work
Hi thanks the values in the sample application were not calculated. The training was focused on how to implement a drill down feature in Excel. Future videos may have accurate calculations if they pertain to the core training. Thanks very much for your comment.
Do you think that it is possible to make this this drill-down feature compatible with filtering? When filtering a column and trying to collapse an expanded field, I get the following error: "This won't work because it would move cells in a table on your worksheet". I assume the problem is that, after applying a filter, certain rows in your worksheet are hidden, screwing with the Hide Sub (.Range(lActRow + 1 & ":" & lLastOrdRow).EntireRow.Delete). Your worksheets has the same issues when filtering.
The idea is to put all original data on other sheets. Once you have that, you can do nearly Anything, with the data on your current sheet, including drill down, sorting and filtering. Make sure you are not using Excel's "Table" which has lots of limitations. I never use tables because of these limitations (My ranges just 'look like' tables because of the alternating row colors.
When you have all data in other sheets, there is no need to hide any rows, simple write the code to clear and re-write the data based on your filters.
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 with code screenshots here:bit.ly/groupexcel
@@ExcelForFreelancers Thanks for sharing this, but if in Our main data we need to do filter then the tool is copying the data on incorrect cells. How can we resolve that? Can you please provide a solution for this
Thank you so much for this. It helped me present lots of information on a single sheet.
But I had a problem when I turned on the filter on the table. The drill down information on level 2 and level 3 spilled over and replaced the level 1 information. Any advise on how to resolve this problem, please?
Hi Tony, thanks so much for your comment. You would not want to run a filter on this table. This table should not include your original data. To filter, use an advanced filter on your original data sheet, then bring over only the filtered data into this drill down 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
@@ExcelForFreelancers Thanks, you're a star! 👍🏽👍🏽👍🏽
Nice advice
Thank you so very much, I really appreciate that Kamal
Nice video
Thank you so very much, I really appreciate that Toha :)
Excellent!
Thank you so very much, I really appreciate that.
Amazing video . I tried to replicate with greater number of invoice lines (>100). It works well however for the last couple of lines the drill down does not happen. I have used the same codes. Is there a limitation on number of lines ?
Hi there should be no limitations at all. There is probably something in your code that is limiting that number. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Expand the row range in your code to 1000000 i.e. E5:E1000000 that will solve it.
It is really an extraordinarily tricks . Very nice
Thank you so much Afrin, I do appreciate that
I think I’m trying to be too clever in combining this training (without needing the filtering) with the Shared Workbook training (without having a remote file...yet) and then customising. 🤯 It’s going to be line by line testing. 🙃
I am sure it will be great. Just make sure to keep your original data separate when doing drill down. It gets risky and complicated if you are working with your original data table. I'm sure you will do great.
Excel For Freelancers Yes, all data is kept in a set of data tables that I will one day move to a shared directory. And although it seems a little obvious, line by line testing or setting a stop point within the VBA, it improves understanding as you can see what each line does. Solved a couple of issues really quickly yesterday doing that. 👍🏻
Good video. Seems like access would be better suited for it.
For sure this could be done in Access, but for small databases, Excel can be very flexible. Thanks for your comment.
Excelent!
Thank you so very much, I really appreciate that.
Excellent tutorial! is there a way to do multi line invoices?
Hi for sure. I will make a basic free video and a very comprehensive course on Invoices. I have that on my list so make sure you follow my facebook closely here: facebook.com/ExcelForFreelancers/
Nice
Thank you so very much, I really appreciate that Faragi
I like your style of getting around things using alternative solutions.
Thanks so much Lang, I do appreciate that.
File can't be downloaded
Hi Harun, 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.
@@ExcelForFreelancers Thanks sir
Loved the Drop-Down video. I did find that if you expand an invoice or payment by selecting the + and try to just shrink the area without moving to another cell first (select the - cell) the shrink macro does not work
HI I am so glad you liked it. You can fix this small issue after the expand, inside that macro just select any other cell, then you can click it again, for example at the end of the macro you can add Sheet1.Range("A1").select (or any other cell) and then it will work by clicking it again (also instead of single click you can use double click as well.
Thanks for your comment.
Ahh! Thanks for the question and answer. You solved a smilar issue I had with checkmarks.
I noticed you have to click off of a cell to re click the cell again. kinda annoying is there a fix to this? Can I do it with a shape?
Thanks for the Video I learned a lot!
Hi Deathium, there are two ways to fix this, You can either use Double Click (instead of single click) to open or at the end of the macro on single click just automatically select any other cell in VBA, which will force the selection to another cell such as
.Range("A1").select
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 Yeah So that what I did I moved the select after I click the cell.
"ActiveCell.Offset(0, -1).Select"
So I would love to have buttons or shapes. is there a way You could click a button and have the macro find what cell the shape is on? that would be awesome. Thanks Again!
I generally like the ability to click a cell and have a button appear on a row or column, This way you can use the same button/shape to appear exactly where you want it. You can see samples of this in my video here: ruclips.net/video/xOWJIlG8XEE/видео.html
How do you set background in blue & merged??
Hi you would right click any cell, go to Format Cells, click the Fill Tab, click Fill Effects, then just choose the two colors you want and then the shading styles below.
I hope this helps.
Where does the original data get inputted to
Hi and thanks for your comment. I just added some fake data into a table. There is no input form in this training. Thanks very much.
Excellent !!! very useful video.
Thank you very much. I do appreciate that Swami.
hi, do you make this work step by step, thank you
Hi thanks I have this video which I go over slowly. If you download the workbook, you can follow the code and hopefully that will help you.
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
How to change the dollar sign
Hi thanks you can update the currency symbol in the cell format. 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 some one tell me how to download the coading
Hi, 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.
hello sir i need this worksheet
Hi Sisco, 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.
awesome tutorial vba
Glad you liked it, thanks so much Apichat. :)
Hello, thanks a lot for this and other videos. Wonder if it is possible to drill down every position at once ? i Tried to do loop that select every "+" but after first its stopping. Will be grateful for any tips
Hi and thanks. If you are looking to an Expand All group, the easiest way would be to run a loop from the last row to the first row (reverse order) and select each cell in which the plus is in. This will automatically expand each group. (Make sure you go in reverse order)
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
How are you expanding without using hidden rows? Your explanation in the video doesn't clearly say - you only say what it is not, but not what it is? I guess it has to do with advanced filtering to expand?
THe table that you see, is not the original data, which is stored on separate sheets. When i want to expand the rows i simply copy down the data the # of rows that have returned in my advanced filter, clear out those rows that are duplicate and insert the data from my advanced filter.
I have a course coming out in a few months that will explain in more detail however the concept will be the same, so you may have to watch this a few times. Also feel free to download the free workbook where you can see exactly how I did this.
Great tutorial, smart coding!
HI thanks so much glad you like it
Please make HR dashboard..where I can add employees with there pics...leaves n salary making, along with salary slip printing with there code or names..and also tracking sales....state wise.. product wise...help doing making this
That's a great idea. This sounds like it would be perfect for a comprehensive paid course that would include a master workbook and perhaps 8 - 15 hours course, since there is a lot i could include including Time Click, Employee Info, Scheduling, Payroll Export, Time & Earnings history, etc.
Great work well but seem i cannot uncheck on the same checkbox unless i click on other cell first.
Eric Wang That is not a problem, you can just add one line of code to select a different cell after the code is run such as
Range("A1").Select
Then you can click on the same cell again and again without having to select another cell first.
I hope this helps
Thank you very much. I do appreciate that. You can select a different cell in vba after the macro runs if you like
Your ideas are the greatest thank you
Thank you so much Mohamed
@@ExcelForFreelancersI need your help. I am responsible for 3 warehouses. My work is to get the balance for the serviceable and damage between 10 types of ULD's (units loading device to load cargo or passenger baggage ) for many airlines at the airport, follow up the ULD's in and out process and handling with each flight and so With the repair workshops and scrap, I tried on many Excel files but I was not satisfied with it and I will upload to you the last file
Hi Mohamed. Thank you for your comment. In our Excel For Freelancers Facebook Group here: bit.ly/groupexcel we have 15,000 Excel experts who can help you with this. Just create a post and mark it as PAID JOB, so that a qualified Excel Developer can contact you with a fair price to complete this project for you. Thanks so much
Damn. It's so amazing.
Thank you so much Thien. Xin Cam On
Very good...
Thanks very much
The Runtime error rate with this advanced filter is huge :(.
I got it working then i added more information to the invoice, adjustet the range for the filter, adjusted the header to insert. Code wise it should work but insted it throws me an runtime error. Somehow the filter is not working and it's this line that makes trouble:
Tabelle4.Range("A3:L" & LastInvRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Tabelle4.Range("AA1:AL2"), CopyToRange:=Tabelle4.Range("AA3:AL3"), Unique:=True
Most run time errors on advanced filter are because of incorrect headers. The headers of all 3 (original table, advanced filter, and results should all match exactly the same. Errors happen when the column headers are different, even just 1 character difference will cause an error. I hope this helps.
Please feel free to join and post the issue in our fb group with screen shots if you like, you can join here bit.ly/groupexcel
LastInvRow is numeric and the range is string so put 'Cstr(LastInvRow)' instead
Thanks @Joske thats right, if the LastInvRow is blank or some text it's also going to create an error. Good point and thanks for the reply.
A check/uncheck all button would be good to add but how?
Hi, you can try adding Check/Uncheck this way: ruclips.net/video/UFw9bAkoCEo/видео.html
Hi how to download the workbook, it's not happening
Hi there are two ways to download the workbook. Using your email or with Facebook Messenger.
Using Your Email: bit.ly/Drill_Down_Data_Wb
With Facebook Messenger: bit.ly/DrillDownData_FbDl
Hi I have a unique data of 2000 is this works, I work in developers I generate electricity bill for 2000 flats . So I need to know how to track payment for rack months
Sure you can do that, by making adjustments to the formulas and code. If you have a specific question perhaps you can join our Facebook group as there are thousands of Excel experts to help you right in our group: bit.ly/groupexcel
Brilliant work. Thanks
Thanks very much
Hi...can u make it four level drill down....1st is region...then location...then branch...the sales relationship manager name..example...when click region like
Delhi...then come to Delhi's location...then will come to branch name then will come sales relationship manager name then will his total month on month business....if u made it...it will help us.
Thanku...
Hi this is certainly possible it just required 4 databases, each with a linking key. Thanks so much.