Soru Veri Doğrulamada Otomatik Tamamlama

[pcaglar]

Merhaba veri doğrulama yapıp içinde arama yapmak istedim

bu kodu kullandım fakat

=KAYDIR(Sayfa2!$B$1;(KAÇINCI(A5&"*";Sayfa2!$B$1:$B$15;0))-1;;EĞERSAY(Sayfa2!$B$1:$B$150;A5&"*"))

A5 satırında zım yazıp aratınca zımba içeren satırların arasındaki diğer satırlarıda alıyor. Sadece zım içerenleri nasıl alabilirim?
Teşekkürler.

İndir Teklif (1)

Dosyayı indir Teklif (1)

dosya.co

 

[Orion1]

Yıldızları kaldırıp denermisiniz.

 

[52779]

Selamlar,

=KAYDIR(Sayfa2!$B$1;(KAÇINCI(A5&"*";Sayfa2!$B$1:$B$15;0))-1;;EĞERSAY(Sayfa2!$B$1:$B$150;A5&"*"))

A5 satırında zım yazıp aratınca zımba içeren satırların arasındaki diğer satırlarıda alıyor. Sadece zım içerenleri nasıl alabilirim?
Teşekkürler.

Diğer satırları alması çok doğal....
Veri doğrulama açılır listede kullandığınız formülün DÜZGÜN çalışması için. Sayfa2!$B$1:$B$150 deki Referans Listenin, SIRALI liste olması gerekir...
Referans Listenizi (A dan Z ye ya da Z den A ya) Sıralandırdığınızda Formül düzgün çalışacaktır.

Şu haliyel formülü incelediğinizde,

KAÇINCI(A5&"*";Sayfa2!$B$1:$B$15;0) : A5 hücresinde zım yazmakta olduğundan, zım ile başlayanların ilk sırası Listede 9. sıradadır.
EĞERSAY(Sayfa2!$B$1:$B$150;A5&"*") : zım ile başlayanların adedi 4 tür.
Bu durumda formül listedeki 9. sıradan itibaren 4 satırlık alanı değerlendirmeye alır. Yani,
=KAYDIR(Sayfa2!$B$1;(KAÇINCI(A5&"*";Sayfa2!$B$1:$B$15;0))-1;;EĞERSAY(Sayfa2!$B$1:$B$150;A5&"*"))
formülünde değerleri yerine koyarsak,
=KAYDIR(Sayfa2!$B$1;(9)-1;;4) = =KAYDIR(Sayfa2!$B$1;8;;4) = Sayfa2!$B$9:$B$12 olacaktır...

Formülünüzdeki ilk Sayfa2!$B$1:$B$15 de diğeri gibi Sayfa2!$B$1:$B$150 olmalı....

 

[pcaglar]

Yıldızları kaldırıp denermisiniz.

formül hatalı oldu. Çalışmadı.

Selamlar,


Diğer satırları alması çok doğal....
Veri doğrulama açılır listede kullandığınız formülün DÜZGÜN çalışması için. Sayfa2!$B$1:$B$150 deki Referans Listenin, SIRALI liste olması gerekir...
Referans Listenizi (A dan Z ye ya da Z den A ya) Sıralandırdığınızda Formül düzgün çalışacaktır.

Şu haliyel formülü incelediğinizde,

KAÇINCI(A5&"*";Sayfa2!$B$1:$B$15;0) : A5 hücresinde zım yazmakta olduğundan, zım ile başlayanların ilk sırası Listede 9. sıradadır.
EĞERSAY(Sayfa2!$B$1:$B$150;A5&"*") : zım ile başlayanların adedi 4 tür.
Bu durumda formül listedeki 9. sıradan itibaren 4 satırlık alanı değerlendirmeye alır. Yani,
=KAYDIR(Sayfa2!$B$1;(KAÇINCI(A5&"*";Sayfa2!$B$1:$B$15;0))-1;;EĞERSAY(Sayfa2!$B$1:$B$150;A5&"*"))
formülünde değerleri yerine koyarsak,
=KAYDIR(Sayfa2!$B$1;(9)-1;;4) = =KAYDIR(Sayfa2!$B$1;8;;4) = Sayfa2!$B$9:$B$12 olacaktır...

