Note: The other languages of the website are Google-translated. Back to English
Vpiši se  \/ 
x
or
x
Registracija  \/ 
x

or

Kako ustvariti odvisen spustni seznam v Googlovem listu?

Vstavljanje običajnega spustnega seznama v Googlov list je za vas lahko enostavno, včasih pa boste morda morali vstaviti odvisen spustni seznam, kar pomeni drugi spustni seznam, odvisno od izbire prvega spustnega seznama. Kako bi se lahko spoprijeli s to nalogo v Googlovem listu?

Ustvarite odvisen spustni seznam v Googlovem listu


Ustvarite odvisen spustni seznam v Googlovem listu

Naslednji koraki vam bodo morda pomagali pri vstavljanju odvisnega spustnega seznama. Naredite naslednje:

1. Najprej vstavite osnovni spustni seznam, izberite celico, kamor želite postaviti prvi spustni seznam, in nato kliknite datum > Validacija podatkov, glej posnetek zaslona:

2. V izskočil Validacija podatkov pogovorno okno, izberite Seznam iz obsega s spustnega seznama ob Merila in nato kliknite gumb, da izberete vrednosti celic, na podlagi katerih želite ustvariti prvi spustni seznam, glejte posnetek zaslona:

3. Nato kliknite Shrani gumb, je bil ustvarjen prvi spustni seznam. Na ustvarjenem spustnem seznamu izberite en element in nato vnesite to formulo: =arrayformula(if(F1=A1,A2:A7,if(F1=B1,B2:B6,if(F1=C1,C2:C7,"")))) v prazno celico, ki meji na stolpce s podatki, nato pritisnite Vnesite ključ, so bile naenkrat prikazane vse ujemajoče se vrednosti na podlagi prvega elementa spustnega seznama, glejte posnetek zaslona:

Opombe: V zgornji formuli: F1 je prva celica spustnega seznama, A1, B1 in C1 so elementi prvega spustnega seznama, A2: A7, B2: B6 in C2: C7 so vrednosti celic, na katerih temelji drugi spustni seznam. Lahko jih spremenite v svoje.

4. Nato lahko ustvarite drugi odvisni spustni seznam, kliknite celico, kamor želite dodati drugi spustni seznam, in nato kliknite datum > Validacija podatkov Pojdite na Validacija podatkov izberite pogovorno okno Seznam iz obsega od spustnega menija ob Merila in kliknite gumb, da izberete celice formule, ki so ujemajoči se rezultati prvega spustnega elementa, glejte posnetek zaslona:

