Data validation cu lista si referinte structurate din tabel

Informatii despre cum se utilizeaza Microsoft Excel 2007. Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc

Data validation cu lista si referinte structurate din tabel

Mesajde smcsa » Joi Noi 12, 2009 11:07 am

Ideea este urmatoarea: Pe un sheet am un tabel cu vanzarile (un rand pentru fiecare rand din factura). Pe un alt sheet am un tabel cu nomenclatorul de servicii care se vand. Pe coloana "servicii" din primul sheet, pe coloana unde trec serviciile vandute vreau sa am Data Validation cu lista care sa permita doar valorile din nomenclator. Pana aici nimic nou. Dar eu vreau sa folosesc la Source referinta structurata catre acea coloana din sheet-ul cu nomenclatorul si imi da eroare in formula. Intrebarea este: nu se poate asa, e o limitare a Excel ? Formula e buna, am verificat-o in sheet si returneaza ce trebuie.
Varianta cu range name dinamic care s-a mai dat pe aici nu ma satisface pentru ca:
1. Asa e mult mai usor de construit formulele (am vreo 30 de locuri unde am nevoie), manual sau VBA
2. Range-urile tabelelor sunt prin definitie dinamice
3. E total aiurea sa mai definesc alte name peste cele ale tabelelor, pentru fiecare coloana de care am nevoie
4. Am tabele pe post de sursa care au si total row si pe mine ma intereseaza doar [#Data]
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
smcsa
 
Mesaje: 2805
Membru din: Mar Sep 29, 2009 7:29 pm
Localitate: Timisoara

Re: Data validation cu lista si referinte structurate din tabel

Mesajde Dr. Cloud » Joi Noi 12, 2009 12:50 pm

Imi permit sa ma bag si eu putin in discutie.
Daca am inteles bine, vreti sa faci Data Validation cu info din alt worksheet.
Cand ati introdus sursa v-a dat eroare.

Daca asa este, eu v-am atasat cartea de lucru si cu o poza.
Am facut data validation cu datele din celalalt worksheet.
Cand ati specificat sursa pentru lista ati pus =Servicii[Denumire], dar de fapt trebuia =Denumire pentru ca trebuia pus numele coloanei si nu celulele de referinta. (cf. traducerii capetelor din Name manager)
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Dr. Cloud
 
Mesaje: 3329
Membru din: Mar Oct 02, 2012 11:19 am

Re: Data validation cu lista si referinte structurate din tabel

Mesajde Dr.Windows » Joi Noi 12, 2009 1:00 pm

Cred ca formula =Denumire ete totusi incorecta avand in vedere cerintele initiale - fara nume de range-uri... pentru ca ce se vede este doar un Named Range "facut de mana" si care i-a "scapat" probabil in foaia pusa ca exemplu... pentru ca daca incerci sa creezi un alt tabel vei vedea in Name Manager ca iti apare doar numele tabelului fara sa numeasca de la sine putere vreo coloana... :roll:
Dr.Windows
Site Admin
Site Admin
 
Mesaje: 4493
Membru din: Vin Iul 31, 2009 7:32 am

Re: Data validation cu lista si referinte structurate din tabel

Mesajde smcsa » Joi Noi 12, 2009 1:24 pm

Se pare ca s-a intamplat ca la descoperirea penicilinei :D
Da, imi scapase acel nume definit de mine, dar se pare ca, acesta avand referinta la un range prin definitie dinamic, este el insusi dinamic. Deci singura solutie (chiar daca e dupa ureche) este sa definesc cate un nume pentru coloanele care ma intereseaza din nomenclatoare de forma "=Denumire", dar cu nume diferite, pentru ca am vazut ca validare de forma =Servicii!Denumire (ca sa pot avea acelasi name definit de mana, cu scope pe sheet-ul lui) nu vrea (You cannot use references to other worksheets or workbooks for Data Validation criteria)
Iar numele astea "de mana" le pot da si din VBA
smcsa
 
Mesaje: 2805
Membru din: Mar Sep 29, 2009 7:29 pm
Localitate: Timisoara

Re: Data validation cu lista si referinte structurate din tabel

Mesajde Dr. Cloud » Joi Noi 12, 2009 1:46 pm

Eu am incercat si asa:
Cod: Selectaţi tot
=INDIRECT("Servicii!A2:A6")

Si imi returneaza lista de servicii.

Din cate am "studiat", Data Validation nu poate face referire la alt sheet, decat prin Name Range sau formula.
Dar cum in Name Range ai mai multe coloane nu poate face referire la ele
Dr. Cloud
 
Mesaje: 3329
Membru din: Mar Oct 02, 2012 11:19 am

Re: Data validation cu lista si referinte structurate din tabel

Mesajde Dr.Windows » Joi Noi 12, 2009 2:17 pm

smcsa scrie:Iar numele astea "de mana" le pot da si din VBA


Inseamna ca trebuie sa tai punctul nr. 3 din cerintele tale pentru ca EXACT asta vei face... ;) doar ca ceva mai "automatizat"... numai ai grija la stabilirea regulilor de "numire" sa nu le schimbi prea des si sa te trezesti cu o muuuultime de rangeuri cu diverse nume dar pe exact acelasi range.
Si ai dreptate un Name Range definit peste o coloana de tabel va face automat acel range "dinamic" - se va extinde/restrange odata cu tabelul...
Dr.Windows
Site Admin
Site Admin
 
