View original (French)

Excel dropdown list formula

Solved
filousaxo Posted messages 148 Registration date   Status Member Last intervention   -  
filousaxo Posted messages 148 Registration date   Status Member Last intervention   -

Hello,

In an Excel workbook I have one worksheet named pdg and another named liste villes.

In the sheet liste villes, I have two columns: A for cities and B for postal codes.

In the pdg sheet, cell B5 contains a formula that looks up in the liste villes sheet the city that corresponds to the postal code entered in cell F5 of the pdg sheet.

My formula is: =RECHERCHEX(F5; 'liste villes'!B:B;'liste villes'!A:A;"").

My formula works.

I have a problem because some postal codes are attributed to several cities. Example: 95450 for the cities ableige and commeny, condecourt, la villeneuve st martin and le perchay.

What can I do please?

Is it possible to have a formula that, for example, when I encounter this problem, provides me a drop-down list with all cities assigned to the postal code entered in F5 of the pdg sheet?

Thanks for your feedback,

Best regards,

Filousaxo

7 answers

  1.
    JCB40 Posted messages 3069 Registration date   Status Member Last intervention   479
     
    Hello

    An example of the file with 4:5 lines would be very useful

    Best regards
    0
  2. danielc0 Posted messages 2224 Registration date   Status Member Last intervention   296
     
    Hello everyone, The formula: 
     =FILTRE('liste villes'!A:A;'liste villes'!B:B=F5)
    returns the municipalities that share the same ZIP code. Put this formula, for example, in M3. In the cell that should contain the dropdown list, apply data validation with the formula: 
    =M3#
    [image 1] [image 2] Daniel
    0
  3. Mike-31 Posted messages 18205 Registration date   Status Contributor Last intervention   5 147
     

    Hello,

    Try this array formula in the PDG tab in cell B5; you’ll need to confirm it by pressing CTRL, SHIFT, and ENTER at the same time.

    If you enter it correctly, the formula will appear between these braces { }

    =IFERROR(INDEX('liste villes'!$B:$B;SMALL(IF('liste villes'!$A$3:$A$30=F$5;ROW('liste villes'!$B$3:$B$30));ROWS(I$3:I3)));"")

    Once the formula is validated, it will spill over across multiple rows and all cities that meet the criterion will appear.

    Best regards

    See you,
    Mike-31

    I am responsible for what I say, not for what you understand...

    0
  4. via55 Posted messages 14395 Registration date   Status Member Last intervention   2 759
     

    Hello everyone

    A more classic way to have a dynamic dropdown list with the OFFSET function, which has the advantage of working in older Excel versions

    Regards

    Via

    0
    1. danielc0 Posted messages 2224 Registration date   Status Member Last intervention   296
       

      Hello everyone,

      However, if the list of cities is not sorted, it may be interesting to do so using:

       =SORT(FILTER('cities list'!A:A;'cities list'!B:B=E2))

      On liste.villes :

      Validation list :

      Daniel

      0
    2. filousaxo Posted messages 148 Registration date   Status Member Last intervention   9
       
      Hello, Thank you for your feedback. However, I do not master or understand where I should place "data validation." Here is an example copy of my file: https://cijoint.org/r/EUcycYzj#VFlfzj+CKbSYLn/1yLvxGhJOIDzcdDiQHXBwrQ3bWIs= Postal code 95450, seen in the example in the "pdg" tab and in cell F5, is among the postal codes that bother me because it has multiple assigned cities. Same for 95130. Thank you in advance for kindly looking at my problem. Best regards, Filousaxo
      0
  6. via55 Posted messages 14395 Registration date   Status Member Last intervention   2 759
     
    Hello,

    Your file with the formula in the Name Manager (Formulas tab or Ctrl + F3) for the name villes (based on a CP-sorted list on your sheet liste villes) and the data validation of cell B5 on the PDG sheet (validation is under Data tab > Data Tools > Data Validation).

    Link: https://cijoint.org/r/H8XJSN4_#IZlACZwqu81UuD5F5KO+tcebkHoJ+mavg7pqa9sJfgY=
    Dropdown in cell B5 that only shows the cities for the CP entered in F5; if CP is unique, the dropdown shows only one city.

    There cannot be both a formula in the same cell that would show only one city and a dropdown for the case of multiple CP.

    Cdlmnt

    Via
    0
  7. danielc0 Posted messages 2224 Registration date   Status Member Last intervention   296
     
    Hello everyone,

    I just created the dropdown list without changing anything else:

    https://cijoint.org/r/V_H4FtPP#AdEHwJl4wYLsL/eI+Tu41ffwqODlb+dyPwuvldocZ7Q=

    For the dropdown list, select B5, click on the "Data" tab, click on "Data validation" and enter the following values:

    Valide.

    Daniel
    0
    1. filousaxo Posted messages 148 Registration date   Status Member Last intervention   9
       

      Hello Via55 and hello Danielc0,

      Thank you for your feedback and your solutions.

      I would like to understand how you did it

      I don’t understand your formula that selects cities according to the postal codes.

      How did you set the F5 key to activate the "arrow" to scroll the list, please.

      Best regards,

      Filousaxo

      0
  8. danielc0 Posted messages 2224 Registration date   Status Member Last intervention   296
     
    Regarding the arrow, it positions itself when you create the data validation as I explained in message #8. If there is something you don’t understand, say so.

    For the formula:
    =FILTRE('liste villes'!A2:A10000;'liste villes'!B2:B10000=F5;"")
    it retrieves the cities from column A whose postal codes (column B) are equal to F5.

    Daniel

    PS. for the data validation, I indicated "=Z5#"
    the # indicates that all results from the formula above are taken regardless of their number. Thus, in your example, Z5# represents:
    0
    1. filousaxo Posted messages 148 Registration date   Status Member Last intervention   9
       

      Hello again,

      Yes, that works, I think I had forgotten something.

      Thank you, Danielc0 and Via55 for your solutions.

      Best regards,

      Filousaxo

      0