Elenco a discesa co 2 condizioni

[maro65]

Non riesco a risolvere un problema, che riguarda il file di seguito allegato:

http://rapidshare.com/files/355657158/G ... .xlsm.html

Nella colonna "A" del foglio "Documenti" seleziono il fornitore di anagrafica; nella colonna "J" sempre del foglio "Documenti" seleziono l'articolo mediante un elenco che ho creato nel foglio "List_for" (appartenente sempre allo stesso file).

Quello che non riesco a fare è che una volta selezionato il fornitore nella colonna "A" del foglio "Documenti", l'elenco dei prodotti che vedo nella collonna J vorrei che fossero solo quelli del fornitore selezionato e non tutti i prodotti presenti. Inoltre il prodotto può essere lo stesso per due fornitori diversi, ma avere un prezzo differente uno dall'altro.

Grazie in anticipo, se qualcuno riesce ad aiutarmi.

Ciao.

[Anthony47]

Ciao maro65 e benvenuto nel forum.
Non vedo nel tuo file dove e' l' anagrafica con l' elenco completo di Fornitore /Prodotto /Prezzo; la cosa piu' vicina a quello che dico e' il foglio LIST_FORe quindi supponiamo che sia quello.

Io procederei in questo modo:
-inserisci sul "modulo vba" del foglio Documenti la seguente macro:

Codice: Seleziona tutto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set CheckArea = Application.Union(Range("J2:J2000"), Range("L2:L2000"))
If Application.Intersect(ActiveCell, CheckArea) Is Nothing Then Exit Sub
If (Selection.Rows.Count + Selection.Columns.Count) > 2 Then Exit Sub
Sheets("LIST_FOR").Range("H1").Value = Cells(Target.Row, 2).Value
End Sub


Questa ti copiera' in H1 di List_For il valore contenuto in col B della riga selezionata, se appartenente ai range J2:J2000 oppure L2:L2000.