Mesaje: 4493
Membru din: Vin Iul 31, 2009 7:32 am

Re: Data validation cu lista si referinte structurate din tabel

Mesajde smcsa » Joi Noi 12, 2009 3:06 pm

Alexx scrie:Eu am incercat si asa:
Cod: Selectaţi tot
=INDIRECT("Servicii!A2:A6")

Si imi returneaza lista de servicii.

Din cate am "studiat", Data Validation nu poate face referire la alt sheet, decat prin Name Range sau formula.
Dar cum in Name Range ai mai multe coloane nu poate face referire la ele


Referinta trebuie sa fie dinamica (adaug, sterg in nomenclatoare)

lucian scrie:
smcsa scrie:Iar numele astea "de mana" le pot da si din VBA


Inseamna ca trebuie sa tai punctul nr. 3 din cerintele tale pentru ca EXACT asta vei face... ;) doar ca ceva mai "automatizat"... numai ai grija la stabilirea regulilor de "numire" sa nu le schimbi prea des si sa te trezesti cu o muuuultime de rangeuri cu diverse nume dar pe exact acelasi range.
Si ai dreptate un Name Range definit peste o coloana de tabel va face automat acel range "dinamic" - se va extinde/restrange odata cu tabelul...


La punctul 3 ma gandeam la un utilizator obisnuit, care vrea sa faca asta fara habar de VBA.
Numele vor fi de forma "NumeSheet_NumeColoana".

Ideea e ca aplicatia va permite ca utilizatorul sa-si genereze sheet/tabelul pentru orice nomenclator doreste, cu orice structura (din UserDialog) dupa ce si-a inserat coloana in sheet-ul de lucru (in cazul exemplului sheet-ul Vanzari) si in plus, la inceput de an, aplicatia trebuie sa genereze INTREG FISIERUL fara date, dar cu structura de sheets / tabele / data validation etc. conform ultimei zile de lucru din anul anterior
smcsa
 
Mesaje: 2805
Membru din: Mar Sep 29, 2009 7:29 pm
Localitate: Timisoara

Re: Data validation cu lista si referinte structurate din tabel

Mesajde Dr. Cloud » Joi Noi 12, 2009 3:20 pm

Am mai incercat asa:
Cod: Selectaţi tot
=INDIRECT("Servicii!A2:A100")

Chiar daca am doar 6 inregistrari in acea coloana, in lista generata de Data Validation nu imi arata si blank-urile, ci doar valorile concrete
Astfel pot sa sterg si sa adaug ce valori doresc.
Dr. Cloud
 
Mesaje: 3329
Membru din: Mar Oct 02, 2012 11:19 am

Re: Data validation cu lista si referinte structurate din tabel

Mesajde smcsa » Joi Noi 12, 2009 3:31 pm

Corect, dar daca ajung sa am nevoie de 100 pozitii ? De fapt corect "daca ajunge...", ca e Maria Sa clientul, care poate fi o proaspata fata cu Bacalaureat cu 10 pe linie si care nu stie sa calculeze baza si TVA daca cunoaste suma totala (nu glumesc, am intalnit un caz). De cand fac aplicatii de baze de date, am invatat sa ma pun in locul celui mai dummy utilizator.
smcsa
 
Mesaje: 2805
Membru din: Mar Sep 29, 2009 7:29 pm
Localitate: Timisoara

Re: Data validation cu lista si referinte structurate din tabel

Mesajde Dr. Cloud » Joi Noi 12, 2009 3:41 pm

Pai in loc de A100 pui A65.... :lol:
Eu aici ma opresc, caci mai mult de atat nu stiu.
Dr. Cloud
 
Mesaje: 3329
Membru din: Mar Oct 02, 2012 11:19 am

Următorul

Înapoi la Intrebari despre Excel 2007

Cine este conectat

Utilizatorii ce navighează pe acest forum: Niciun utilizator înregistrat şi 2 vizitatori