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

13Jul/116

Complex Transpose

I’ve called this tip ‘Complex Transpose’, which hopefully describes what I’m about to show you. Essentially, it’s about transposing a two column data set of related items into a multi-row and column data set.

The key technique employed utilises Array formulas, which I won’t go into detail about, mainly because plenty before me have (see links below). The quick 'how-to' is that you need to hold down the CTRL & SHIFT keys when pressing ENTER to commit the formula, instead of just hitting ENTER. Their other common name is a ‘CSE formula’ (Control, Shift, Enter). When committed, the formula is surrounded by curly {brackets}.