Formülünüzdeki ilk Sayfa2!$B$1:$B$15 de diğeri gibi Sayfa2!$B$1:$B$150 olmalı....

Teşekkürler uzun uzun anlatmışsınız. Fakat nasıl çalıştığını anlamıştım zaten formülün. F9 yapınca aldığı değerleri görebiliyorum ve mantığını çözdüm. Fakat bu formül sonuca ulaştırmadı. Adan Z yede oluşturabileceğim bir liste değil.

Hala bir çözüm önerisine açığım.

 

[Korhan Ayhan]

Tablonuzda olan veri doğrulama hücreleri için yardımcı sütunlar oluşturmalısınız. Paylaştığınız dosyada A5:A18 arası için 14 satır var. Bu da 14 sütun demektir. Çok mantıklı değil ama çözüme gitme yolunda bu yöntemi kullanabilirsiniz.

Aşağıdaki DİZİ formül A5 hücresinde yazdığınız "zım" ifadesi geçen verilerin listesini size verecektir.

İstediğiniz hücreye uygulayıp alt hücrelere sürükleyiniz.

=EĞERHATA(İNDİS(Sayfa2!$B$2:$B$1000;KÜÇÜK(EĞER(ESAYIYSA(MBUL($A$5;Sayfa2!$B$2:$B$1000));SATIR(Sayfa2!$B$2:$B$1000)-1);SATIR(A1)));"")

 

[pcaglar]

Tablonuzda olan veri doğrulama hücreleri için yardımcı sütunlar oluşturmalısınız. Paylaştığınız dosyada A5:A18 arası için 14 satır var. Bu da 14 sütun demektir. Çok mantıklı değil ama çözüme gitme yolunda bu yöntemi kullanabilirsiniz.

Aşağıdaki DİZİ formül A5 hücresinde yazdığınız "zım" ifadesi geçen verilerin listesini size verecektir.

İstediğiniz hücreye uygulayıp alt hücrelere sürükleyiniz.

=EĞERHATA(İNDİS(Sayfa2!$B$2:$B$1000;KÜÇÜK(EĞER(ESAYIYSA(MBUL($A$5;Sayfa2!$B$2:$B$1000));SATIR(Sayfa2!$B$2:$B$1000)-1);SATIR(A1)));"")

Çok teşekkür ederim, çok işime yaradı. Başka bir sayfada yapıştırdım oradan veri doğrulama ile aldım. Birde
=EĞERHATA(Sayfa2!$A2:$A500;YOKSAY())
bu kodu kullanarak aldığım için alttaki boşluklarıda alıyor. Ona da pratik bir kod var mı, sadece 4 değer çıktıysa listede 4 değer göstersin ? Uzun bir şeyse gerek yok uğraşmayın yazmaya sağolun.

 

[Korhan Ayhan]

Boşluklar için dinamik alan tanımlaması kullanabilirsiniz.

Forumda örnek vardı. Arama yaparsanız ulaşabilirsiniz.

 

[pcaglar]

Boşluklar için dinamik alan tanımlaması kullanabilirsiniz.

Forumda örnek vardı. Arama yaparsanız ulaşabilirsiniz.

Hocam gece farkedememişim. A5 de zım yazıp aratınca çalışıyor tamam ama sayfa 1 de A6'da da A7'de de sırayla arama yapmam gerekli. Ozaman bu formül çalışmıyor. Ona ne yapmak lazım?
Teşekkürler

 

[Korhan Ayhan]

#5 nolu mesajımda bu durumdan bahsetmiştim. Sanırım dikkatli okumadınız.

 

[YUSUF44]

Daha kullanışlı olduğunu düşündüğüm bir dosya hazırladım. A5:A16 arasında hücre seçtiğinizde çıkan userformda arama yaparak sayfaya ekleyebilirsiniz:


