0

A lot of our members staff have trouble finding which counties various cities and towns are in.

Our work involves writing and sending letters, of which the county is a mandatory field. I want to create a table in LibreOffice Calc that has all the respective counties for each city and town.

Using the data in this table, I want to be able to create a page where you can simply type the city, press enter and it displays the county that it's In. Maybe even postcode too?

I don't want to use Ctrl +F. Is there a way to do it?

5
  • Will you solve this issue with "Excel" (mentioned in your question) or need an alternative for this? (such as OpenOffice/LibreOffice). Your problem is as easy as using the vlookup/hlookup function (help.libreoffice.org/Calc/Spreadsheet_Functions#VLOOKUP or help.libreoffice.org/Calc/Spreadsheet_Functions#HLOOKUP) to locate the country inside a table using the data from a single cell, or by using the auto-filter options of the spreadsheet (let's say: LibreOffice Calc). Do you mind giving us some more details please? Dec 3, 2013 at 21:00
  • Sorry, it needs to be on excel. I need something that we can keep open all day that doesn't take up a lot of the screen. Maybe just a little square where you can enter the first part of postcode or the city name and it tells you the county. Something where instead of using ctrl +f or whatever, it just has a field for text then an actual search button?
    – thomas
    Dec 3, 2013 at 21:05
  • I am rewording your question in order to drop an answer and avoiding people to vote it down for closure. Please be patience. Dec 3, 2013 at 21:16
  • That is fine, only we don't use libre office, we use Microsoft Excel?
    – thomas
    Dec 3, 2013 at 21:27
  • Then you're in the wrong place to ask, my friend. This is a Ubuntu based support site and Excel seems to be a Microsoft based piece of software. Thus I suggest you to move your question to a different support site. Anyway if you don't mind I am in the middle of my writing and I would like to finish it in order to other users get the benefits of your needs and a possible solution. Thank you! Dec 3, 2013 at 21:34

1 Answer 1

4

Well, look. First of all: If you wish to forcefully use "Excel" (as mentioned in your original question and the comments) you will be in the need to use it via WINE, which may decrease the speed on how do you do this task and certain functions (like the vertical and/or horizontal lookup functions) may not work as expected.

I suggest you to give a chance to LibreOffice Calc, which handles both Excel (.xls and .xlsx) and LibreOffice (.ods) file formats. This way you can share your document with MS (Windows) based system users and work with the data without a major issue in any direction.

Now, let's cook fast food.

Let's say we have a spreadsheet with the details of your customers, as shown in the next small table called "Clients" (see the name at the bottom of the spreadsheet).

enter image description here

Now, let's rename "Sheet2" and use the "Search Form" name for it. After which we'll setup certain cells in order to contain the desired data as follows:

enter image description here

Now, having in mind that we have a "vertical list" where the data is having a column name and rows containing the details of each record, we'll use the LibreOffice's VLOOKUP function in order to search for the desired data and bring the results on the corresponding cells, as follows.

  1. In the C7 Cell of the "Search Form" spreadsheet, (which will correspond to the "State" Data), we put: the next formula:

    =VLOOKUP(C5,Clients.A2:G7,2)

  2. By Default we'll see a #N/A error message in the cell but it is caused by the NULL data in the "City" cell (C5), we'll solve this issue by writing something in the C5 Cell, something like "San Antonio" and the form will deliver the right State in the proper cell as shown in the next screenshot.

enter image description here

Now, in a different recipe, we'll use the same data but this time we'll sort the columns based on a different order as shown in the next screenshot:

enter image description here

I will explain later the need to change the order on the columns. For now let's focus on the creation of the new search form formulas in order to bring the data in the right place.

Now, to look for the "Name" field, we write the next in the C14 and C16 cells respectively:

C14: =VLOOKUP(C12,Clients.A2:E7,4) C16: =VLOOKUP(C12,Clients.A2:E7,5)

This will give the next results when looking for "Portha, Joseph":

enter image description here

Please note that -nevertheless- the data in the first form (the search state based on city form), are wrong this time.

This is because the VLOOKUP function is using the first column data in order to retrieve the proper results. If we translate the formulas we say that:

In the first example:

=VLOOKUP(C5,Clients.A2:G7,2) means:

Match the data contained on C5 in the vertical array of Clients from A2 to G7 and show me the data in the column number 2.

In the second example:

=VLOOKUP(C12,Clients.A2:E7,4) means:

Match the data contained on C12 in the vertical array of Clients from A2 to E7 and show me the data in the column number 4

Now, this can be useful for practicing some VLOOKUP exercises in order to learn how to create a customized search form.

If you wish to download the .ods file that I used for this exercises just click right here.

Now, if you need to run simple search solutions you may wish to use the "Filter" options in the "Data" Menu of LibreOffice. To do this simply go to the spreadsheet where the data resides, make sure it includes headers (titles for the columns) and hit the "Data" Menu, then "Filter" next "AutoFilter". Your column names will get a small button with an arrow pointing to downside. If we hit any of these small icons we'll see useful filtering data in order to separate the data in a reduced group which will contain only the desired data as seen in the next screenshots:

Auto Filter buttons
enter image description here

Auto Filter options for the State Column selecting only the Texas State enter image description here

Results of choosing only the Texas State in the drop down filter options. Please note that the small icon for "State" is now blue, indicating filtering on this criteria enter image description here

If you wish to do some additional search, you can run a "Standard Filter" from the options on any of the drop down auto filter buttons and you will see something like this:

enter image description here

In this dialog window you can use as many filter criteria as you need in order to keep only the desired data, remember that filter criteria values shows a drop down menu with a selection of the values in the table.

I hope this help you to solve your issue.

Final words: If you insist on using Microsoft Excel remember that the VLOOKUP/HLOOKUP and Standerd Filter, the same as other functions works the same both in LibreOffice and Microsoft Office and there is no problem when converting, unless you experience some kind of issue with translations, lets say: In Spanish we use BUSCARV/BUSCARH and other spanish words to create the formulas.

Good luck!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .