Veri doğrulama listesinde boş hücreler gözükmesin

Katılım
20 Eylül 2006
Mesajlar
74
Excel Vers. ve Dili
Excel 2013 TR
Arkadaşlar,
Örneğin;
alt alta 5 hücreye a,b,c,d ve e şeklinde veriler girdik. Ad tanımlamadan ise bu 5 hücreyide kapsayan alt alta 10 hücre seçerek deneme isminde bir ad tanımladık.

Bir hücreyi seçerek, veri doğrulamadan liste diyerek =deneme formülü ile listemizi oluşturduk. Ancak hücrede açılan listede boş olan hücrelerle birlikte gözüküyor. Oysaki istediğim kaç hücre dolu ise listede sadece dolu hücrelerin gözükmesi.

"Boşluğu yoksay" işaretli olsa da olmasa da değişen bir şey söz konusu değil.

Bu hususta yardımcı olursanız sevinirim.
 
Katılım
31 Ocak 2012
Mesajlar
2,430
Excel Vers. ve Dili
Excel 2010 , Türkçe
Sayfanın adı Sayfa1 olsun. A2:A11 hücrelerine
a,b,c,d,e olarak 5 değer girdiğinizi ve A7 den itibaren de boş bıraktığınızı kabul edelim. A2:A11 alanını seçerek "deneme" ad tanımlamasını yaptınız.
Formüller > Ad yöneticisinden deneme ismini seçerek;
Başvuru yerine
=KAYDIR(Sayfa1!$A$2;0;0;BAĞ_DEĞ_DOLU_SAY(Sayfa1!$A$2:$A$11);1)
formülünü yazın.
Böylelikle dinamik bir alan oluşturulmuş olmaktadır. A7 ye f , A8 e de g değerini girin açılır listede sonucu gözlemleyin.
 
Katılım
20 Eylül 2006
Mesajlar
74
Excel Vers. ve Dili
Excel 2013 TR
Üstadım,
formül için teşekkür ederim öncelikle. Ancak;

veri doğrulamadan =deneme denildiğinde işlem yapılıyor. Fakat asıl yapmak istediğim şudur.

a,b,c,d şeklinde deneme ad tanımlamasını yaptık.
a için 1,2,3,4,5
b için 6,7,8
c için 9,10
d için değer yok
şeklinde listeler oluşturduk ve bunlara bağlı olarak a,b,c,d için farklı ad tanımlarını verdiğiniz formülle tamamladık.

veri doğrulama ile a1 hücresine denemeyi tanımladık. (bu normal çalışıyor.)

(formül ile ad tanımlama yapmadığımız durumda) B2 hücresine =dolaylı(A1) olarak veri doğrulma yaptığımızda; A1 hücresinde seçilen değer a ise b2 hücresinde 1,2,3,4,5, b ise 6,7,8 gibi olurken formül ile yapılan ad tanımlamaları sonrası listelemeyi göremiyoruz. sürekli olarak "kaynak listesi sınırlı bir liste olmalı, ya da tek bir satır yada sütuna yapılan bir başvuru olmalı" şeklinde hata veriyor.

bu konuda yardımcı olur musunuz?

Örnek Dosya linki : http://www.dosyayukle.net/download/923e04b7f7ac89fe3108c5d573ec8893.html
 
Son düzenleme:
Katılım
20 Eylül 2006
Mesajlar
74
Excel Vers. ve Dili
Excel 2013 TR
İki çözümde muhteşem üstadlar. Teşekkür ediyorum yardımlarınız için.
 
Katılım
27 Ağustos 2013
Mesajlar
213
Excel Vers. ve Dili
Office 2019 Pro
Türkçe
Merhabalar . Veri doğrulamada ki boşul silme işlemini yapamadım. Eklediğim dosya da B2:D2 aralığını "deneme"" olarak ad tanımladım. C2 hücresine açılır liste şeklinde veri doğrulama yapmak istediğimde boşlukları nasıl yok edebilirim. Yukarıda kaydır. Sakman26 nın çözümü eğer dolu hücreler arasında boşluk varsa işe yaramıyor.


 

