If you regularly create PowerPoint presentations that include information from Excel, you will know how frustrating it can be. Having to re-copy and re-paste the information every time a change is made in the original Excel documents. Have you come across the feature which enables you to link your Excel workbook directly into your PowerPoint … Read more
I was recently asked this question – “How can I make my workbook read-only?”. I gave the person an answer. However, the real question they were asking turned out to be “How can I make my workbook read-only for everybody but me”. In this post, we will explore some of the options available. Save the … Read more
My first experience of Bullet Charts came from reading Information Dashboard Design by Stephen Few in 2007. This was the year I started to get serious with dashboards. Initially, to create Bullet Charts I used the 3 stacked bar/column charts approach. Later, I moved to using the two-axis stacked column where possible, but in general, … Read more
We have now reached the end of the Mastering VLOOKUP series. Hopefully, you’ve picked up a few tips and tricks to take your VLOOKUP skills to the next level. If you’ve been following along you will have learned more than just VLOOKUP: Download the Advanced VLOOKUP Cheat Sheet There is a lot to remember, so … Read more
In the previous post in this series, Mastering VLOOKUP, we learned how to look up the 2nd, 3rd, 4th or nth item from a data set. Today, we will be expanding on that by looking at how to return all the matching values in the dataset. Plus, we will combine this with partial matches, so … Read more
In a Macro or Add-in there may be times when you require the user to select a file. It is possible to use the standard FileDialog box for this purpose. The following code is intended to provide the most common settings which you can amend to meet your needs. Open the file DialogBox for selecting … Read more
In this series, Mastering VLOOKUP we have covered a lot of ground. But this post is one of the most fiendish of all. VLOOKUP, by its design, is intended to return the first match it finds. But, if we want the 2nd, 3rd or nth VLOOKUP it not so easy. Often people resort to helper … Read more
The 2nd argument in the VLOOKUP function is the table_array. This is the range of cells which contains the data to be used within the VLOOKUP function. For example, in the code below A1:C9 is the table_array. =VLOOKUP(“O Redding”,A1:C9,3,FALSE) When using the VLOOKUP function, we often know how many rows there are in the data, … Read more
There are times when our data is laid out in columns and rows. In these circumstances, we may need to VLOOKUP row and column at the same time. Look at the example below, how can we look up the Low for Jun? This post will show you how. VLOOKUP row and column with VLOOKUP & MATCH … Read more
There are a lot of circumstances where you need a Macro to run when a workbook is opened. It could be one of the following: Show a user form which requires input Activate a specific range or object To run a process which updates the data Check the username of the person accessing the file … Read more