Completare automata din lista

[H2SO4]

Io lucrez cu datavalidation de ...mai mult de 100 de item-uri (furnizori cu care nu se mai intretin relatii !!) deci, multumesc de formule . Probabil saptamana viitoare o sa pot sa le aplic si la "cearsafurile mele ".

Ca sa nu fie asa incarcate acele DV (si pentru ca ai si)

furnizori cu care nu se mai intretin relatii !!)

de ce nu folosesti o metoda usoara de eliminare a datelor care nu te mai intereseaza - fara a le sterge - adica intro coloana (sa zicem A) treci toti furnizorii iar in coloana B pui un "x" in dreptul datelor de interes (este mai usor de intretinut lista) apoi cu ajutorul unei formule in coloana C, preiei doar datele cu bifa din col B. Aceasta coloana o vei folosi pentru lista din DV.

[Tzica]

H2SO4>
Multumesc de sugestie.Nu stiu cum de nu am aplicat asa ceva si in excel (in acces am la furnizori / clienti un camp checkbox Yes/No ) !!!

[Tzica]

H2SO4>

Mi-ai dat de lucru !! As cauta o solutie fara coloana ajutatoare, folosind name managerul....dar nu stiu daca e posibil.Iaca o prima incercare (fireste...nu intoarce decat prima prima valoare gasita in lista).

[Tzica]

Ok.Doar pentru a clarifica , ordona acest topic:
1. Speta cu autocompletare, plecand de la primele doua litere. De departe abordarea lui TudyBTH e cea mai utila.Aceasta abordare e utila, folositoare cand avem de completat liste (exemplul fiin NIR-ul) in care introducem denumiri diferite, iar autocompletul nu poate sa-si faca datoria, neavand introduse denumiri;
2.Eliminare diverse denumiri (speta cu furnizori inactivi).Gandindu-ma mai bine, nu e chiar atat de utila o filtrare a furnizorilor inactivi.Bine...depinde cum folosim.Eu am un pled de cateva mii de randuri, (cearsaf o sa-i zic cand o depasi zecemii), in care am introduse datele despre facturi...nu detaliile facturii (Data, NumarFurnizor,Total etc..) .Practic , la pledul meu de cateva mii de randuri, autocompletul isi face datoria. La sugestia lui H2SO4 , am reusit dar...numai cu coloana ajutatoare.
Nu stiu care din formulele folosite, pentru/in name manager ar fi mai eficienta:
FiltruFurnizor:

Cod: Selectaţi tot

OFFSET(Sheet1!$H$4;0;0;COUNTA(Sheet1!$H:$H)-1)

Sau, mai pe bajbaitelea, Nfilfur (bine...aici e clar, trebuie dragalita si mai in jos formula de pe coloana H)

Cod: Selectaţi tot

Sheet1!$H$4:$H$20

Coloana ajutatoare, coloana H are clasica formula de filtrare , CSE:

Cod: Selectaţi tot

