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 Ź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

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

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

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

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

Jeśli wolisz wersję wideo – oto ona: