[Risolto] Gestione correlate di 3 Liste in Menu con Validità Dati

[webgas]

Ciao a tutti, da giorni che cerco di far funzionare un file, ho fatto diverse prove con le funzioni disponibili e spesso con errori oltre a non trovarne altre, provo ad inserire uno schema:

Inoltre con altro menu a tendina nel 1°Foglio 'Prezzi Ricambi' in A4 stessa riga degli altri di questo foglio, vorrei selezionare anche i Marchi che richiamano le Schede corrispondenti

Esempio, a seconda di cosa seleziono nei menu a tendina di B e C mi deve dare il codice corrispondente nella cella D
1°Foglio 'Prezzi Ricambi', ma vorrei anche fare in modo che mi gestisse i duplicati dentro la validità dati facendomeli vedere tutti

--------A-----------------------B------------------C-----------------------D
--menu a tendina-| menu a tendina | menu a tendina | Cella
------Marca----------------Modello-----------Ricambio--------Codice Ricambio
-----Majestic-------------Lewis Pro--------Accelleratore------------???
-------Vivo------------------Lewis-----------Camera d'aria------------???
-----Maserati--------------Renegad---------Cavalletto---------------???
-------Jeep-----------------Lewis------------Cavalletto---------------???

In questo foglio ci sono i dati previsti fino a 500 righe ma vorrei anche fare in modo che mi gestisse anche i duplicati che si trovano solo in Colonna C ma ed hanno già univoci codici
2°Foglio 'Majestic'

--------A-----------------------B------------------C-----------------------D
----------------------Modello----------------Ricambio---------Codice Ricambio
----------------------Lewis Pro------------Accelleratore---------21654
----------------------Lewis Pro---------------Pedali----------------1565
----------------------Rover Easy--------------Telaio----------------11651
----------------------Renegade-------------Cavalletto------------18641
----------------------Lewis------------------Cavalletto------------21163
----------------------Lewis----------------Camera d'aria---------21685
----------------------Rover Plus--------------Freccia---------------45648


Il risultato dovrebbe essere che dal 1°Foglio 'Prezzi Ricambi' in modo correlato se seleziono la Marca del caso in A4 mi restituisse la lista delle marche, nel menu a tendina di B4 la lista dei Modelli, di conseguenza selezionando in C4 la lista dei Ricambi.

Per ora funziona l'estrapolazione dei codici in D4 con un Cerca.Vert

=CERCA.VERT(B4&C4;Majestic.B3:E501;4;0)

ma nelle nel menu di C4 mi fa vedere solo un Ricambio per Modello mentre per La Marca, la lista la prenderebbe da un elenco a parte sullo stesso 1°foglio che poi dovrò inserire collegamenti alle Schede per ogni Marchio.

Chissà se riesco a farmi capire, Grazie

[gioh66]

Ciao. Allega un file d'esempio su cui si possa lavorare. Le immagini servono a poco.

[webgas]

Ciao, nel frattempo ho aggiunto nel 2° foglio e poi tutta a destra, colonne supplementari per ovviare al cerca vert, indice e confronta non ci sono riuscito neanche per trovare l'ID Rif.

[webgas]

.

[webgas]

Riguardo le colonne di appoggio se non servono vorrei che i menu a tendina funzionassero pescando i dati nelle prime colonne direttamente

[webgas]

Per l'ID Rif. trovato "=INDICE(Majestic.A2:E500; CONFRONTA($E$4; Majestic.E2:E500; 0); 1)
"

Riguardo ai menu no

[gioh66]

Buon pomeriggio. Vediamo se ho capito. Nel foglio "Pezzi di ricambio" per il menù a tendina del "Modello" usa

Codice: Seleziona tutto

SCARTO(INDIRETTO(A4&".A1");1;CONFRONTA(B3;INDIRETTO(A4&".A1:S1");0)-1;1000)

e per quello del "Ricambio"

Codice: Seleziona tutto

SCARTO(INDIRETTO(A4&".A1");CONFRONTA(B4;INDIRETTO(A4&".C2:C10000");0);CONFRONTA(C3;INDIRETTO(A4&".A1:S1");0)-1;CONTA.SE(INDIRETTO(A4&".A2:S10000");B4))

Poi per gli altri dati in D4 da tirare a destra metti

Codice: Seleziona tutto

=INDICE(INDIRETTO($A$4&".A2:S1000");CONFRONTA($B$4&$C$4;INDIRETTO($A$4&".C2:C1000")&INDIRETTO($A$4&".D2:D1000");0);CONFRONTA(D3;INDIRETTO($A$4&".A1:S1");0))

Ti riallego file di prova con le modifiche con cui fare prove.

[webgas]

