• DİKKAT

    DOSYA İndirmek/Yüklemek için ÜCRETLİ ALTIN ÜYELİK Gereklidir!
    Altın Üyelik Hakkında Bilgi

ad tanımlama ve doğrulama

  • Konbuyu başlatan Konbuyu başlatan mmsez
  • Başlangıç tarihi Başlangıç tarihi
M

mmsez

Katılım
5 Eylül 2007
Mesajlar
79
Excel Vers. ve Dili
2003 türkçe
Merhaba.yaklaşık 2 saattir bakıyorum ve işlem olarak cevap da buldum ama uygulayamadığım bir sorunum var.elimde uzun bir isim listesi ve bu isimlerin referans olduğu başka isimler var.ekte benzer bir dosya hazırladım.teker teker yaparak istediğim sonuca ulaşıyorum fakat bu çok uzun bir iş olacak benim için.bir veri sayfam var burada kişiler ve referansları yazıyor.veri sayfasında da ben kişi listesini doğrulma yöntemi ile seçiyorum ve karşısına da o kişinin referanslarını yine doğrulama yolu ile seçmek istiyorum.Tek tek ad tanımlamak çok zor ve uzun zman alıyor.Bazı konularda yakın cevaplar var ama bu basit örnek üzerinden cevap benim gibi excel fakirlerine daha açıklayıcı olacak sanırım.

şimdiden teşekkürler...
 

Ekli dosyalar

mmsez' Alıntı:
Merhaba.yaklaşık 2 saattir bakıyorum ve işlem olarak cevap da buldum ama uygulayamadığım bir sorunum var.elimde uzun bir isim listesi ve bu isimlerin referans olduğu başka isimler var.ekte benzer bir dosya hazırladım.teker teker yaparak istediğim sonuca ulaşıyorum fakat bu çok uzun bir iş olacak benim için.bir veri sayfam var burada kişiler ve referansları yazıyor.veri sayfasında da ben kişi listesini doğrulma yöntemi ile seçiyorum ve karşısına da o kişinin referanslarını yine doğrulama yolu ile seçmek istiyorum.Tek tek ad tanımlamak çok zor ve uzun zman alıyor.Bazı konularda yakın cevaplar var ama bu basit örnek üzerinden cevap benim gibi excel fakirlerine daha açıklayıcı olacak sanırım.

şimdiden teşekkürler...
Genişletmek için tıkla ...
Selam,

"ad" adlı ad için:
Kod: 
=VERİ!$A:$A

"ref" adlı ad için:
Kod: 
=DOLAYLI("VERİ!$B"&KAÇINCI(DATA!$A2;VERİ!$A:$A;0)&":$D"&KAÇINCI(DATA!$A2;VERİ!$A:$A;0)&"")

İyi çalışmalar.
 
Son düzenleme:
Farkındayım saat çok geç ama bana bunu yani ref kodunu nasıl anlatabilirsiniz?
 
mmsez' Alıntı:
kod'un içeriğini sormak istemiştim.
Genişletmek için tıkla ...

.

Kod: 
=INDIRECT("VERİ!$B"&MATCH(DATA!$A2;VERİ!$A:$A;0)&":$D"&MATCH(DATA!$A2;VERİ!$A:$A;0)&"")

Bu formülün mantığı; A sütundaki değere bağlı olarak B sütununda yapılacak veri doğrulama için bir dinamik alan tanımlamamız gerekir.

