Bağlı Listeler

Bazı formlarda birbirine bağlı listeler oluşturmak gerekebilir. Bunu Veri Doğrulama ile nasıl yapabiliriz? Şimdi oldukça basit olan bu uygulamaya başlayalım sonrasından onu biraz daha ileri seviyeye taşıyacağız. Tabii taşırken de bayaa eğleneceğiz. Şimdi isterseniz birbirine bağlı olmasını istediğimiz listeleri hazırlayalım. Ben bu listeleri genelde ayrı bir sayfada hazırlıyorum. Bu sayede ana formum görünmesini istemediğim verileri barındırmadığından daha temiz kalıyor. Bir tür 5S. Neyse aşağıdaki tabloyu kopyalayarak Excel’de istediğiniz sayfaya kopyalayın.

Ben bu tabloyu Listeler adlı ayrı bir sayfada oluşturdum. Şimdi de isimleri tanımlayalım. Sırası ile Bölgeler, Akdeniz, DoğuAnadolu (Excel isimlerde boşluk karakteri kullanılmasına izin vermez) ve Ege adlı isimleri tanımladım. Sonrasında aşağıdaki gibi bir tablo oluşturalım.

Veri Doğrulama Bağlantılı Listeler Örneği

Şimdi buradaki örnekte gözüken D3 hücresine tıklayın ve veri doğrulama ekranını açarak Liste seçeneğini seçin. Sonrasında Kaynak kısmına gelip F3 tuşuna basarak gelen pencereden Bölgeler ismini seçin. Burada az önce parantez içinde belirttiği bir sorun ortaya çıkıyor. Bölgeler listesinde “Doğu Anadolu” var ancak isimler içinde “Doğu Anadolu” ismini oluşturmamıza Excel izin vermemişti. Buna çok dikkat edin. Sonraki adımda bunu çözecek bir yöntem kullanmamız gerek. Şimdi D4 hücresine gelin, yine Veri Doğrulama ekranından listeyi seçin ve kaynak kısmına aşağıdaki formülü yazın. Burada çok kısa bir bilgi vereyim; Excel Veri Doğrulama penceresi içinde yer alan kutucuklara formül yazılması esnasında otomatik tamamlama yapmaz. Bu nedenle ben genelde bu formülleri boş bir hücreye yazdıktan sonra kopyalayarak bu alanlara yapıştırırım:

=DOLAYLI(YERİNEKOY($D$3;” “;””))

Burada YERİNEKOY formülünü daha önceki metin formülleri yazımda anlatmıştım. DOLAYLI ise oldukça basit bir formül burada hızlıca değinelim. DOLAYLI formülü yazdığınız metni metin olarak değil adres veya isim olarak ele alır. Yani burada D3 hücresinde yer alan metin içinde varsa boşluklar kaldırılır. Sonrasında elde edilen metin için bir hüücre adresi, hücre aralığı;veya isim var mı kontrol edilir. Var ise bulunan alan geri döndürülür. Tabii bizim D3 hücremizde veri olmadığından burada Excel girilen formülün hata döndürdüğünü söyleyecek, sorun yok “Evet” tuşuna basarak bu pencereyi kapatalım. Zaten biz D3 hücresine sonradan veri gireceğiz.

Şimdi D3 hücresine gelelim ve yandaki aşağı ok düğmesine basarak bir bölge seçelim. Sonra da D4 hücresine gelerek listeyi açalım ve Şehirleri görelim. Eğer D3 hücresine gelip seçili bölgeyi değiştirirsek alttaki hücredeki liste de kendini güncelleyecektir. Aşağıda kısa bir video ile işlem adımlarını gösterdim.

Bu uygulama çok güzel bir uygulama olmasına rağmen bir kaç sorunu da beraberinde getiriyor.

1. Bölge isimlerinin olduğu liste statik. Bu durumda yeni bir bölge eklediğimizde bu bölgeye ait şehir listesini ve listeyi gösterecek ismi de el ile oluşturmamız lazım.
2. Bölgelerdeki şehir isimlerinin yer aldığı isimler de sabit. Yani herhangi bu listelere veri eklediğimizde ilgili ismin gösterdiği alanı da el ile güncellememiz gerek.
3. Form sayfasında Bölge seçilip ardından şehir seçtiğimizde sonradan bölgeyi değiştirdiğinizde daha önce seçilmiş olan şehir bilgisi silinmez ek olarak Excel hata da vermez. Bu ise hatalı bir sonuca yol açar

Şimdi bir sonraki örneklerde ilk iki sorunun çözümünü bulabilirsiniz. Ancak 3. maddedeki sorun için kesin bir çözüm yok. Ben burada veri doğrulama ile hatalı hücre arka planını kırmızı yaparak kullanıcının dikkatini bu alana çekmeyi sağlıyorum.

Bu uygulama için kullanabileceğiniz örnek dosyaya buradan indirebilirsiniz.

Bu örnekte değişken aralık ve bu aralıkta yer alan seçeneklere göre başka bir aralıktaki değerleri birbirine bağlı olarak iki farklı hücrede liste halinde göstermenin nasıl yapılacağını anlatacağım. Bu ders için daha önceden gösgtermiş olduğum değişken aralık formüllerini kullanacağım. Bu yüzden henüz bu yazımı okumadı iseniz lütfen buraya tıklayarak önce o yazıyı okuyun.

Önceki örnekten farklı olarak 2 tane isim tanımlayacağız. İlk isim bölgelerin olduğu ana liste. İkincisi ise ana listeden seçilen değere göre otomatik olarak ilgili aralığı bularak geri döndüren isim. İlk listedeki değere göre arama yapılacağı ve değişken bir aralık olacağı için bu formül biraz karışık bir formül olacak. O yüzden çok dikkatli olun.

Anlatacağım formüllerde yer alan Sayfa1’i formun yer aldığı sayfa adi, sayfa2’yi ise listelerin yer aldığı sayfa adı ile değiştirmeyi de sakın unutmayın.

İlk formülümüz çok kolay. zaten daha önce yapmış olduğumuz bir örnekti. Benim bölgeler listem B3’ten başlıyor:

=KAYDIR(‘Sayfa2’!$B$3;1;0;BAĞ_DEĞ_DOLU_SAY(‘Sayfa2’!$B:$B)-1;1)

Bu formülü isim tanımlama penceresinde oluşturacağımız Bölgeler adlı ismin Başvuru Yeri kutusuna yazalım.

Ne yapmak istediğimi kısaca anlatayım. Öncelikle kullanıcı menüdeki ilk açılır pencereden bir seçenek seçecek. Bu seçeneği ben listelerimin yer aldığı 2. sayfada aratıp hangi sütunda yer aldığını bulacağım, sonra bu sütunda toplamda kaç tane satır olduğunu hesaplayıp bir aralık bulacağım, en son bulduğum bu değerleri bir KAYDIR formülünde yerlerine yazacağım ki o aralık otomatik olarak oluşsun Aşağıdaki resimde ne yapmak istediğimi kısaca anlatmaya çalıştım.

Örneği yapmak için önceki sayfada vermiş olduğum tabloyu kopyalayın lütfen. Sonrasında ilk formülümüz olan aranan değeri bir satır boyunca bulma formülümüzü yazalım (Kaçıncı formülü için buraya bakabilirsiniz):

=KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0)

Benim örnek dosyamda “Doğu Anadolu” F sütununda yani 6. sütunda yer aldığından 6 rakamını geri döndürüyor. Sonrasında 6. sütunun adresini elde etmemiz gerek. Bunu İNDİS formülü ile yapabiliriz (İndis formülü burada anlatılıyor):

=İNDIS(‘Sayfa2’!$A:$XFD;0;KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0))

Bu formül ile sayfanın 6. sütununu tamamen geri döndürdük. Aslında burada başka ve fakat çok daha karmaşık bir formül daha kullanabiliriz ancak İNDİS çok daha kısa ve anlaşılır bir formül. Sonrasında bu sütunda kaç tane satıra veri girildiğini bulacağız (Bağ_Değ_Dolu_Say formülünü burada bulabilirsiniz):

=BAĞ_DEĞ_DOLU_SAY(İNDIS(‘Sayfa2’!$A:$XFD;0;KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0)))

Başlık satırı ile beraber 16 adet satır var. Şimdi bu bulduklarımızı kaydır formülüne koyalım (Kaydır formülünü bu yazımda anlatmıştım):

=KAYDIR(‘Sayfa2’!$A$3;1;KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0)-1;
BAĞ_DEĞ_DOLU_SAY(İNDIS(‘Sayfa2’!$A:$XFD;0;KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0)))-1;1)

Çok uzun bir formül oldu. Kısaca yaptığımız Listelerin yer aldığı 2. sayfadaki A3 hücresini alıyoruz, başlık nedeni ile bir satır aşağıya kaydırıyoruz, form sayfasında aradığımız değer olan Doğu Anadolu ifadesini bulduğumuz sütun sayısının bir eksiği kadar kaydırıyoruz ve bu sütunda bulunan dolu hücre sayısı kadar yükseklikte bir aralığı, başlık satırını çıkarak geri döndürüyoruz.

“Şehirler” adında yeni bir isim tanımlayarak “Başvuru Yeri” kutusuna bu formülü yapıştıralım. Artık listemize yeni elemanlar eklediğimizde sürekli yeni isim oluşturmak durumunda kalmayacağımız gibi tek yapmamız gereken ana listemize eklenmiş yeni madde ile aynı ada sahip yeni bir listeyi oluşturmak olacaktır. Bu formül ile alt listelere de istediğimiz kadar ekleme de yapabiliriz. Yani dışarıdan ek bir müdahale gerektirmeyen hazırla ve unut tarzı formlar oluşturmuş oluruz.

Burada tek bir sıkıntı olabilir o da 16.384 adetten fazla liste ihtiyacıdır. Ancak bunun pek olası olmadığını düşünüyorum.

Örnek dosya için buraya tıklayın lütfen. Bu dosyada Koşullu biçimlendirme ile bir önceki sekmede anlatmış olduğum 3. sorun için çözümü de ekledim. Şehir hücresine tıklayarak koşullu biçimlendirme ile ilgili formülü görebilirsiniz. Aşağıda konu ile ilgili bir uygulama örneğinin videosunu da ekliyorum.