Excel Scrollable List Example
If table in worksheet is too large , in such cases ,it is difficult to examine the table and to distinguish the values. As a solution, scrollable lists can be used.
Scrollable lists help to the user to display and review data in the large tables. This is a pretty cool solution.
We can create a dynamic scrolling table using scroll bar control and built-in formula. In our template, we will display Data sheet data by scrolling in a 10-row table on the Report sheet.
Before ,a scroll bar is added to the worksheet. To add scroll bar control to the sheet : Let’s go to Developer Tab –> Insert –> Scroll Bar (Form Control).
Click on Scroll Bar (Form Control) button and click anywhere on your worksheet.
Right click on the Scroll Bar and click on Format Control. This click will open Format Control dialogue box.
In Format Control dialogue box ,select to ‘Control’ tab, make the following changes:
– Current Value: 1
– Minimum Value: 1
– Maximum Value: (value of cell K6 is added with Worksheet_Activate procedure)
– Incremental Change: 1
– Page Change: 10
– Cell Link: $K$2
Data sheet column headings are entered with formulas starting from cell B2 to cell I2 .For example ; formula of cell B2 is “
Easy way to enter formula; A formula is entered in cell B2, then when the mouse is moved over this cell while cell B2 is selected, the + character appears in the lower right corner, this character is dragged from cell B2 horizontally to cell I2. So, formulas are automatically entered in cell range B2: I2.
Starting at cell B3 , the following formula is entered to the range B3:I3 as described above :
OFFSET formula is dependent on cell K2.
In Data sheet, we find the number of data in column A (the number of used rows) with the Counta formula and enter the value in cell K4.
=COUNTA(Data!$A:$A)-1 -1 in the formula was added to subtract the header row from the number of used rows .
In our template, the number “1005” is obtained by the formula.
To get number of scrollable list rows , the formula
=COUNTA($B$3:$B$13) is entered into cell K5. The result is “11”.
=$K$4-$K$5 formula is entered into cell K6 . The value in the cell becomes “994”. This value is the maximum value of the scroll bar.
To create a dynamic scrollable list, the maximum value of the scroll bar must be updated according to the changings in the table in the Data sheet (such as adding-deleting data). For this, we added the following codes to the Report sheet Worksheet_Activate procedure :
Private Sub Worksheet_Activate() ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Max = ActiveSheet.Range("K6").Value End Sub