1. Sütun B'den başlayacağı ve D' sütununda biteciğini biliyoruz bunun için;
("VERİ!$B"&.....
)&":$D"&... satır indeksini bulmamız gerekir. Yani ("VERİ!$B...:$D...) alanının satır indeksleri gerekir.

2. Bunu MATCH(DATA!$A2;VERİ!$A:$A;0) fonksiyonu ile bulmaya çalışıyoruz.

3. Örneğin A2 hücresi B ise ="VERİ!$B"&MATCH(DATA!$A2;VERİ!$A:$A;0)&":$D"&MATCH(DATA!$A2;VERİ!$A:$A;0) formülü VERİ!$B2:$D2 değerini döndürür. Ancak bu değer metin dizesidir. Bunu sayısal değere dönüştürmek için INDIRECT fonksiyonundan yararlanırız.

Böylece formülümüz:

=INDIRECT("VERİ!$B"&MATCH(DATA!$A2;VERİ!$A:$A;0)&":$D"&MATCH(DATA!$A2;VERİ!$A:$A;0)&"") şeklini alır.


.
 
mmsez' Alıntı:
Farkındayım saat çok geç ama bana bunu yani ref kodunu nasıl anlatabilirsiniz?
Genişletmek için tıkla ...

Sayın mmsez,
Önce Örnek dosya ve formüller işinizi gürdü mü görmedi mi? bunu belirtiniz.
Kod: 
=DOLAYLI("VERİ!$B"&KAÇINCI(DATA!$A2;VERİ!$A:$A;0)&":$D"&KAÇINCI(DATA!$A2;VERİ!$A:$A;0)&"")

Bu kodu sadece sizin örnek dosyanıza göre hazırladım. Daha hızlı, pratik, anlaşılır olmasına dikkat ettim. İsteğiniz Makro ile veya başka metotlar ile de yapılabilir. Ancak ben bunu daha uygun gördüm.Dolaylı fonksiyonundan önce Kaçıncı fonksiyonunu anlatayım;
Kod: 
KAÇINCI(DATA!$A[COLOR="Red"]2[/COLOR];VERİ!$A:$A;0)
Data sayfanızın A sütunlarında seçtiğiniz isimler bulunmaktadır. yukarıdaki kodda
A2 deki ismi Veri sayfasının A sütununda arar ve satır no.sunu bulur.
Örneğin A2'de "E" seçili olsun. E veri sayfasının A sütununda 6.satırdadır. Kodlar üzerinden anlatacak olursak;
Kod: 
KAÇINCI([COLOR="Red"]DATA!$A2[/COLOR];VERİ!$A:$A;0)
kodu
Kod: 
KAÇINCI([COLOR="Red"]"E"[/COLOR];VERİ!$A:$A;0)
koduna eşittir. bu da 6 yapar.
Kod: 
KAÇINCI(DATA!$A2;VERİ!$A:$A;0)= 6 ise
Kod: 
=DOLAYLI("VERİ!$B[COLOR="Red"]6[/COLOR]:$D[COLOR="Red"]6[/COLOR]")
olur. Bunu veri doğrulamada liste olarak kullandığınız zaman VERİ!$B6:$D6 aralığındaki hücreleri liste şeklinde kullanılmasını sağlar. Bunlar E1,E2,E3 tür. DATA sayfasının A2 hücresine "ad" için veri doğrulama yaptıktan sonra istediğiniz kadar aşağıya çoğaltınız. Daha sonra hücrelerin içeriğini siliniz. Sonra hücreledeki açılır kutudan istediğiniz ismi şeçiniz. Daha sonra aynı işlemi "ref" için Data sayfasının B2 hücresine yapınız. B sütununda istediğiz hücrenin açılır kutusunu seçtiğiniz zaman, bulunduğunuz satırın A sütunundaki değere ait bilgilerini göreceksiniz. yukarıdaki örneğin A5 'de "D" var ise B5'in açılır listesinde sadece D1,D2,D3 'ü göreceksiniz.
DATA!$A2'deki kırmızı renkli 2 rakamı "ref" için veri doğrulama yaptığınız satır no.su olacaktır. Benzer kodlar hazırlarken ad oluşturduğunuz esnada bulunduğunuz sayfa ve satır önemlidir. Ayrıca DATA!$A2 yerine DATA!$A$2 yazılsa idi formüller yanlş hesap yapacaktı.

Örnek dosyanızı asıl dosyanıza uyarlar iken bu hususlara dikkat ediniz
 
Son düzenleme:
Ergün Güler' Alıntı:
Sayın mmsez,
Önce Örnek dosya ve formüller işinizi gürdü mü görmedi mi? bunu belirtiniz.
Kod: 
=DOLAYLI("VERİ!$B"&KAÇINCI(DATA!$A2;VERİ!$A:$A;0)&":$D"&KAÇINCI(DATA!$A2;VERİ!$A:$A;0)&"")

Bu kodu sadece sizin örnek dosyanıza göre hazırladım. Daha hızlı, pratik, anlaşılır olmasına dikkat ettim. İsteğiniz Makro ile veya başka metotlar ile de yapılabilir. Ancak ben bunu daha uygun gördüm.Dolaylı fonksiyonundan önce Kaçıncı fonksiyonunu anlatayım;
Kod: 
KAÇINCI(DATA!$A[COLOR="Red"]2[/COLOR];VERİ!$A:$A;0)
Data sayfanızın A sütunlarında seçtiğiniz isimler bulunmaktadır. yukarıdaki kodda
A2 deki ismi Veri sayfasının A sütununda arar ve satır no.sunu bulur.
Örneğin A2'de "E" seçili olsun. E veri sayfasının A sütununda 6.satırdadır. Kodlar üzerinden anlatacak olursak;
Kod: 
KAÇINCI([COLOR="Red"]DATA!$A2[/COLOR];VERİ!$A:$A;0)
kodu
Kod: 
KAÇINCI([COLOR="Red"]"E"[/COLOR];VERİ!$A:$A;0)
koduna eşittir. bu da 6 yapar.
Kod: 
KAÇINCI(DATA!$A2;VERİ!$A:$A;0)= 6 ise
Kod: 
=DOLAYLI("VERİ!$B[COLOR="Red"]6[/COLOR]:$D[COLOR="Red"]6[/COLOR]")
olur. Bunu veri doğrulamada liste olarak kullandığınız zaman VERİ!$B6:$D6 aralığındaki hücreleri liste şeklinde kullanılmasını sağlar. Bunlar E1,E2,E3 tür. DATA sayfasının A2 hücresine "ad" için veri doğrulama yaptıktan sonra istediğiniz kadar aşağıya çoğaltınız. Daha sonra hücrelerin içeriğini siliniz. Sonra hücreledeki açılır kutudan istediğiniz ismi şeçiniz. Daha sonra aynı işlemi "ref" için Data sayfasının B2 hücresine yapınız. B sütununda istediğiz hücrenin açılır kutusunu seçtiğiniz zaman, bulunduğunuz satırın A sütunundaki değere ait bilgilerini göreceksiniz. yukarıdaki örneğin A5 'de "D" var ise B5'in açılır listesinde sadece D1,D2,D3 'ü göreceksiniz.
DATA!$A2'deki kırmızı renkli 2 rakamı "ref" için veri doğrulama yaptığınız satır no.su olacaktır. Benzer kodlar hazırlarken ad oluşturduğunuz esnada bulunduğunuz sayfa ve satır önemlidir. Ayrıca DATA!$A2 yerine DATA!$A$2 yazılsa idi formüller yanlş hesap yapacaktı.

Örnek dosyanızı asıl dosyanıza uyarlar iken bu hususlara dikkat ediniz
Genişletmek için tıkla ...

Hocam merhaba. Konunun uzerinden cok uzun zaman geçmiş ancak ayni durum bende de var ve kullanamadim fonksiyonu. Veri sayfasinda ksiiler ve referanslari, data sayfasinda ise ad tanimlamaya esas veriler mi var acaba
 
Cevap yazmak için giriş yap yada kayıt ol.
Geri
Üst