EXCEL - LISTE DÉROULANTE DES VILLES SELON LE CODE POSTAL SAISI

► Receive the Excel file of the video + complete exercises (with Excel topic and answer keys) from the Excel training course for FREE: https://cours.excel-en-ligne.fr ► Link to Excel training courses: https://aidebtscgo.kneo.me/shop/categ... ▬▬▬ 📝 VIDEO SUMMARY ▬▬▬▬▬▬▬ In this video, I explain how to create a drop-down list of cities based on the entered postal code. The goal is for the drop-down list of cities to adapt and only display the cities in the entered postal code. To do this, we need two elements: A sheet with a cell to enter the desired postal code, with the location of the drop-down list of cities that fall within that postal code next to it. - An extraction file with two columns: Cities and corresponding postal codes. We will assign a row number to each city using the Excel formula ROWS. = If the entered postal code is the same as the postal code in the row of our list of postal codes, then the corresponding row number will appear in the result. = We now need to group all the results (and therefore the row numbers) that correspond to the cities of the entered postal code. To do this, we use the SMALL.VALUE formula, which allows us to group the smallest values ​​in order (1st, 2nd, 3rd, etc.) at the top of the column. In our case, the SMALL.VALUE formula allows us to group at the top of our column all the row numbers that correspond to cities with the same postal code as the one entered. = We now have the row numbers corresponding to the cities of the entered postal code: we can therefore use the Excel formula INDEX to display the name of the city (using the list of cities and the row number). Now we need to create the name of the drop-down list with the list of cities obtained. Caution: This list varies depending on the number of cities in the entered postal code. Therefore, we need to adjust its size using the Excel formulas OFFSET and COUNT. The OFFSET formula will allow us to adjust the height of the drop-down list. This height will be found using the COUNT formula, which will count the number of cities corresponding to the entered postal code. All that's left is to create the drop-down list in the dedicated cell to the right of the cell in which the postal code is entered. ----------------------------------------------------------------------- #Excel #ExcelFormula #ExcelTraining ► Website: https://excel-en-ligne.fr/ ► Facebook:   / axelrobinformations   EXCEL - DROP-DOWN LIST OF CITIES ACCORDING TO THE ENTERED POSTAL CODE:    • EXCEL - LISTE DÉROULANTE DES VILLES SELON ...