This is the 5th 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** functions.

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

In this fifth episode of the Tutorial serie, we are going to learn how to create and use a ** named range**.

In cell **C8** of sheet ‘Now!’ we have the following formula:

**=VLOOKUP(1,$planner.A2:D9,4,)**

It does display the current activity, so our reference to **$planner.A2:D9** works fine. If not and you need some help, please leave a comment to this article and I’ll help you to solve the issue.

Let’s display in cell **D8** the start time corresponding to the current activity, and in **E8** the end time.

We can simply drag cell **C8** two cells to the right. We are getting this:

The activity is fine (“Finally free!”) but I am getting an ** #N/A** error in

**D8**and nothing in

**E8**.

The ** #N/A** error is happening because the VLOOKUP function is searching for the value 1 and cannot find it at all.

If you look at the formula that is in **D8** and **E8** now, you will realise that the reference to the cell range has changed and because of that we need to amend the formula in both cells:

Row \ Colum | C | D | E |

8 | ‘=VLOOKUP(1,$planner.A2:D9,4,) |
‘=VLOOKUP(1,$planner.B2:E9,4,) |
‘=VLOOKUP(1,$planner.C2:F9,4,) |

There are two things we want to change in **D8** and **E8**:

In both cells the reference to the table containing the activity, start time and end time, located in sheet ‘planner’ is not right. Indeed by dragging the cell to the right, the reference has also changed from **A2:D9** to **B2:E9**, and to **C2:F9**.

We could have locked the reference with ‘$’ (dollar sign) in cell **C8** before dragging the formula to the right, as seen in part 2.

So our cell in **C8** would be:

**=VLOOKUP(1,$planner.$A2:$D9,4,)**

This would solve our problem, but imagine that would want to add a new activity, like “walk to the park”, in the list of activities you then would need to change the table reference in every cell using the table.

That is why ** named range** are used for: once you have defined a named range with reference to a table like

**$planner.$A2:$D9**, you can use the named range in the cells that need to use that table, like our

**D8**and

**E8**. And whenever you want to extend the table with a new line or a new column you can easily update the named range, and you don’t need to update all the cells using that named range.

And it is very easy to create a named range!

And this is how to do it.

In our sheet named ‘planner’, select the cells region from **A2** to **D9**. Then in the top left corner, just above the column numbers, where you can see the reference of the selection, write something like ‘planner_daddy’ there, and press enter. That’s it, your range **A2:D9** has been named!

There are a few restriction on the name that you can give, for example you cannot name it ‘planner’ because there is a sheet already with this name and that would be confusing for LibreOffice…

So to make sure it works, click anywhere in the sheet to deselect your table. Then select the name you have entered from the drop down list in that same corner, and if everything is fine it should highlight your table

Son back to our sheet named ‘Now!’,wecan now replace the formula in C8 with:

**=VLOOKUP(1,planner_daddy,4,)**

Then you can drag C8 to D8 and E8.

We are not finished yet. To get the ‘start time’ in D8, we simply need to replace the ‘4’ in the formula with ‘2’, because this is the column number of the ‘start time’ column in table ‘planner_daddy’.

Similarly in E8 you simply need to replace the ‘4’ with ‘3’, which is the column number for ‘end time’ in ‘planner_daddy’ table.

So we should have the following formulas:

Row/Column | C | D | E |

8 | ‘=VLOOKUP(1,planner_daddy,4,) | ‘=VLOOKUP(1,planner_daddy,2,) | ‘=VLOOKUP(1,planner_daddy,3,) |

And I have the following display:

And I feel so tired now!

In the next part of this LibreOffice Tutorial I will show you how to display the previous and next activities using function **INDEX**().

(to be continued)