To learn how the OFFSET function works please watch the video. Data is the name of the data source worksheet and A1 is the first column heading in the data source table. This formula uses cell references and sheet names in my example. – In the Cell link box, select an empty cell in the data source worksheet (in my example that’s cell K1).ĥ) In the dashboard worksheet, click in the cell under your first column heading – in my example that’s cell A2 and type the following formula =OFFSET(Data!A1, Data!$K$1,0).
There are two changes you need to make at this stage. Right-click on the scroll bar and select Format Control. You can then draw a scroll bar onto your worksheet.Ĥ) The scroll bar outputs a value to a cell in your workbook. Then click on OK to confirm.ģ) On the Developer tab, which should now appear on your Ribbon, open the Insert menu in the Controls group, and select the Scroll Bar control under Form Controls. In the Excel Options dialog box tick Developer in the list of tabs.
To display the Developer tab, right-click on a visible Ribbon tab – the View tab for example, and select Customise the Ribbon in the shortcut menu. You will need to display the Developer tab on Excel’s Ribbon to do this. You can then copy that formula across to pick up all the other column headings.Ģ) The next step is to create a scroll bar control. To create the link, enter an equals sign and then click on the first column heading in the data source sheet. This way the column headings for your scrollable table will be visible. Link cells in your dashboard sheet to the column headings in your data source sheet. Here are the steps for creating a scrollable table in Excel.ġ) The scrollable table will need a data source which is normally placed on a separate sheet to your dashboard. This might be useful in the context of a dashboard where you do not have the space to display the whole table and only want to display, say, ten rows at a time. In this video, I demonstrate how to create a scrollable table in Excel.
0 kommentar(er)
