Entries by Oz du Soleil

Creating a Variable Hyperlink in Excel

This is cool! The problem: 11 worksheets and hundreds of codes Objective: the ability to type a code and be taken directly to that code wherever it is in the workbook. It’s like the Find/Select feature in Excel, the user wanted to stay in the worksheet and minimize use of the ribbon. One thing to […]

Power Query: Summing Data from Specific Ranges

This is a CRAZY one! Kevin Lehrbass presented this problem: A tall stack of mini data ranges that need to be totaled. BUT! Only total the ranges that say “TOTAL.” Ignore the ranges that are labeled anything else. (Kevin’s solution: https://www.youtube.com/watch?v=oomf8bfcFEw) Kevin’s video shows various formula solutions. My video shows a Power Query (Get and […]

The Wildest Excel Spreadsheet Ever: HLOOKUP and the Importance of Building a Model

NOTE: This video isn’t about the details. It’s the thought process. So, please don’t worry if you don’t catch everything. Download here: http://datascopic.net/messiest Subscriber, eCabinets Tips and Tricks asked about the most bizarre spreadsheet I’ve ever faced, and what I did to untangle it. ‘Bizarre’ can be measured a lot of ways. I chose this […]

Power Query Merge Columns

More about working with Excel’s Get & Transform (aka Power Query). this time, we explore merging columns and the “gotchas” that you need to know about. 0:49 Description of the 3 warnings 1:09 Showing the problem 2:00 Problem 1: The source columns are gone 2:17 Problem 2: The columns are out of order 2:50 Merge […]