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
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}. Continue reading