{=IF(ROWS($H$4:H4)>$F$4;"";INDEX(Table1[[#All];[NumeFurnizor]];SMALL(IF((--ISTEXT(Table1[NumeFurnizor])*(Table1[Status]=1));ROW(Table1[NumeFurnizor])-ROW(Table1[[#Headers];[NumeFurnizor]])+1);ROWS($H$3:H3))))}

M-am obisnuit sa folosesc un numar de control, in locul clasicului Ifferor, pentru a limita calcularea formulei.
Ramane simpla curiozitatate...se poate fara coloana ajutatoare ??? fireste..nonvba !

[TudyBTH]

Salut,

Formula de mai jos (folosita in contextul fisierului cu NIR) am adaptat-o pentru a elimina din lista produsele marcate cu "Inactiv" in coloana "Status". Deci se poate adapta pentru filtrarea din coloana H in fisierul cu furnizori.

Cod: Selectaţi tot

=IFERROR(INDEX(prod;MATCH(0;COUNTIF($F$1:F1;prod)+(INDEX('NRCD-1 pagina'!$B$14:$B$29;COUNT('NRCD-1 pagina'!$E$14:$E$29)+1)<>LEFT(prod;LEN(INDEX('NRCD-1 pagina'!$B$14:$B$29;COUNT('NRCD-1 pagina'!$E$14:$E$29)+1))))+(OFFSET(prod;0;3)="Inactiv");0));"")

rezultand un range ("fprod" aici in NIR) care poate fi definit cu

Cod: Selectaţi tot

=OFFSET(Produse!$F$2;;;COUNTIF(Produse!$F$2:$F$10000;"?*");1)

Selectia furnizorilor cu Data Validation fara coloana ajutatoare implica sortarea tabelului "Furnizori" dupa valoarea din "Status".
Eliminarea coloanei H (cu filtrea) ar necesita executarea filtrarii in Data Validation, lucru ce nu poate fi realizat din doua motive:

• 1. DV nu recunoaste formulele array
  2. (Chiar daca am scie o formula normala - nu am incercat dar e posibil sa poata fi realizat si in cazul de fata) DV nu accepta un range discontinuu

Deci, avand in vedere ca se pune problema filtrarii unui numar destul de mare de itemuri iar actualizarea listei de furnizori se face relativ rar (fata de frecventa extragerii de date din lista), se poate renunta complet la filtrarea cu formula (coloana H) dar trebuie efectuata sortarea tabelului furnizorilor dupa fiecare actualizare.
In acest caz voi avea un range continuu ("FiltSortProd") ce poate fi folosit in DV fara coloana ajutatoare, definit cu formula (pentru furnizorii activi marcati cu 1 in Status)

Cod: Selectaţi tot

=OFFSET(Table1[NumeFurnizor];;;COUNT(Table1[Status]);1)

In concluzie,

• - folosirea unei coloane ajutatoare pentru filtrare implica (nu in toate cazurile) folosirea formulelor array care pot incetinii executia la un numar foarte mare de itemuri.
  - pe de alta parte, coloana ajutatoare, pe langa filtrarea itemurilor inactive poate realiza si filtrarea dupa caracterele introduse, lucru foarte util in selectarea dintr-o lista lunga,
  - solutia optima, excluzand VBA, depinde de numarul itemurilor din lista, daca numarul lor nu duce la o viteza prea mare de executie as alege prima varianta, daca nu adoua

[Tzica]

TudyBTH>
Multumesc pentru rabdare. Cel putin in cazul spetei mele (furnizori), explicatiile sunt mai mult decat clare.Intr-adevar, fara coloana ajutatoare, e un pic mai dificil in sensul actualizarii (selectam in tabel coloana status numai 1 ).

[TudyBTH]

Am adaptat formulele pentru fisierul cu furnizori (Table2)
In coloana "Indicator" poate fi orice camp care urmeaza a fi introdus manual (cum era cantitatea in NIR) cu rolul de a marca linia respectiva ca fiind linie completata

[Tzica]

TudyBTH >
Chapou bas ! Eu deja am fost multumit de formule, faptul ca ai si aprofundat/dezvoltat abordarea e mai mult decat mi-as fi dorit.Multumesc.

[Tzica]

Updatez acest topic, pentru a nu mai deschide altul...e doar o alta abordare a aceleasi probleme, dropdownlist ( cu sugestii ) in tabel.

Sursa:OdonareAlfabetica

Deci, de unde mi-as fi dorit fara coloana ajutatoare...am ajuns la..cel putin trei !!. Cu ajutorul link-ului de mai sus, e rezolvata problema ordonarii alfabetice (am folosit ..monstrul de formula de la sfarsit, rezultatul se poate vedea in sheet-ul calculatii coloana C).
In sheet-ul Calculatii:
- Formula din coloana B aloca un numar de ordine, asupra coloanei C (FiltrutblEmitent), in functie de litera /literele introduse in sheet-ul IntroducereDate celula C2;
- In coloana F, "apar" sugestiile, pe baza numarului alocat in coloana B;
- Celula E2 , contine formula folosita pentru Numele Nvalidemitent, sursa pentru DropDownList din foaia Introducere Date.

Problema de care ma lovesc, e ca in cazul transformarii zonei din IntroducereDate in Tabel, nu reusesc sa gasesc o formula care sa imi reinitializeze "sugestiile"... la adaugarea unui nou rand , (completare tip operatiune de exemplu, sau poa sa fie si coloana Data) TipOperatiune . Si asta...numai ca sa scap de ..sortarea tabelului Emitenti , asta e...suntem oameni...mai uitam !
Multumesc .

[Nills]

Se simte lipsa dl. Indigo.
Sunt convins ca dl. Indigo ar fi venit cu o formula mai putin stufoasa de aranjare alfabetica a emitentilor si cu preluare dinamica a datelor.
Trimite-i un mail.