June 19, 2021
Hello. I created a series of workbooks within one folder to record and report monthly passenger counts for a passenger transportation contractor. Some of the workbooks are used to manually enter data for the current month, and other workbooks are used to populate data from the other workbooks to generate reports. The vlookup formula is used extensively in this process. Each month fresh data is manually entered, and the reports not only populate data from the workbooks within the folder, but also from the previous months folder, and the folder for the same month the previous year.
Here is my dilemma. I have since moved a few states away and have turned over the data entry and maintaining the workbooks to another person with limited Excel knowledge and experience, and it has been causing problems even though the workbooks have been working great for over 10 years. I am still working remotely primarily as a "billing specialist" and troubleshooter for these reports. I have recently heard about power queries and power pivots and was wondering if either of those would be good alternatives to using vlookup. Please note, however, that each month we are having to change the links within the workbooks for the current month, as well as some for the previous month and same month previous year, and I'm not sure how that would work with power queries and power pivots. One of the problems we have been experiencing is that some of the links in previous months folders somehow get corrupted and therefore effects the data within the current months folder, and I am also interested in a way to minimize that happening, perhaps by changing formulas to values. I know that there is an automated process for doing that, but because it takes about a month to manually enter all the data I am afraid the formulas would be changed to values before the reports are completed for the month, but I would need a simple way of doing so for the person working on the files.
February 13, 2021
This definitely sounds like a solution for PQ! I love PQ and highly recommend it to anybody. Here is an article to get you started on VLOOKUPS in Power Query. Also, here is some information on creating variables in Power Query. There are other ways to change links, but this might be the easiest way. Hope that gets you started, if you need more help, please come back and ask; personally, I love PQ. 🙂
June 19, 2021