Ekli dosyalar

Katılım
6 Mart 2005
Mesajlar
6,238
Excel Vers. ve Dili
Excel Vers. ve Dili:
Office 2016 TR 64 Bit
listenize göre boşluk olmayan yeni bir liste oluşturarak veri doğrulamala ilişkilendirmeli veya makro kullanmalısınız.
 
Katılım
6 Mart 2005
Mesajlar
6,238
Excel Vers. ve Dili
Excel Vers. ve Dili:
Office 2016 TR 64 Bit
C2 Hücresine kopyalayınız.Aşağı doğru çoğaltınız.Tekrarsız ve boşluksuz B sütununudaki listenizi yeniden oluşturur.
Kod:
=EĞERHATA(İNDİS($B$2:$B$200;KAÇINCI(0;İNDİS(EĞERSAY($C$1:$C1;$B$2:$B$200)+($B$2:$B$200=""););0));"")
 
Katılım
27 Ağustos 2013
Mesajlar
213
Excel Vers. ve Dili
Office 2019 Pro
Türkçe
C2 Hücresine kopyalayınız.Aşağı doğru çoğaltınız.Tekrarsız ve boşluksuz B sütununudaki listenizi yeniden oluşturur.
Kod:
=EĞERHATA(İNDİS($B$2:$B$200;KAÇINCI(0;İNDİS(EĞERSAY($C$1:$C1;$B$2:$B$200)+($B$2:$B$200=""););0));"")

Evet bu formül aradaki boşlukları kaldırıyor ama veri doğrulama kısmında nasıl kullanacağım? Veri doğrulama da c sütununu kaynak olarak seçtiğimde yine boşluk oluşuyor.
Veri doğrulamada dinamika ralık oluşturamaz mıyız?
 
Katılım
6 Mart 2005
Mesajlar
6,238
Excel Vers. ve Dili
Excel Vers. ve Dili:
Office 2016 TR 64 Bit
Ad tanımlamada(formüller sekmesinde) aşağıdaki formülü kullanınız ve veri doğrulamada isimle tanımlayınız.Dinamik aralık oluşturur.
Kod:
=KAYDIR($C$2;;;TOPLA.ÇARPIM(($C$2:$C$200<>"")*($C$2:$C$200<>0)))
 
Katılım
27 Ağustos 2013
Mesajlar
213
Excel Vers. ve Dili
Office 2019 Pro
Türkçe
Ad tanımlamada aşağıdaki formülü kullanınız ve veri dorulamada isimle tanımlayınız.Dinamik aralık oluşturur.
Kod:
=KAYDIR($C$2;;;TOPLA.ÇARPIM(($C$2:$C$200<>"")*($C$2:$C$200<>0)))
Teşekkür ederim şimdi tam istediğim gibi oldu.

Peki c stunu gibi ek sütun oluşturmadan bu 2 formülü ad tanımlamadan birleştirerek yapabilir miyiz?(Yani boşlukların olmadığı yeni bir liste oluşturmak için yeni bir sütun kullanmadan)
 
Katılım
6 Mart 2005
Mesajlar
6,238
Excel Vers. ve Dili
Excel Vers. ve Dili:
Office 2016 TR 64 Bit
Rica ederim.Makro ile olur.Daha başka yöntem varmı bilmiyorum.
 
Katılım
27 Ağustos 2013
Mesajlar
213
Excel Vers. ve Dili
Office 2019 Pro
Türkçe
C2 Hücresine kopyalayınız.Aşağı doğru çoğaltınız.Tekrarsız ve boşluksuz B sütununudaki listenizi yeniden oluşturur.
Kod:
=EĞERHATA(İNDİS($B$2:$B$200;KAÇINCI(0;İNDİS(EĞERSAY($C$1:$C1;$B$2:$B$200)+($B$2:$B$200=""););0));"")
Ayrıca bu formülü söz konusu değerleri alfabetik olarak sıralayacak şekilde düzenlemek mümkün mü?
 
Üst