Ti ringrazio, purtroppo da errori 504, tra l'altro il file avvisava che è stato fatto con una versione più aggiornata e potrebbe non funzionare ma ho già la versione 4.1.1.5 che risulta però aggiornata, unica cosa che credo non c'entri ma dovrò capire, e che Java aggiornato non funziona poichè non lo vede nella cartella bin di Java, eppure c'è.

Sul foglio Prezzi Ricambi nel menu a tendina ora ci sono tutti i duplicati.

[gioh66]

E certo io ho fatto il lavoro con Libreoffice. Adesso c'è da capire cosa non piace a Openoffice.

[gioh66]

Scoperto l'arcano. Su openoffice la formula di D4 va confermata come matriciale, cioè con la combinazione di tasti ctrl+maiuscolo+invio e poi copiata e incollata a destra.

[webgas]

Ciao, ho provato ma non succede nulla, anche se provo ad inserire la formula tra le parentesi Graffe

[gioh66]

Prova così. Apri la formula con doppio click o con F2 cancella l'ultima parentesi, rimettila e premi contemporaneamente ctrl+maiuscolo e dai invio. Oppure premi su Fx e nella finestra che si apre spunta matrice. Poi copia e incolla a destra.

listino prezzi 1.png (84.63 KiB) Visto 755 volte

listino prezzi 2.png (47.5 KiB) Visto 755 volte

Ps.: il mio Openoffice è in inglese ma penso si capisca.

[webgas]

Ok, Grazie, ha Funzionato con la Spunta Matrice ripassandoli tutti, non con altri metodi.

Sono in fase di popolamento dati nelle schede per vedere se funziona come pensavo.

Volevo spostare la colonna B del secondo foglio, in fondo dove c'erano quelle di supporto alle formule in modo da evitare scritture, provando ho visto che errori, forse perchè hai utilizzato la colonna B e da O a S che stavo provando per ovviare ai duplicati o altro?

[gioh66]

Se vuoi spostare la colonna B in fondo alla tabella, devi modificare la formula in D4 così
=INDICE(INDIRETTO($A$4&".A2:S1000");CONFRONTA($B$4&$C$4;INDIRETTO($A$4&".B2:B1000")&INDIRETTO($A$4&".C2:C1000");0);CONFRONTA(D3;INDIRETTO($A$4&".A1:S1");0))
Questo perchè INDIRETTO è una formula statica e non si adatta alle modifiche, devi correggere a mano.

[webgas]

Anche se inverto l'ultima colonna che ha formule, H con G, serve una modifica? Così resterebbero i campi liberi per lavorarci evitando di scriverci sopra

[gioh66]

Non dovrebbe cambiare niente. Le colonne B e C servono per determinare la riga da cercare, mentre la colonna viene individuata tramite l'intestazione delle colonne.

[webgas]

Ciao, Dovrebbe essere a posto ripassando tutte le funzioni con spunta Matrice nel Primo Foglio;

Anche invertendo le Colonne H e G ho solo dovuto rivedere le formule;

Ho spostato la Colonna B al Fondo per cui è diventata S, quella con la concatenazione di Modello e Ricambio che utilizzavo per ovviare ai duplicati nel menu a tendina;

Al popolamento dati ho di nuovo il problema dei Duplicati in ricambi su determinati Ricambi che me ne fa vedere uno con valori esatti se doppio, l'altro risulata #N/D;

Immagino che occorra tenerne conto di questa colonna, nonostante ID e Codice Ricambio univoci, spesso capita che il Modello è uguale tranne la lettera in più all'inizio ma potrebbe essere altro, mentre i Ricambi sono spesso con valori duplicati pur facendo parte della stessa marca poichè ogni fornitore potrebbe aggiungere un suo riferimento.

Codice Validità Ricambio Attuale:
SCARTO(INDIRETTO(A4&".A1");CONFRONTA(B4;INDIRETTO(A4&".B2:B4001");0);CONFRONTA(C3;INDIRETTO(A4&".A1:S1");0)-1;CONTA.SE(INDIRETTO(A4&".A2:S4001");B4))

Sto anche eliminando i caratteri nelle descrizioni varie di cui mi sto accorgendo man mano che danno fastidio alle formule tipo + * "

Grazie Infinite

[gioh66]

Buondì. Col nuovo file in mano mi sono accorto che c'erano delle incongruenze nel menù a tendina della cella C4. Per correggerlo ho modificato il menù a tendina di B4 così

Codice: Seleziona tutto

SCARTO(INDIRETTO(A4&".A1");1;CONFRONTA(B3;INDIRETTO(A4&".A1:L1");0)-1;4001)

e in C4 così

Codice: Seleziona tutto

SCARTO(INDIRETTO(A4&".A1");CONFRONTA(B4;INDIRETTO(A4&".B2:B4001");0);CONFRONTA(C3;INDIRETTO(A4&".A1:L1");0)-1;CONTA.SE(INDIRETTO(A4&".A2:L4001");B4))

