Today I want to present how to use Excel Buffer functionality in order to update/import data in NAV/D365 Business Central based on data from Excel files. Example of what we want to achieve To show the concept I made a very easy example, so let’s suppose we have the following customers with Location Codes: And…
As you might noticed, Excel is one of my passions, so I always thought about creating an Excel Hire test that would test the knowledge of candidates about the most important Excel concepts. So here it is 🙂 What covers this test ? Lookup Functions: vlookup, index and match Basic Reference Function: offset Basic Date…
There might be situations when you would like to set a trial period for your Excel applications: maybe you want to prevent employees take the applications and use them forever in other places after they leave your company or you simply need to provide to a client a trial that can be tested only a limited…
In this post I will demonstrate how to use Dictionaries in Excel VBA in order to calculate, store and use data summarized on different criteria. In the real life I used the algorithm from this post to automate the issue of inter-company invoices. This automation saved dozens of hours considering that before they had it, users where issuing…
The creation of this Excel Addin started in mid-2015 and the trigger was the need to streamline and automate general recurring and/or time consuming “manual” work in Excel. This is a project at which I work constantly by creating new features or improving the existing ones. In this post you can find examples of how…
You have an Excel file with thousands of rows like in the below image and for each row you need to extract the file name: How would you do this ? Solution: One formula that can solve your problem is this one: =RIGHT(A3,LEN(A3)-FIND(“$”,SUBSTITUTE(A3,”/”,”$”,LEN(A3)-LEN(SUBSTITUTE(A3,”/”,””))))) Let’s explain how it works: The file name is the sub-string after the…
In this post you can watch and/or download a video tutorial about Microsoft’s Excel 2016 Power Query basic features. The parts of the tutorial are the following: Part 1: an overview of Power Query, the working files used in the tutorial and the final report which you learn to create in the next sessions Part 2:…
In this post I show you how to Import in Excel exchange rates from a webpage using VBA Internet Explorer Automation. You will also learn to use some nice formulas (date formulas, counta, indirect, text) and create a dynamic chart to represent the trend of the imported data.
Excel model used to track and report the data regarding your personal income from the companies you worked for. At the end of the post you can also download a free example and use it by replacing the dummy data with your own data.
What is the Paysplip Sender ? An Excel VBA application which automatically sends the Payslips each month to all employees in Password Protected PDF file. How it works ? 1) The HR user inputs in sheet Send all employees with ID, Name, E-mail, Password for PDF file with the Payslip and the Year and Month:…