Userformla sayfaya veri ekleme sayfayı xlsx ve pdf olarak kaydetme

 

[pcaglar]

#5 nolu mesajımda bu durumdan bahsetmiştim. Sanırım dikkatli okumadınız.

Gece anlamamıştım ne demek istediğinizi. Şimdi anladım hocam. O çok kötü bir seçenek o halde. 100 satır kullansam. 100 sütun hazırlamam gerekli.

Daha kullanışlı olduğunu düşündüğüm bir dosya hazırladım. A5:A16 arasında hücre seçtiğinizde çıkan userformda arama yaparak sayfaya ekleyebilirsiniz:

Hücreye tıklayınca userformla arama ve sayfaya ekleme

Elinize sağlık zahmet etmişsiniz. Bunda da makroya izin ver çıkıyor hocam. O biraz sıkıntı işte. Güvenilmez bir yapısı var makroların. Zararlı kod çalıştırılabiliyor. Ben müşteriye yollayınca rahatsız edebilir makro olması. Fakat yeni bir şey öğrenmiş oldum. Kendi işimde kullanırım sağolun.

Sanırım makrosuz daha güzel bir örneği olsa Korhan bey fikir verirdi.

İndir Teklif Kopya

Dosyayı indir Teklif Kopya

dosya.co

bende böyle bir alternatif bulmuştum belki birinin işine yarar. A3' e yazılan kelimeyi alttaki kutucuklarda seçebiliyoruz. Ama A20 ye ekleyeceğim ürünü A3 e yazıp aşağı inip seçmekte pratik değil.

 

[YUSUF44]

Kendi orjinal dosyanızı neden müşteriye gönderesiniz ki? Sadece teklif sayfasını düz excel ya da pdf olarak göndermeniz daha uygun olmaz mı?

 

[pcaglar]

Kendi orjinal dosyanızı neden müşteriye gönderesiniz ki? Sadece teklif sayfasını düz excel ya da pdf olarak göndermeniz daha uygun olmaz mı?

excel i hazırlayıp gönderirken sadece sayfa1 i yollamam gerekir. Pdf e dönüştürünce sıkıntı yok fakat excel isterse karşı taraf, taşı ve kopyala ile yeni excele aktarınca sayfa 1 i, makroyla aktarıyor. Makroyu temizlemek her defasında yorucu olur hocam

 

[YUSUF44]

#10. mesajdaki dosyayı değiştirdim, inceleyin lütfen.

 

[pcaglar]

#10. mesajdaki dosyayı değiştirdim, inceleyin lütfen.

pdf yap çalışıyor fakat xlsx yapa tıklayınca A Firması 26082021.xlsm dosyası oluşturup run time error '1004' ..... ögesini bulamadık Taşınmış yeniden adlandırılmış silinmiş olabilir mi ? uyarısı çıkıyor. Oluşan A firması dosyası da orjinal dosyanın aynısı zaten hocam.

 

[YUSUF44]

Makro önce dosyanın bir kopyasını oluşturuyor. Sonra o dosyayı xlsx olarak kaydediyor. Sonra da kopya xlsm dosyasını siliyor. Öncesinde mükerrer olmaması için klasörde aynı addaki xlsx dosyayı silmesi gerekiyor. Sizde henüz o dosya olmadığı için o hatayı verdi ve hata verdiği için de dosyanın devamını yapamadı. O hatayı vermemesi için xlsxyap makrosunu aşağıdakiyle değiştirip dener misiniz?

Sub xlsxyap()
    Application.DisplayAlerts = False
    Dim sPath As String
    sPath = ThisWorkbook.Path & Application.PathSeparator & [B1] & " " & Format(Date, "DDMMYYYY") 'no extension!
    
    ThisWorkbook.SaveCopyAs sPath & ".xlsm"
    If Dir(sPath & ".xlsx") <> "" Then
        Set eski = Application.Workbooks.Open(sPath & ".xlsx")
        eski.Close
        Kill sPath & ".xlsx"
    End If
    Dim Wb As Workbook
    Set Wb = Application.Workbooks.Open(sPath & ".xlsm")
    Wb.Sheets(1).Shapes.Range(Array("Button 1")).Delete
    ActiveSheet.Shapes.Range(Array("Button 2")).Delete
    Wb.SaveAs sPath & ".xlsx", xlOpenXMLWorkbook
    Wb.Close SaveChanges:=False
    Kill sPath & ".xlsm"
    Application.DisplayAlerts = True
