fbpx

Dynamiczne źródło listy rozwijanej między arkuszami

12.12.2017 | Sprawdzanie poprawności (walidacja)

Czyli co zrobić, aby źródło listy rozwijanej w INNYM ARKUSZU samo się powiększało?

Niedawno pokazywałam Wam sposób na to, aby można było dopisać wartości do źródła listy rozwijanej w komórce i żeby ta lista automatycznie aktualizowała się o te wprowadzone wartości. Sposób był prościutki – trzeba było oprzeć źródło listy na obiekcie tabela i samo poszło.

Szybko jednak złożyliście reklamacje ;), że zaprezentowany przeze mnie sposób działa tylko w arkuszu, w którym są zarówno lista jak i jej źródło. Prawda jest jednak taka, że najczęściej źródło listy umieszczamy w innym arkuszu (najczęściej dodatkowo ukrytym, ale to akurat nie ma tutaj znaczenia). I pokazany przeze mnie sposób już wtedy nie działał. Czyli sytuacja była identyczna, jak w starszych wersjach Excela (sprzed 2010 – pamiętacie?:>), że nie można było wskazać źródła listy rozwijanej, która była w innym arkuszu. Po prostu Excel, z poziomu sprawdzania poprawności (mechanizm listy rozwijanej) nie chciał przejść do innego arkusza.

Oczywiście użytkownicy szybciutko znaleźli sposób na obejście tego ograniczenia… Dokładnie ten sam sposób zastosujemy teraz.

Zachęcam do lektury!

Formatka tym razem wygląda nieco inaczej, ponieważ źródło listy rozwijanej jest w innym arkuszu (Źródło) niż formatka (Dane):

Formatka, arkusz Dane

Formatka, arkusz Dane

Formatka, arkusz Źródło

Formatka, arkusz Źródło

Formatkę mamy, zatem do dzieła!

Przygotowanie źródła listy rozwijanej – definiowanie nazwy

Cały trik polega na… nazwaniu zakresu, który ma być źródłem do listy rozwijanej. Dokładnie tak samo, jak w starszych wersjach Excela. Tam też to działało.

Może się to wydawać trochę dziwne, ponieważ nasze źródło już jest nazwane! Jest to bowiem obiekt tabela, który sam z siebie ma przecież nazwę. Problem jednak polega na tym, że jak tę nazwę wpiszemy do źródła listy rozwijanej – nic z tego nie wyjdzie.

Jedyną metodą, która tutaj zadziała jest nazwanie kolumny tabeli, która ma być szukanym źródłem. Jak to zrobić? Najprostsza metoda nadawania nazw w tym wypadku nie zadziała, ponieważ jak zaznaczymy naszą tabelę, to w polu nazwy pojawi się jej nazwa. A wiemy, że tejże nie możemy wpisać do sprawdzania poprawności.

Trzeba więc posłużyć się menedżerem nazw. Aby do niego wejść należy skorzystać ze skrótu klawiszowego Ctrl + F3, albo z menu wybrać Formuły/ Menedżer nazw. Pojawi się następujące okienko, w którym należy kliknąć przycisk Nowy:

Menedżer nazw

Menedżer nazw

Po kliknięciu tego przycisku przejdziemy do okienka, w którym będziemy definiować nazwę zakresu, którą użyjemy w źródle listy rozwijanej. Ja ten zakres nazwę Zrodlo, a odwoływać się będzie on do zakresu tbZrodlo[Źródło listy] (opis ten uzyskasz po po prostu poprzez wskazanie zakresu B4:B10):

Tworzenie nowej nazwy

Tworzenie nowej nazwy

Po naciśnięciu OK nazwa została nadana i wskoczy ona do okienka Menedżera nazw. I to tyle. Nazwa nadana, więc możemy iść do kolejnego etapu. Teraz będzie już z górki 😉

Tworzenie listy

Samo tworzenie listy wygląda identycznie, jak do tej pory.

1. Najpierw zaznaczamy komórkę (lub zakres), w którym chcemy mieć listę

2. Następnie idziemy do menu Dane/ Sprawdzanie poprawności i wybieramy jako wartość dozwoloną Lista, a jako jej źródło wpisujemy utworzoną przed chwilą nazwę (koniecznie pamiętając o znaku równości!!!):

Tworzenie listy rozwijanej

Tworzenie listy rozwijanej

Efektem będzie oczywiście lista rozwijana, ale nie byle-jaka! Jeśli teraz dopiszemy jakiś nowy dział do listy z działami w arkuszu Źródło – nasza lista rozwijana się o ten dział powiększy!

Taki jest efekt końcowy:

Wynik

Wynik

Fajne? Mam nadzieję, że tak i że pomogłam.

Jeśli wolisz wersję wideo – oto ona:

 

 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

9 komentarzy

  1. Cześć,
    Czy jest możliwość utworzenia takiej listy która działa między powiązanymi plikami?

    Reply
    • Hmmm, trzeba byłoby importować dane jako źródło do listy. Np. za pomocą Power Query.

      Reply
      • Dzięki, niestety działam na starym excelu 2007:) więc nie poszaleję 🙂

        Reply
        • Ech, no niestety te wersje mają znaczenie…. Ale może niedługo się przesiądziesz?:>

          Reply
  2. Cześć!

    Chciałabym w arkuszu X swtorzyć taką listę rozwijaną. Mam też arkusz Y i chciałabym aby wraz ze zmianą wartości w tej liscie w arkuszu X, aktualizowała się ta sama lista w arkuszu Y. Czy jest to możliwe?

    Reply
    • Hej, ja bym tutaj proponowała np. PQ albo zdarzenie w VBA, które na zmianę w komórce aktualizuje listę w innym arkuszu. A PQ też warto byłoby połączyć z jakimś zdarzeniem, żeby „samo” się działo 🙂

      Reply
  3. Dziękuję za fajny sposób na „rosnącą” listę rozwijaną, jednak jak widzę, nie działa to w stronę zmniejszania elementów listy – nazwy znikają ale pozostają puste pola do wyboru. Czy da się to jakoś poprawić?

    Reply
    • Tak, tak się dzieje. Trzeba ręcznie zmniejszyć tabelę. Tabela ma umiejętność powiększania się, natomiast nie umie się sama zmniejszać.

      Reply
  4. Próbuje e wykorzystać funkcje Wyszukaj.Poziomo. Błąd N/D, arkusz nie działa. Robiłem go analogicznie do arkusza z funkcja Wyszukaj.Pionowo. Mozna przesłać ten arkusz, do sprawdzenia?

    Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest