Using Index Function & Scroll Bar Button to Make a Dynamic Appointment Box

Using Index Function and Scroll Bar Button to Make a Dynamic Appointment Box

By using the Scroll Bar button and the Index formula, we can prepare the below workaround. The moment we click on the Scroll Bar button the date will change to next and so is the corresponding data like Place and Appointment details. How all this done, is explained here in this post.

The raw data we have is in the below table. For this data create a table by selecting the data and pressing the Ctrl L button. Click somewhere in the data of the table and you will see the Design tab under Table Tools on the main ribbon. Now in the Table name box by typing the table name you can change it to any like in this case it’s Events or you can go with the default name which would be like Table1 or so.

Serial No.	Date	Place	Appointment Details
1	10/1/2013	Islamabad	Meeting with Directors
2	10/2/2013	Peshawar	Site Visit
3	10/3/2013	Gujranwala	Farms Visit
4	10/4/2013	Faisalabad	Fish Farm Visit
5	10/5/2013	Lahore	Attending a Marriage
6	10/6/2013	Sheikupura	Meeting with Factory Managers
7	10/7/2013	Nawabshah	Farms Visit
8	10/8/2013	Hyderabad	Plant Visit
9	10/9/2013	Karachi	Meeting with Directors
10	10/10/2013	Gawadar	Site Visit

Now somewhere in the sheet put the first record number which is 1 and by using the Index function pull the information corresponding to that number which for now is 1 and the info we want to pull is first date, place and Appointment details.

So in the F1 cell we have written 1. So to pull the first date record, our formula would be below.

=INDEX(Events[Date],$F$1)

Here the Events table’s Date column is our array and we want to pull the first record so referring F1 cell where 1 is mentioned.

The result would be like 41548 (date in numbers), so to convert it to date format, use the below formula.

=TEXT(G2,"dddd d, mmmm")

Now similarly pull the Place and Appointment details by same way with Index formula.

=INDEX(Events[Place],F1)

=INDEX(Events[Appointment Details],F1)

Now our results would be like below.

1
Date	41548	Tuesday 1, October
Place		Islamabad
Appointment Details		Meeting with Directors

Go to next sheet or where you want to make that workaround/presentation box.

First Insert the scroll Bar button. For that go to Developer Tab the Insert and press on the Scroll Bar button.

By dragging you can place it anywhere and manage its size as well. Now right click on the button and open the Format Controls and Control tab. As out data range is from 1 to 10 so set the minimum & maximum values to 1 and 10 and incremental change to 1 so on a click the change is of one.  In the Cell Link type the cell reference or first click the cell link box and the cell which you want to refer. This would be the same F1 cell where we put 1 and used it in the Index function to pull the first record. So in this way a click on the button will change the value in F1 cell and accordingly our results of the Index formulas as well.  Now under the button in the cell provide a cell reference of the cell where we have a formula and are pulling the date and then in the next cell the Place and then the Appointment details. Fill colors in there and do some formatting to make it colorful.  Now pressing a button will populate the next date and corresponding details of Place and Appointment.