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:
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|
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:
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:
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:
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)