5. Končno kliknite gumb Shrani in drugi odvisni spustni seznam je bil uspešno ustvarjen, kot je prikazano na sliki spodaj:


Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2019 in 365. Podpira vse jezike. Preprosta namestitev v vašem podjetju ali organizaciji. Vse funkcije 30-dnevnega brezplačnega preskusa. 60-dnevno jamstvo za vračilo denarja.
zavihek kte 201905

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    JAY · 3 months ago
    Hi there. Its been working but for it only appears that only one cell can be formulated.
    I need help 
    For example: in Cell A1, it shows dependent dropdown, but when in cell a2 it does not work.
    Thas what help that i need, From A:A, They have all different selection from the dropdown list. Thanks.

  • To post as a guest, your comment is unpublished.
    GerritB · 2 years ago
    Would it been possible to create pull down menu based of formula?
    I use formula "=SUMIFS(Items!$I:$I,Items!$A:$A,K$1,Items!$D:$D,$D2)" (=for example 150) to find the qty on pallet based on item and container size.
    So I wanted to create pull down menu like qty 1 pallet, qty 2 pallets, qty 3 pallets,.....
    Then user will always order qty of full pallets like 150, 2 x 150, 3 x150, up to 21 x 150.
  • To post as a guest, your comment is unpublished.
    Ana Miguel · 2 years ago
    Hi, I already created a drop down list like this one, what I'd like to know is how i can, when clicking on one option from that list, restrict what appears as options on another cell. For example, in one cell i can choose between laparoscopy and orthopaedics. If I choose orthopaedics then, the other cell which has a list of doctors, should only show the doctors for orthopedy and not all of them. Does anyone know how to do this?
  • To post as a guest, your comment is unpublished.
    Vaibhav Shelate · 2 years ago
    Hii Sir,
    I Want to Create A Dependent Drop Down List Like
    - State(A1):- District(B1):- Taluka(C1) :- Then Information(D1) ,
    So I need your Help .
    Tell How do you do this Type of Dependent Drop down List in Excel or Google Sheet
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hi, Shelate,
      Maybe the below article can solve your problem, please view it:
      https://www.extendoffice.com/documents/excel/5852-excel-multiple-level-drop-down-list.html

      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Guest · 2 years ago
    Now is needed to use: =arrayformula(if(F1=A1;A2:A7;if(F1=B1;B2:B6;if(F1=C1;C2:C7;""))))
    • To post as a guest, your comment is unpublished.
      Marcos L · 2 years ago
      Thank you soooo much!
  • To post as a guest, your comment is unpublished.
    Nick · 3 years ago
    Hi,
    I was wondering if anyone could advise me with this? I've followed the above steps but on step 3 rather than getting:
    "...all the matching values based on the first drop down list item have been displayed at once," I am just seeing one value (the top one from the drop down list).
    My cells are placed slightly differently and the first drop down is filled from another sheet (from anywhere int eh column excluding the top row) so my formula is:

    =arrayformula(if(VLE!D2:D=B11,B12,if(VLE!D2:D=C11,C12:C14,if(VLE!D2:D=D11,D12:D14,if(VLE!D2:D=E11,E12:E16,if(VLE!D2:D=F11,F12:F16,""))))))

    It is correctly displaying the top drop down option for each of (using cell numbers from the step 3 example on this page) A1, B1, and C1 so, again using this page's example, if I select Asia form the F1 drop down, I would get China, but nothing further


    Any suggestions?

    Many thanks! Nick
  • To post as a guest, your comment is unpublished.
    Andrew · 3 years ago
    If the first drop own is on sheet 1 A1, will this work were I want the second drop down as sheet 1 A2, but to have its data range on a separate sheet?
  • To post as a guest, your comment is unpublished.
    Peter · 3 years ago
    Ich bekomm immer die Meldung "Fehler beim Parsen der Formel" wenn ich =arrayformula(if(F1=A1,A2:A7,if(F1=B1,B2:B6,if(F1=C1,C2:C7,"")))) eingebe. Was ist der "Weiter-Schlüsel"? Reicht es nicht wie bei Excel die Formel einzugeben?
    • To post as a guest, your comment is unpublished.
      Geyza · 3 years ago
      Ersetzen Sie den , zu ;
      es hat für mich funktioniert
  • To post as a guest, your comment is unpublished.
    Alexis · 3 years ago
    This is the best explanation I have found, so thank you for that!

    In my situation, I am working with over 200 rows with more being continuously added. Is there an easier/more efficient way to handle this many rows?
    • To post as a guest, your comment is unpublished.
      Lad · 3 years ago
      Yes, if you don't put number index to specify ending cell.
      Like instead of C2:C7 you put C2:C. This will automatically tell the array formula, that you want to consider all values in column "C" starting at "C2".
  • To post as a guest, your comment is unpublished.
    Xained · 4 years ago
    What happens if I want exactly the same dropdown list in F2 as you have in F1 ?
    I tried, and it doesn't overwrite D1:D7 .. meaning D1:D7 is only available once.
    I need something else, double dropdown, but on many-many lines .. guess I must use another system then .. not sure which ..
    • To post as a guest, your comment is unpublished.
      skyyang · 4 years ago
      Hi, Xained,
      This method can only applied to one cell, if you have some good ways for applying this to multiple cells, please comment here.

      Thank you!
      • To post as a guest, your comment is unpublished.
        Tram · 2 years ago
        Please, guide me how to do that.Thanks
      • To post as a guest, your comment is unpublished.
        Lucas.Gu · 3 years ago
        혹시, 하나의 셀이 아닌 여러셀에 할 수 있는 방법을 알 수 있을까요?
        • To post as a guest, your comment is unpublished.
          kill · 2 years ago
          내말이... 시바 좃빠지게 따라했디만 밑에줄은 안되노..