End Sub

 

[Korhan Ayhan]

Sayfa2 de ki ürün listeniz kaç satır olabilir? Ona göre makrosuz alternatif bir çözüm önerebilirim.

Hazırladığım dosyayı paylaşıyorum. Eğer ürün listesi çoğalırsa bir tık yavaşlama söz konusu olacaktır. 2000 satırlık bir veri listesi olabileceğini varsaydım.

Sayfa2 de F sütunu yardımcı alan olarak kullanılmıştır. İşlemlerde bu sütun dikkate alınmıştır.

Harici Link (Silinebilir) ; https://dosyam.org/2EL9/Teklif.xlsx

 

[pcaglar]

Makro önce dosyanın bir kopyasını oluşturuyor. Sonra o dosyayı xlsx olarak kaydediyor. Sonra da kopya xlsm dosyasını siliyor. Öncesinde mükerrer olmaması için klasörde aynı addaki xlsx dosyayı silmesi gerekiyor. Sizde henüz o dosya olmadığı için o hatayı verdi ve hata verdiği için de dosyanın devamını yapamadı. O hatayı vermemesi için xlsxyap makrosunu aşağıdakiyle değiştirip dener misiniz?

Sub xlsxyap()
    Application.DisplayAlerts = False
    Dim sPath As String
    sPath = ThisWorkbook.Path & Application.PathSeparator & [B1] & " " & Format(Date, "DDMMYYYY") 'no extension!
   
    ThisWorkbook.SaveCopyAs sPath & ".xlsm"
    If Dir(sPath & ".xlsx") <> "" Then
        Set eski = Application.Workbooks.Open(sPath & ".xlsx")
        eski.Close
        Kill sPath & ".xlsx"
    End If
    Dim Wb As Workbook
    Set Wb = Application.Workbooks.Open(sPath & ".xlsm")
    Wb.Sheets(1).Shapes.Range(Array("Button 1")).Delete
    ActiveSheet.Shapes.Range(Array("Button 2")).Delete
    Wb.SaveAs sPath & ".xlsx", xlOpenXMLWorkbook
    Wb.Close SaveChanges:=False
    Kill sPath & ".xlsm"
    Application.DisplayAlerts = True
End Sub

Teşekkürler hocam buna da bakıcam. Farklı bir tecrübe olur bana. Makro tarafını hiç bilmediğimden düzenlemek gerektiğinde sorun yaşarım diye biraz korkarak yaklaşıyordum bu çözüme.

Sayfa2 de ki ürün listeniz kaç satır olabilir? Ona göre makrosuz alternatif bir çözüm önerebilirim.

Hazırladığım dosyayı paylaşıyorum. Eğer ürün listesi çoğalırsa bir tık yavaşlama söz konusu olacaktır. 2000 satırlık bir veri listesi olabileceğini varsaydım.

Sayfa2 de F sütunu yardımcı alan olarak kullanılmıştır. İşlemlerde bu sütun dikkate alınmıştır.

Harici Link (Silinebilir) ; https://dosyam.org/2EL9/Teklif.xlsx

Hocam bilginizi konuşturmuşsunuz. Bende şu formülü kullanıp, doğru çalışması içinde

=KAYDIR(Sayfa2!$B$1;(KAÇINCI(A5&"*";Sayfa2!$B$1:$B$150;0))-1;;EĞERSAY(Sayfa2!$B$1:$B$150;A5&"*"))

listeyi formülle a dan z ye sıralatacaktım. Aklıma bu gelmişti çözüm olarak. Ama sizinki tam aradığım çözüm olmuş. Elinize emeğinize sağlık. Oturup anlaması kaldı bana da