LibreOffice tutorial – Part 6 – Index

This is the 6th part of my LibreOffice Tutorial to learn about simple useful formulas and a tiny bit of macro to make a simple daily agenda.

In the first part we looked at renaming a sheet, entered  some random data, formatted cells and started to build the logic of the agenda.

In the second part of this tutorial we wrote some time and IF formulas.

In the third part of this tutorial we learned about nested IF and AND function.

In the fourth part of this tutorial we learned about VLOOKUP.

In the 5th part we looked at how to create and use a ‘named range’.

In this part, in order to display previous and next activities for the current time I am to introduce the INDEX function to you.


To be able to display activities that were before the current one we can use an order system by numering all our activities.

Logically our very first activity would be number 1, the next activity would be number 2, the third number 3, etc…

So in Sheet ‘Planner’ we should add a new column in our table and we should name ‘order’ (or ‘index’ but that would be a bit confusing for the rest of this tutorial) and enter 1 for the first activity, 2 for the second, etc…

An easy way to do that is to simply enter 1 in the first cell and then double click on the handle (the small square in the bottom-right corner of the selected cell), it will automatically fill-up the other cells downward incrementally .

1 thing to remember to do:

We need to extend the range of the named range ‘planner_daddy’ (or what ever funky name you gave to your table)to be able to refer to our new column ‘order’ easily.

You can do this in LibreOffice 6 by selecting “Manage Names…” from the top-left box, where the named range is listed.

You can also do it by right-clicking on when the named range is selected and then by choosing in the context menu the entry “Manages Names…”

You can also select from the menu “Sheet>Name Ranges and Expressions>Manage”, or if you like keyboard shortcuts ‘CTRL+F3’.

In the Manage Names window, change the “Range or Formula” expression for our ‘planner_daddy’  Name, from

$planner.$A$2:$D$9

to

$planner.$A$2:$E$9

 

The Name Ranges management window will look like that:

 

Once you have click ‘OK’, you can verify that your new range is correctly set but by selecting it from the named range box (upper left corner). It will highlight all the cells included in the named range.

This is what I have:

In the next step, we are going back to sheet ‘Now!’ and we are going to write the formula in the cell above our current activity.

Our current activity being displayed in cell C8, we are going to write the new formula in cell C7.

WE are going to use the INDEX() function to display the previous activity in cell C7.

INDEX() is used to find a value in a table using an index number, in fact row number, for a given column.

We want to display the activity that is before the current activity. To do that the logic to follow could be:


1-Get the order of this current Activity in table planner_daddy

2-Subtract 1 from that order

3-Return the value of order-1 for column 4 of table planner_daddy

To perform 1 we can use the same formula that is in Cell C8. But we should look for the value in column 5 (Order) of table planner_daddy instead of column 4 (Activity), that has the value 1 (or ‘TRUE’) in the first column of planner_daddy.

VLOOKUP(1,planner_daddy,5,)

So this return the value in the ‘order’ column for our Current Activity.

To perform 2, let’s just take away 1 from it:

VLOOKUP(1,planner_daddy,5,) – 1

To perform 3, we use INDEX() like this:

INDEX(planner_daddy, VLOOKUP(1,planner_daddy,5,) – 1, 4,)

Explanation:

INDEX() returns the value of the cell in row define by VLOOKUP(1,planner_daddy,5,) – 1 which look for the value in column 5 of planner_daddy for 1st column value = 1 (or ‘TRUE’) take away 1, and in column 4.

Once we have that formula entered in C7, if we drag the formula to D7 and replace the number 4 by 2, we get the start time of the previous activity in cell D7.

If we drag again the forula to E7 and replace the number by 3, we get the end time of the previous activity in cell E7.

With the formulas:

Row/Column C D E
7 =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,4) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,2) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,3)
8 =VLOOKUP(1,planner_daddy,4,) =VLOOKUP(1,planner_daddy,2,) =VLOOKUP(1,planner_daddy,3,)

 

If we want to display in cell C9 the activity that is coming after the current activity (in C8) we can use the same formula than is in C7 but adding 1 to the VLOOKUP instead of taking way 1.

=INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,4)

 

In my planner I display the current activity in the middle, between the 2 previous activities and the 2 next activities. So the formulas for the previous and next activities are only different in the number added to the second argument of the function index:

-2 for the activity that was before the previous activity,

-1 for the previous activity,

+1 for the next activity and

+2 for the activity after the next activity

This is how the formulas look like:

 

Row/Column C D E
5
6 =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,4) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,2) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-2,3)
7 =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,4) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,2) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)-1,3)
8 =VLOOKUP(1,planner_daddy,4,) =VLOOKUP(1,planner_daddy,2,) =VLOOKUP(1,planner_daddy,3,)
9 =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,4) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,2) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+1,3)
10 =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,4) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,2) =INDEX(planner_daddy,VLOOKUP(1,planner_daddy,5,)+2,3)

 

In the next article we will look at an error that is happening if like me, you are working late and there is actually no activity to come next as you have reached the last activity already…

In other words, how can we control if there is an error in the formula and what should we display if there is an error by using the ISERROR() function.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.