-in colonna I di LIST_FOR ti crei (tramite Dati /Filtro avanzato) un elenco univoco di valori di colonna A (Ragioni sociali? ragionevolmente questo elenco conterra' tutte le voci che oggi sono l' origine della convalida di foglio Documenti, colonna B)
-poi guarda questa discussione per creare nelle colonne K-L un elenco dinamico di Prodotti appartenenti alla Ragione sociale presente in H1 (col K) e relativi prezzi (col L): viewtopic.php?f=26&t=84339
Per questo userai la colonna di appoggio J in cui inserirai la formula =PICCOLO(SE(etc etc (che sara' da adattare alla tua struttura dati), mentre in K ed L inserirai la formula tipo =INDICE(etc etc (idem)

A questo punto torna su Documenti:
-come Convalida di col B usa l' elenco di List_For colonna I
-come convalida di col J (di Documenti) usa l' elenco di colonna K
-per la colonna L (sempre di Documenti) usa Cerca.Vert, cercando il valore di col J nella tabella su List_For colonne K ed L

Spero che lo spunto sia sufficiente, se No prova a postare dettagliando dove sei arrivato.

Ciao

[ricky53]

Ciao,

Mi viene in mente una soluzione con una macro e con un nome elenco dinamico:

1. nel foglio1 (DOCUMENTI) in colonna “B” inserisci il fornitore, le celle hanno la convalida mediante un nome elenco = “Fornitori”
2. nel foglio 2 (LIST_FOR) in colonna “A” ci sono i fornitori, in colonna “B” ci sono i prodotti, in “C” i prezzi
3. nel foglio2 colonna “D” ci sono i fornitori senza ripetizione
4. nel foglio2 colonna “E” (di appoggio) vengono riportati dalla macro i prodotti corrispondenti al fornitore presente in colonna “B” del foglio 1
5. definisci due nomi elenchi, uno per la colonna “B” del foglio1 che fa riferimento alla colonna “D” del foglio2 ed un altro per la colonna “J” sempre del foglio 1 che fa riferimento alla colonna “E” del foglio 2

E’ più complicato scrivere che fare, però devi seguire passo passoquanto ho scritto (nella speranza di non aver commesso errori di scrittura delle colonne e dei fogli).

Nel foglio 1 inserisci

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
    RR = Foglio2.Range("E" & Rows.Count).End(xlUp).Row
    Foglio2.Range("E2:E" & RR).ClearContents
    RR = Foglio2.Range("A" & Rows.Count).End(xlUp).Row
    J = 2
    For I = 2 To RR
        If Foglio2.Cells(I, 1) = Foglio1.Cells(Target.Row, 1) Then
            Foglio2.Cells(J, 5) = Foglio2.Cells(I, 2)
            J = J + 1
        End If
    Next I
    If Target.Column = 1 Then
        Foglio1.Cells(Target.Row, 10) = ""
    End If
End Sub

La macro al cambiamento di un valore della colonna "B" del foglio 1 ricostruisce l'elenco dinamico dei prodotti di appoggio nelle colonna "E" del foglio 2, elenco che è utilizzato nella colonna "J"

Prova e sentiamoci

Ciao da Ricky53

[ricky53]

Ciao,

allego un Esempio che puoi Scaricare

Ciao da Ricky53

[ricky53]

Ciao,

nella macro che ho allegato ho riportato un numero di colonna errato, non potendo correggere il mio intevento, riporto la macro con i valori esatti

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
    RR = Foglio2.Range("E" & Rows.Count).End(xlUp).Row
    Foglio2.Range("E2:E" & RR).ClearContents
    RR = Foglio2.Range("A" & Rows.Count).End(xlUp).Row
    J = 2
    For I = 2 To RR
        If Foglio2.Cells(I, 1) = Foglio1.Cells(Target.Row, 2) Then
            Foglio2.Cells(J, 5) = Foglio2.Cells(I, 2)
            J = J + 1
        End If
    Next I
    If Target.Column = 2 Then
        Foglio1.Cells(Target.Row, 10) = ""
    End If
End Sub

Ciao da nuovo da Ricky53

[maro65]

Per Antony,

Grazie mille per la risposta; ti premetto che non sono molto esperto ed ieri ho più volte provato ad applicare le formule che mi hai suggerito senza successo. Ti allego il file e provo ad indicarti dove non riesco ad avere risultati.

http://rapidshare.com/files/357743993/G ... .xlsm.html

Scrivendo la macro nel foglio "Documenti", la prima volta che l'ho utilizzata ha scritto nella cella "H1" del foglio "List_For" il nome "Fornitore 1": andando però a variare il nome nella colonna "B" del foglio "Documenti" non varia il nome che compare nella cella "H1"; dovrei riuscire ad ottenere che al variare del nome del fornitore della colonna "B" varia il nome scritto nella cella "H1" che dovrebbe diventaer la condizione per cui vado a costruire il nuovo elenco filtrato.

La colonna I non l'ho scritta, perchè l'elenco univoco di fornitori e clienti si trova nel foglio "Ana" colonna "C"; per cui la convalida di colonna "B" nel folgio "Documenti" l'ho realizzata con la colonna "C" del foglio "Ana".

La formula applicata nella colonna "J " del foglio "List_For" non fornisce il risultato, in quanto dovrebbe scrivere solo i risultati che vede nella cella "H1" del foglio "List_For": purtroppo non sono riuscito ad addattarla bene al mio file.

Spero di essere stato abbastanza chiaro, e di non avere creato confusione.

Grazie ancora per l'aiuto.

Ciao.

[maro65]

Per Ricky,

Gentile Ricky ti ringrazio molto per la tua risposta; io non sono molto esperto ed per il momento ho provato la soluzione proposta da Antony; ora proverò anche la tua.

Grazie mille per l'aiuto.

Ciao.

[Anthony47]

H1 su LIST_FOR non viene aggiornato quando si modifica col B, ma quando vado in col J o L, cioe' quando ho effettivamente bisogno di un elenco.
La formula di LIST_FOR col J va introdotta con Contr-Maiusc-Enter, non col solo Enter; in ogni caso per questa formula dipende dalla tua struttura dati, su cui avevo espresso un dubbio che non hai chiarito:

Non vedo nel tuo file dove e' l' anagrafica con l' elenco completo di Fornitore /Prodotto /Prezzo; la cosa piu' vicina a quello che dico e' il foglio LIST_FORe quindi supponiamo che sia quello.

Se pero' l' ipotesi fosse stata giusta allora avresti cominciato a creare l' elenco di col I, cosa che non hai fatto. Deduco quindi che la mia ipotesi era errata, ma in questo caso non so cosa dire oltre allo spunto dell' altro messaggio.
Per immaginare qualcosa in piu' bisognerebbe che sia chiarito dove si trova con l' elenco completo di Fornitore /Prodotto /Prezzo.

Ciao.

[maro65]

Molto propabilmente devo avere sbagliato qualcosa... come hai detto correttamente la condizione fornitore/prodotto/prezzo è quella presente nel foglio "List_For"; la colonna "I" che mi hai detto di creare nel folgio "List_For", ho detto di averla nella colonna "C" del foglio "Ana", ossia l'elenco fornitori non ripetuto dei fornitori. Ho comunque copiato in colonna "I" la formula "=ANA!C1" ecc., così ho creato un elenco univoco di fornitori nella colonna I.

Poi, scusami ma non ho capito bene il 2° passaggio: io lavoro nel foglio "documenti", quando indico nella colonna "B" il fornitore e vado in colonna "J" sempre del foglio "documenti" non trovo l'elenco dei prodotti del solo fornitore selezionato, ma tutto l'elenco completo dei prodotti perchè il valore della cella "H1" nel foglio "List_for" non si modifica.

Spero di non aver creato ulteriore confusione. Scusami ancora se non sono stato abbastanza chiaro.

Ciao.

[Anthony47]

Se l' attuale intervallo "ANAGRAFICA" gia' contiene l' elenco unico (non ripetuto) dei fornitori allora non hai bisogno di crearne una copia in col I di LIST_FOR, e va anche bene la convalida che hai messo in col B di DOCUMENTI.

Per quanto riguarda la costruzione dell' elenco prodotti del fornitore prescelto, lo crei con le formule della discussione che ti ho linkato; formule che tradotte sui tuoi dati sono:
In J2:

Codice: Seleziona tutto

=PICCOLO(SE(LIST_FOR!$A$1:$A$1000=$H$1;RIF.RIGA(LIST_FOR!$A$1:$A$1000);"");RIGHE($A$2:A2))

che confermerai con Contr-Maiusc-Enter
In K2:

Codice: Seleziona tutto

=SE(VAL.ERRORE(J2);"";INDICE(LIST_FOR!B$1:B$1000;$J2))

Poi copi J2:K2 verso il basso per 50 righe.
Poi imposti un intervallo chiamato SUBPROD che include diciamo da K2:K50 e che userai per la convalida in DOCUMENTI-col J.
Se pensi di avere piu' di 50 Prodotti per fornitore, "allunga" questi intervalli e copia piu' verso il basso le formule J2:K2.
Se nella convalida di col J crea problemi avere un elenco contenente valori solo nelle prime righe, allora si potra' assegnare all' intervallo Subprod una lunghezza dinamica.

Per quanto riguarda l' aggiornamento di cella H1 di LIST_FOR, questa avviene quando ti posizioni in colonna J.

Avrai notato che ho saltato le formule in L2 di LIST_FOR di cui ti avevo parlato nei messaggi precedenti e che dovevano contenere il prezzo unitario dei prodotti; questo e' perche' mi sono reso conto che questo elenco varierebbe in continuazione e quindi formule tipo Cerca.Vert che facessero riferimento alla colonna L darebbero risultati casuali. Invece per il calcolo del prezzo unitario userai in L2 di Documenti la formula

Codice: Seleziona tutto

=SE(VAL.ERRORE(CONFRONTA(B2&J2;(LIST_FOR!A$2:A$13)&(LIST_FOR!B$2:B$13);0));"";INDICE(LIST_FOR!C$2:C$5000;CONFRONTA(B2&J2;(LIST_FOR!A$2:A$13)&(LIST_FOR!B$2:B$13);0)))

anche questa da confermare con Contr-Maiusc-Enter

Infine, per evitare errori dovuti a variazioni di col B su righe con colonna J gia' compilata, puo' essere utile fare in modo che tutte le volte che si modifica la col B venga azzerata la corrispondente colonna J; per questo userai la seguente macro:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
CheckArea = "B2:B2000"      '<<< L' area usata di Documenti / Col B
If Application.Intersect(Target, Range(CheckArea)) Is Nothing Then Exit Sub
Cells(Target.Row, 10).ClearContents
End Sub


Uso: tasto dx su tab col nome DOCUMENTI, scegli Visualizza codice, copia la macro e incollala sotto la macro precedente (Worksheet_SelectionChange).

Spero che eseguendo rimanga tutto chiaro, e che soprattutto funzioni come ti interessa.

Ciao

[maro65]

Grazie mille per la risposta, il problema della colonna H1 ora è risolto e la stessa cella si modifica con il variare dell'anagrafica, quando mi posiziono in colonna "J" del foglio "Documenti".

In colonna "J" del foglio "List_For", come risultato mi compare nella cella "J2" il valore numerico "1" quando seleziono il fornitore 1, nelle celle "J3:J7" il valore "#NUM!", nelle celle "J8:..." il valore "#VALORE!"

Se invece seleziono il fornitore 2, nella cella "J2:J7" mi compare il valore "#VALORE!", nelle celle "J8:J10" mi compare il valore "#NUM!", nelle celle "J11:..." il valore "#VALORE!".

Mi sembra che il valore "#NUM!" (tranne il valore nuomerico "1" del fornitore 1) corrisponda ai prodotti indicati per quel fornitore, ma non uscendo un valore la colonna "k2" non si aggiorna.

Ho controllato più volte, e mi sembra di aver trascritto correttamente il testo della formula.

Grazie ancora, ciao.

[Anthony47]

Secondo me non hai inserito la formula in J2 usando Contr-Maiusc-Enter: seleziona J2, premi F2, premi Contr, premi Maiusc, premi e rilascia Enter, rilascia gli altri due tasti. Poi copia verso il basso.

Ciao

[maro65]

Grazie mille, il file funziona benissimo e l'errore era proprio quello da te individuato.

Ho però un piccolo problema: avevo costruito un file semplificato per analizzare la soluzione per migliorare la ricerca (che con il tuo aiuto ho trovato). Ora che tutto funziona volevo riportare le formule come viste nel file che stò utilizzando e dove sono presente dei dati sensibili.

Le macro che hai inserito nell'esempio del foglio "documenti" sono in contrasto con una macro che avevo già inserito (e che ho trovato nel Vs sito), che mi permetteva di utilizzare l'elenco a discesa digitando le prime lettere del nome "anagrafica"+enter, senza scorrere tutto l'elenco.

La macro di cui ti parlo è la seguente:

Codice: Seleziona tutto

CheckArea = "B1:B2000"
If Not Application.Intersect(ActiveCell, Range(CheckArea)) Is Nothing Then
    If (Selection.Rows.Count + Selection.Columns.Count) > 2 Then Exit Sub
        Application.ScreenUpdating = False
        Application.Calculation = xlManual
        Application.EnableEvents = False
RArt = Target.Row
ArtV = UCase(Target.Value)
    If ArtV <> 0 Then
        UR = Worksheets("ANA").Range("C" & Rows.Count).End(xlUp).Row
    For R = 2 To UR
        If ArtV = Mid(Worksheets("ANA").Range("C" & R).Value, 1, Len(ArtV)) Then
            Target.Value = Worksheets("ANA").Range("C" & R).Value
            GoTo esci
        End If
    Next R
esci:
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Secondo te devo eliminarla, o posso riuscire a far convivere le tre macro nel foglio "documenti"?

Grazie ancora per avere risolto questo mio problema. Grazie molte.

Ciao.

[Anthony47]

Non ho seguito con attenzione la discussione da cui credo hai prelevato la macro, non so nemmeno quale algoritmo e quale interfaccia la macro sfrutta; insomma ti direi "cancella".
Avendo filtrato sul fornitore hai ancora bisogno di restringere l' elenco sui primi caratteri?

Ciao

[maro65]

Ciao Antony,
ho applicato l'esempio costruito con te al mio file di origine: funziona tutto, anche se mi dà un errore in "Microsoft Visual Basic" Errore di run-time '1004': Metodo "Intersect" dell'oggetto '_Application' non riuscito; questo errore esce ad ogni digitazione e non riesco a capire il perchè, in quanto nell'esempio che ho visto con te questo errore non esiste.

Mi parlavi della possibilità di visualizzare un elenco dinamico che permetta di vedere solo gli articoli del fornitore e non le righe bianche; mi potersti indicare come fare?

Avendo circa 400 fornitori, attualmente quando consulto nel foglio "Documenti" il nome del fornitore devo scorrere tutto l'elenco, secondo te è possibile riuscire ad inserire come facevo prima le lettere iniziali del nome da digitare e premere invio per velocizzare la ricerca?

Grazie mille per l'aiuto, ciao.

[maro65]

Gentile Antony,
volevo chiederti se per caso non era chiara la mia domanda?

Grazie e ciao.

[Anthony47]

Le domande sono chiare, il post mi era totalmente sfuggito...

Se ti da' Errore di run-time e' difficile che il metodo funzioni ancora; hai alterato la macro Worksheet_SelectionChange? Se SI, come?

Per "l' elenco dinamico", guarda qui per capire come si definisce un intervallo dinamico: viewtopic.php?f=26&t=84968#p486280
Userai questa tecnica per definire un intervallo relativo ai prodotti filtrati per Fornitore (col K di List_for), e userai questo intervallo per la Convalida.

Per la terza domanda, se la macro gia' funzionava prima dell' intervento per la gestione dei prodotti filtrati allora la cosa piu' semplice e' combinarle; prova in questo modo:

Codice: Seleziona tutto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set CheckArea = Application.Union(Range("J2:J2000"), Range("L2:L2000"))
If Application.Intersect(ActiveCell, CheckArea) Is Nothing Then Goto Seconda '< >< >
If (Selection.Rows.Count + Selection.Columns.Count) > 2 Then Exit Sub
Sheets("LIST_FOR").Range("H1").Value = Cells(Target.Row, 2).Value
Seconda:      '< >< >
'SEGUE LA SECONDA MACRO    '< >< >
CheckArea = "B1:B2000"
If Not Application.Intersect(ActiveCell, Range(CheckArea)) Is Nothing Then
'etc etc
'etc
End Sub



Le righe da osservare sono quelle marcate < >< >

Ciao.

[maro65]

Come al solito i tuoi aiuti sono sempre molto utili, e molti complimenti per il forum.

Credo comunque di aver sbagliato a porti alcune domande.

Con riferimeno alla prima domanda ora il file funziona, il mio errore è che la macro che avevo nel foglio "Documenti" e che era in contrasto con le macro da te proposte, si trovava anche nel foglio "List_For"; ora che è stata eliminata anche nel foglio "List_For" le macro funzionano molto bene.

Con riferimento alla seconda domanda, il risultato ottenuto con la formula che mi hai proposto e la convalida che avevo prima è sempre lo stesso, in quanto l'intervallo K1:K1000 rimane sempre uguale. Il problema è che praticamente (essendo l'intervallo variabile), bisognerebbe che non venissero lette le celle vuote: io uso excel 2007 e all'interno dei dati di convalida esiste una cella (dove ho inserito il "flag") "Ignora celle vuote" ma non funziona, per cui quando apro l'elenco della cella, l'inizio dello scorrimento si posiziona nella prima cella vuota anzicché nella prima cella contenente il prodotto. Spero di non aver creato ulteriore confusione.

Con riferimento alla terza domanda, la ricerca del fornitore la dovrei eseguire nella colonna "B" del foglio "Documenti" e nella colonna "A" del foglio "List_For", per cui la ricerca in elenco dovrebbe essere eseguita prima della ricerca del prodotto. La macro utilizzata non mi sembra dia questo risultato.

Per proseguire con la costruzione del mio software, ti vorrei proporre altre due domande:
1. quando scrivo la data è possibile avere un formato che mi permetta di scrivere 110310 e leggere 11/03/2010, anzicché scrivere 11/03/10 e leggere 11/03/2010, ossia eliminare l'uso della barra "/"?
2. una volta che compilo il mio foglio con inserito fornitore, prodotti, documento di riferimento, vorrei creare uno scadenziario per il pagamento; non ho idea da dove iniziare, ma pensavo se era possibile inserire una nuova cella alla fine della riga con un "flag" da selezionare per copiare alcuni dati delle celle della riga in colonna ad altro foglio e completare manualmente quello che non poteva essere copiato?

Grazie ancora, e buona giornata.

[Anthony47]

Con riferimento alla seconda domanda, il risultato ottenuto con la formula che mi hai proposto e la convalida che avevo prima è sempre lo stesso, in quanto l'intervallo K1:K1000 rimane sempre uguale. Il problema è che . . .(etc etc)

Non so "prima" che convalida avevi, ora dovresti avere in testa all' elenco K1:K1000 le sole voci che appartengono al Fornitore prescelto seguito da celle vuote.
Con gli spunti dati nel topic che ti ho linkato dovresti ora definire un intervallo dinamico e usarlo nella convalida invece di K1:K1000. Menu /Inserisci /Nome /Definisci; come nome scrivi DINARANGE (a piacere), come Riferito a scrivi

Codice: Seleziona tutto

=SCARTO(LIST_FOR!$K$1;0;0;SOMMA(--(LUNGHEZZA(LIST_FOR!$K$1:$K$100)>0));1)

Completi con Ok. Poi in colonna J di Documenti imposti la convalida usando l' intervallo DINARANGE.

Per la terza domanda, perdona se una volta dici " secondo te è possibile riuscire ad inserire COME FACEVO PRIMA le lettere iniziali del nome da digitare e premere invio per velocizzare la ricerca?" e adesso dici "La macro utilizzata non mi sembra dia questo risultato." non so che pensare perche' la macro da utilizzare (e che devi inserire nel codice che ti ho proposto il 10-3 pomeriggio) e' quella che tu usavi "prima", se funzionava prima dovrebbe funzionare anche dopo.

Per la data, il separatore e' un simbolo convenzionale che separa i campi in gioco (gg, mm, aa), e puo' essere lo slash oppure il trattino; sicche' puoi scrivere 01-3-10 e diventa 1 marzo 2010; ma puoi anche scrivere 1-3 (o 1/3) e prende l' anno corrente; e se vuoi scrivere "oggi" basta Contr+PuntoeVirgola. Insomma a sostituire una convenzione con un' altra (ggmmaa) non ne vedo il vantaggio; inoltre sono contrario a sostituire i valori introdotti dall' utente (ne perde la tracciabilita' dei dati). Insomma se vuoi farlo lo fai scrivendo 110310 e poi in una cella adiacente usi la funzione DATA(Anno;Mese;Giorno) per calcolare la data ufficiale.

Per lo scdenziario, se hai la data di un documento (es 30 gennaio 2010) e i termini di scadenza (es 90 gg), calcoli la data di scadenza sommando la data del documento e i termini di scadenza.

Ciao.