questo perchè, se ho capito bene idati che ti servono nei vari fogli sono quelli fino alla colonna L.
Inoltre per non avere duplicati metti la spunta a "Disponi in ordine crescente". Tutto il resto rimane uguale.
Ti riallego il file per verifiche.
https://www.dropbox.com/scl/fi/5q3tttnt ... sqm48&dl=0

[webgas]

Ciao, dalle prime verifiche sembra andare abbastanza bene, non so perchè ma non danno neanche più fastidio i caratteri tipo + * " a parte ' se davanti i numeri, trovato uno ma non ha senso di esserci per cui tolto;

A volte la tendina si apre dopo un po ma credo colpa del numero di righe che controlla;

Nella Tendina Modello, su Majestic appaiono 2 KYM e tutti gli altri li toglie, visualizzano entrambi Ruota con Motore, uno con il resto della lista ricambi e l'altro da #N/D, ma lo fa anche con Accelleratore, anche se spunto Disponi in ordine crescente in validità, strano solo qui me ne sono accorto;

Ho sostituito la S con L sulle formule con matrice sul pannello principale;

Rimane da eliminare gli #N/D e #VALORE se non compromette nulla;

Grazie mille

[gioh66]

Buondì. La lentezza come dici tu può dipendere dalla mole di dati.
I due KIM te li da perchè quello della riga 17 è scritto in maniera diversa. Cioè mentre tutti gli altri sono scritti con uno spazio finale, questo non ce l'ha e anche se non si vedono per il foglio di calcolo gli spazi contano.
Poi per eliminare i valori di errore modifica la formula in D4 così

Codice: Seleziona tutto

=SE(VAL.ERRORE(INDICE(INDIRETTO($A$4&".A2:S4001");CONFRONTA($B$4&$C$4;INDIRETTO($A$4&".B2:B4001")&INDIRETTO($A$4&".C2:C4001");0);CONFRONTA(D3;INDIRETTO($A$4&".A1:S1");0)));"";INDICE(INDIRETTO($A$4&".A2:S4001");CONFRONTA($B$4&$C$4;INDIRETTO($A$4&".B2:B4001")&INDIRETTO($A$4&".C2:C4001");0);CONFRONTA(D3;INDIRETTO($A$4&".A1:S1");0)))

[webgas]

Ciao, in effetti con gli spazi giusti sono giorni che cerco di sistemarli notando qualche problema, stasera ci lavoro, ho un sacco di descrizioni ricambi con tanti e diversi spazi da eliminare un a uno, non avendo trovato il modo, mentre per i modelli avevo aggiunto uno spazio perché usavo la concatenazione e volevo uno spazio tra loro, ora però non serve più la colonna S, grazie

[gioh66]

Per annullare gli spazi più velocemente potresti fare così.
Ti posizioni su una colonna vuota e metti

Codice: Seleziona tutto

=ANNULLA.SPAZI(B2)

e trascini in basso. Poi con l'intervallo selezionato fai "taglia", ti posizioni in B2, tasto dx, incolla speciale e se non c'è metti la spunta solo a "testo", "numeri" e "date".
La formula nelle celle con più parole non elimina lo spazio che le divide.
Ps.: un suggerimento per concatenare due parole con uno spazio: supponendo di voler unire A1 e B1 usa

Codice: Seleziona tutto

=CONCATENA(A1;" ";B1)

Tra le virgolette c'è uno spazio, nel caso non si noti.

[webgas]

Ancora Grazie, per gli spazi sistemato, avevo provato con i codici ma poi mi ero perso,ho notato solo che se trova apostrofi davanti a numeri, la validità non lo vede, se inserisco davanti allo 0 invece il numero in lista si vede ma una volta selezionato annulla gli zeri restituendo comunque il risultato #N/D, in tal caso serve #N/D per capire per cui nelle tendine va bene così.

Gli altri #N/D invece li ho tolti dal pannello in tutte le celle, da D a M.

[webgas]

Ciao, pensavo di aver finito invece dopo aver appurato l'estrema lentezza con blocchi del file, ho dovuto alleggerirlo ed anche modificarlo con conseguenti rimodulazioni, ho aggiunto un menu a tendina in D4 nel primo foglio e tolto altro, i menu precedenti funzionano ma in questo cercando di capire la logica per aggiungere funzioni non ci sono riuscito con errori vari.

In Validità Dati di D4 pensavo fosse giusto ma evidentemente no

