ods file that I used for this exercises just click right here. Now, this can be useful for practicing some VLOOKUP exercises in order to learn how to create a customized search form. 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 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. If we translate the formulas we say that: This is because the VLOOKUP function is using the first column data in order to retrieve the proper results. Please note that -nevertheless- the data in the first form (the search state based on city form), are wrong this time. This will give the next results when looking for "Portha, Joseph": Now, to look for the "Name" field, we write the next in the C14 and C16 cells respectively: For now let's focus on the creation of the new search form formulas in order to bring the data in the right place. I will explain later the need to change the order on the columns. 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: In the C7 Cell of the "Search Form" spreadsheet, (which will correspond to the "State" Data), we put: the next formula:īy 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. 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. After which we'll setup certain cells in order to contain the desired data as follows: Now, let's rename "Sheet2" and use the "Search Form" name for it. 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). 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. xlsx) and LibreOffice (.ods) file formats. I suggest you to give a chance to LibreOffice Calc, which handles both Excel (.xls and. 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 use Dream Host as the ISP, and they support all the usual suspects, including PHP, MySQL, Python, Ruby, etc.Well, look. I'd prefer to not write code myself because people will be using this for at least the next ten years, and I don't want to support it forever. I don't want to use a spreadsheet, like Google Docs, because I don't trust the users to keep the recording IDs straight in the song table. They could potentially try to do this at the same time, so I need record locking too. Several people could create / edit Song records. Only one person can add / edit Recording records. I need to restrict write access to specific users on a per-table basis. I need support for referential integrity in that the Recording ID field in the Song table must be a valid entry in the Recording table. Probably a dozen fields in each table, a few of which would be free-form text of up to several sentences. The second table would have a record for each song, with a link back to the recording. One table would have a record for each recording. The task is for people to listen to recordings of festivals and concerts and annotate them with things like: musicians, instruments, songs, links to lyrics, etc. I'd prefer to do this without writing any code myself. I need to set up a simple (2 table) database for a bunch of not-too-technical users.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |