So, you’ve designed a great looking report or dashboard in Excel. But, wouldn’t it be great if you could add a small section of the report where the user could scroll through a filtered set of the raw data? Well, it’s funny you should ask…
Here we have some data I made earlier… a table of order data and a list of orders.
What we’re aiming to do is create a magical Scrolling Dynamic Lookup window to add to our equally awesome report, like so:
In order to make our life that little bit easier – and to help you understand the formulas – I first created some Named Ranges, just like the ones below.
The next step is to create the structure for our scrolling ‘window’. This repeats the column headings from the order data table and adds an additional Item column, which the dynamic lookup formulas will reference.
In D2 we need to create a Data Validation rule that provides the drop down filter to select the particular order that will be displayed. As you can see below, this references one of the Named Ranges we already setup:
In case you’re wondering where the vertical scroll bar came from, this is a Form control that was added using the developer tab.
NB: If you can’t see the developer tab, you need to turn it on by opening the Excel Options form (Office Button > Excel Options). In the Popular tab you need to check Show Developer tab in the Ribbon.
In order to complete the setup of the vertical scrollbar, right click on the scrollbar object and select Format Control; you should now see the following form:
The key parameter is the Cell link, which needs to refer to B4. You can also modify other parameters that will affect how the scrollbar operates. For example, I set a Maximum value of 50, but if I had any orders that had more than 54 products (50 plus the 4 rows below the first row), they wouldn’t all be displayed. To make this truly automated, the next step would be to add some VBA code to update the Maximum value based on the maximum order size, but that’s for another time.
Now we have the scrolling window created, all we have to do is throw in the magical Array formula in C4 and copy to all remaining cells (remembering to hold CTRL + SHIFT while we hit ENTER!):
The MATCH function returns the column number in the order data table
The SMALL function returns the column number in the order data table. This utilises the Order ID filter that we created in D2 and helps to make the formula dynamic. This is the Array part of the formula requiring CTRL+SHIFT+ENTER.
The index function returns the value from the order data table using the column and row number that the SMALL and MATCH functions have returned.
Finally surrounding our INDEX function with the IFERROR function, allows us to show nothing if the INDEX function returns an error when no value exists.
Depending on how you need to implement this in a report, there are a number of tweaks you can perform from simple formatting, to removing unnecessary columns or increasing the number of rows in the Dynamic Scrolling Lookup.
Now, download and get learning!