ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

22Jul/111

Scrolling Dynamic Lookup

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.

Sample data

What we’re aiming to do is create a magical Scrolling Dynamic Lookup window to add to our equally awesome report, like so:

Scrolling Dynamic Lookup

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.

Creating Named Ranges

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.

Scrolling Dynamic Lookup structure

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:

Data validation for filtering

In case you’re wondering where the vertical scroll bar came from, this is a Form control that was added using the developer tab.

Adding the vertical scrollbar

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:

Setup the scrollbar

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!):

={IFERROR(INDEX(DataTable,SMALL(IF(DataTable=$D$2,ROW(DataTable)),$B4),MATCH(C$3,DataTableHeaders,0)),"")}

Some explanation...

The MATCH function returns the column number in the order data table
MATCH(C$3,DataTableHeaders,0)

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.
SMALL(IF(DataTable=$D$2,ROW(DataTable)),$B4)

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.
 INDEX(DataTable,SMALL(...),MATCH(...))

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.
IFERROR(INDEX(...),"")

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!

Scrolling Dynamic Lookup
Scrolling Dynamic Lookup
Scrolling Dynamic Lookup.xlsx
Version: Excel 2007
12.9 KiB
1635 Downloads
Details...
Comments (1) Trackbacks (0)
  1. I have tried to do the same file but I am not able to copy the array function in all other rows and column it is showing same 1st cell value in all the cells


Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

No trackbacks yet.