Presenting an elegant solution to displaying large data via scroll-able list. All you need is a
- A list (of course)
- And formulas to tighten everything together !!
Lets make a killing here
So here is our list!
We have 50 Employees with Designation and Date of Birth (Download the list)
Next we need a Scrollbar
You’ll have to buy one! 😯 .. Just kidding 😆 You will find the scrollbar in the Insert drop down in Developer tab (can’t find Developer Tab ? Read here !)
- Click on the Scrollbar and draw one on the sheet, keep the orientation vertical (it can be drawn both ways, vertical and horizontal)
- Formatting the scrollbar
- Right click and go to Format Control (alternative shortcut is CTRL 1)
- Set the Minimum Value to 0
- Maximum Value to 40 (because we have 10 empty rows between the top and bottom end of the scrollbar)
- Link it to a cell in the sheet. Now when you shift the scroller, you’ll see the linked cell reflecting the value as per scrollbar’s movement
Tying everything together with a formula
The Logic goes like this
- As we move the scroller the value in the linked cell changes.. right?
- From that number (in the linked cell) we want 9 more rows to be displayed between our scroll bar.. with me till now?
- We will use the INDEX formula to lookup the values in the main employee table
Here is our formula =INDEX($B$4:$D$53,$G$4+ROWS(F$6:F6),COLUMNS($G5:G5))
Decoding the formula
- The Index function is looking in the array of the employees $B$4:$D$53
- The row number is the Linked Cell + Row Incrementor .. Why? because we want 9 more values after the linked cell. [Related: Learn how to make a row incrementor] $G$4+ROWS(F$6:F6)
- Column number is displayed by the column incrementor because when we copy the formula to the right we need to display the next column result COLUMNS($G5:G5)
Here is a quick snapshot