SCARTO(INDIRETTO(A4&".A1");
CONFRONTA(B4;INDIRETTO(A4&".A2:A1001");0);
CONFRONTA(C3;INDIRETTO(A4&".A1:H1");0)-1;
CONFRONTA(C4;INDIRETTO(A4&".B2:B1001");0);
CONFRONTA(D3;INDIRETTO(A4&".A1:H1");0)-1;
CONTA.SE(INDIRETTO(A4&".A2:H1001");C4))

In E4 in poi stesso problema delle lunghe funzioni che non ho capito come aggiungere e se possibile con lo stesso sistema ma per ora sbaglio

SE(VAL.ERRORE(INDICE(INDIRETTO($A$4&".A2:H1001");CONFRONTA($B$4&$C$4;INDIRETTO($A$4&".A2:A1001")&INDIRETTO($A$4&".B2:B1001");0);CONFRONTA(D3;INDIRETTO($A$4&".A1:H1");0)));"";INDICE(INDIRETTO($A$4&".A2:H1001");CONFRONTA($B$4&$C$4;INDIRETTO($A$4&".A2:A1001")&INDIRETTO($A$4&".B2:B1001");0);INDICE(INDIRETTO($A$4&".A2:H1001");CONFRONTA($C$4&$D$4;INDIRETTO($A$4&".B2:B1001")&INDIRETTO($A$4&".C2:C1001");0);CONFRONTA(E3;INDIRETTO($A$4&".A1:H1");0)))

Nei fogli ho eliminato colonne e ridotto le righe a 1001 per capire se tra tutto miglioro la velocità dei calcoli per cui ora il range è da A e H.

Grazie

[webgas]

In Validità Dati mi pare di esserci riuscito, mi fossilizzavo sul fatto che dovesse fare riferimento a scalare indietro, confermi?

SCARTO(INDIRETTO(A4&".A1");
CONFRONTA(C4;INDIRETTO(A4&".B2:B1001");0);
CONFRONTA(D3;INDIRETTO(A4&".A1:H1");0)-1;
CONTA.SE(INDIRETTO(A4&".A2:H1001");C4))

[webgas]

Provato anche così togliendo il val per vedere l'errore ma da #N/D

=SE(ERRORE(INDICE(INDIRETTO($A$4&".A2:H1001"); CONFRONTA($B$4&$C$4; INDIRETTO($A$4&".B2:B1001")&INDIRETTO($A$4&".C2:C1001"); 0); CONFRONTA($D$4; INDIRETTO($A$4&".A1:H1"); 0))); ""; INDICE(INDIRETTO($A$4&".A2:H1001"); CONFRONTA($B$4&$C$4; INDIRETTO($A$4&".B2:B1001")&INDIRETTO($A$4&".C2:C1001"); 0); CONFRONTA($D$4; INDIRETTO($A$4&".A1:H1"); 0)))

[gioh66]

Buondì. Visto che i fogli hanno tutti la stessa struttura, io direi di mettere in D4 questa formula da tirare a destra

Codice: Seleziona tutto

=INDICE(INDIRETTO($A$4&".A2:H1001");CONFRONTA($C$4;INDIRETTO($A$4&".B2:B1001");0);CONFRONTA(D3;INDIRETTO($A$4&".A1:H1");0))

Ti riallego il file con la nuova formula

[webgas]

Ciao, quindi inutile la tendina in D4, solo che se in C4 c'è solo trova solo un codice interamente numerico che non ci si ricorda a quale descrizione corrisponda potrebbe essere un problema, ci sarebbe un modo che C4 e D4 potessero ricercare da entrambi le posizioni? se non ci si ricorda del numero codice numerico, uno prova a cercare la descrizione, fermo restando le precedenti iniziali ricerche su A4 e B4.

Dimenticavo anche la versione per non vedere l'errore nel precedente ma al suo posto "Non Correttamente Selezionato".

Grazie

[webgas]

Mannaggia scordato un pezzo, aggiornato Non Correttamente Selezionato"

[gioh66]

Non ho capito cosa intendi. Per avere i risultati i menù a tendina vanno selezionati in ordine: prima A4 poi B4 e infine C4.
Non puoi partire dalla descrizione... o forse anche si ma sarebbe meno pratico perchè dovresti fare un menù a tendina con tutti i prodotti per marca e poi ricavare gli altri dati.

Per togliere l'errore #N/D puoi modificare la formula così:

Codice: Seleziona tutto

=SE(VAL.ERRORE(INDICE(INDIRETTO($A$4&".A2:H1001");CONFRONTA($C$4;INDIRETTO($A$4&".B2:B1001");0);CONFRONTA(D3;INDIRETTO($A$4&".A1:H1");0)));"NON CORRETTAMENTE SELEZIONATO";INDICE(INDIRETTO($A$4&".A2:H1001");CONFRONTA($C$4;INDIRETTO($A$4&".B2:B1001");0);CONFRONTA(D3;INDIRETTO($A$4&".A1:H1");0))

)