In some cases you might find that the RefreshAll command isn’t working as you’d hoped. For example, if you have a pivot table looking up data from a SQL database connection you need the pivot table to refresh after the connection but RefreshAll won’t necessarily do that. To ensure one connection is refreshed at Read More …
Month: October 2018
Multiple Find & Replaces
When you are data cleansing within your VBA routine a common action you might need to do is re-map text values to a different name so they match another dataset you’re joining them to. You can do this in VBA without having to specify each individual value in the code. In this example we Read More …
INDEX and MATCH functions
INDEX and MATCH are two of the most versatile functions within Excel. We will look at these functions individually to begin with but the real value comes from combining the two of them. They can serve as a useful alternative to VLOOKUP because they can look up in either direction – VLOOKUP is restricted Read More …
Power Pivot – Drill down to more than the first 1,000 rows
When you double-click a value on a Power Pivot table to view a listing of the results, by default the pivot table will only return the first 1,000 rows. To expand this limit, go to the Data tab and click on Connections. In the dialogue box, click on ‘ThisWorkbookDataModel’ and go to Properties. In Read More …