[Excel] Convalida dati doppia particolare

[dottbarbi]

Buongiorno a tutti.

E' possibile creare una convalida dati così strutturata:

Nel foglio 1 ho una tabella composta da codice in colonna A e descrizione in colonna B.

Nel foglio 2 la cella A1 deve presentarmi ho un menu a tendina contenente l'elenco dei valori della colonna A del foglio 1 (e fin qui nessun problema) oppure il corrispondente codice che ottengo se selezione una voce dalla cella B1 che a sua volta deve presentare un menu a tendina contenente l'elenco dei valori della colonna B del foglio 1 o la descrizione corrispondente al codice selezionato nella cella A1.

Il pratica vorrei una convalida dati doppia che funzioni in tutti e due i versi.

[Flash30005]

Penso che si creerebbe un riferimento circolare
Dovresti dare priorità ad uno degli elenchi

ciao

[Flash30005]

Prova questo cartella Excel
nel foglio 1
c'è l'elencoA e l'elencoB
Nel foglio2 puoi scegliere in A1 i valori in elencoA e in B1 avrai il corrispondente di elencoB e viceversa
http://rapidshare.com/files/256162324/C ... 2.zip.html

Prova e fai sapere
Ciao


P.S. per evitare il riferimento circolare ho messo la variabile Nega

Avviso: Modificato indirizzo Download alle ore 19:17

[dottbarbi]

Grazie!

Hai capito perfettamente quello che avevo bisogno di fare, cioè scegliere alternativamente o selezionando un codice o una descrizione.

Ora non mi resta che riuscire ad implementarla all'interno del mio foglio nella formula di ricerca (SPERIAMO BENE).


Ciao

[dottbarbi]

Grazie!

Hai capito perfettamente quello che avevo bisogno di fare, cioè scegliere alternativamente o selezionando un codice o una descrizione.

Ora non mi resta che riuscire ad implementarla all'interno del mio foglio nella formula di ricerca (SPERIAMO BENE).


Ciao

Allora dopo aver dato un occhio alle due macro che riporto per gli utenti del forum mi pongo alcune domande

Sub MacroelencoA()
Nega = 1
Worksheets("Foglio2").Range("B1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],Foglio1!R[1]C[-1]:R[10]C,2,FALSE)"
End Sub
Sub MacroelencoB()
Nega = 1
Worksheets("Foglio2").Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=OFFSET(Foglio1!R1C2,MATCH(Foglio2!RC[1],Foglio1!R[1]C[1]:R[10]C[1],0),-1,-1)"
End Sub

1) Dato che le mie celle di inserimento sono comprese tra riga 26 e 46 è possibile inserirle all'interno di un ciclo iterativo (nel senso che avrò un inserimento in A26 e B26, uno in A27 e B27 ... fino a A46 e B47)?

2) Nelle formule OFFSET e VLOOKUP posso sostituire i riferimenti RC con nomi di intervalli?

[Flash30005]

Forse allora è meglio adottare questa tecnica

Codice: Seleziona tutto

Sub MacroelencoA()
nega = 1
Ur = Worksheets("Foglio1").Range("A" & Rows.Count).End(xlUp).Row
For i = 26 To Ur
If Worksheets("Foglio2").Range("A1").Value = Worksheets("Foglio1").Range("A" & i).Value Then Worksheets("Foglio2").Range("B1").Value = Worksheets("Foglio1").Range("B" & i).Value
Next i
End Sub
Sub MacroelencoB()
nega = 1
Ur = Worksheets("Foglio1").Range("B" & Rows.Count).End(xlUp).Row
For i = 26 To Ur
If Worksheets("Foglio2").Range("B1").Value = Worksheets("Foglio1").Range("B" & i).Value Then Worksheets("Foglio2").Range("A1").Value = Worksheets("Foglio1").Range("A" & i).Value
Next i
End Sub


Le righe prese in considerazione sono dalla riga 26 a quante righe sono con dati (anche 65.000 o solo 47 se i dati arrivano fino a 47)

Ciao

[dottbarbi]

Forse nell'ultimo post non sono riuscito a spiegare bene la mia problematica.

Nel Foglio1 che nel mio caso si chiama "Listino" ho le Colonnna B fino a n elementi che contiene il codice dell'articolo che ho definito come intervallo "Codice" e la Colonna C anch'essa fino a n elementi che contiene la descrizione dell'articolo definita come intervallo "Descrizione".

Nel Foglio2 nelle righe da 26 a 47 voglio inerire i dati con la metodologia da te compresa.

Attualmente ho impostato su ogni cella in colonna B (B26,B27....B47) un semplice metodo di convalida: Consenti: Elenco - Origine: Codice. Così mi si apre un menu a discesa con tutti i codici; mentre nelle corrispondenti colonne C c'è la formula:
=SE(B26<>"";INDICE(Listino; CONFRONTA(B26;Codice;); 3);"") (questa è quella relativa alla riga 26).

Quindi quello che mi servirebbe sarebbe una sorta di confronto del tipo: Se è stata selezionato un elemento tramite la validazione dati nella cella in colonna B esegui la formula, altrimenti devi convalidare da un elenco che come origine dovrebbe avere l'intervallo Descrizione. Quindi nel caso di selezione dalla cella in colonna C si dovrebbe aggiornare l'elemento corrispondente in colonna B.

Spero di aver chiarito e non confuso le idee.

[Anthony47]

Scusate, non ho seguito questa discussione e faccio fatica a ricostruire.
Se la richiesta e' di avere sul secondo foglio due colonne, la prima convalidata da elenco=codici e la seconda da elenco=descrizione, volendo pero' contemporaneamente avere nella cella una formula che dato il codice estrae la descrizione oppure data la descrizione estrae il codice, beh allora la presenza di una formula nella cella e' incompatibile con la presenza di un dato digitato (o scelto da elenco), nel senso che appena scrivo in cella la formula va a farsi benedire per sempre, salvo che non la ripristini con una macro di tipo Worksheet_Change: un change in col 1 ripristina la formula in colonna 2, e viceversa.
In alternativa, ti fai due colonne di servizio per inputare a scelta il codice o la descrizione, ambedue soggette a convalida da elenco, e poi ti ricostruisci nelle colone adiacenti i risultati: per il codice

Codice: Seleziona tutto

=Se(E'_presente_il_codice_inputato;prendi_la_cella_col_codice_inputato;cerca.vert(La_descrizione;tabella_anagrafica;1;0))

Per la descrizione sara' una formula complementare, ma dando la priorita' al codice, cioe'

Codice: Seleziona tutto

=Se(E'_presente_il_codice_inputato;Cerca.vert(Il_Codice;Tabella_anagrafica;2;0);La_Descrizione_inputata)

Ciao.

[dottbarbi]

Scusate, non ho seguito questa discussione e faccio fatica a ricostruire.
Se la richiesta e' di avere sul secondo foglio due colonne, la prima convalidata da elenco=codici e la seconda da elenco=descrizione, volendo pero' contemporaneamente avere nella cella una formula che dato il codice estrae la descrizione oppure data la descrizione estrae il codice, beh allora la presenza di una formula nella cella e' incompatibile con la presenza di un dato digitato (o scelto da elenco), nel senso che appena scrivo in cella la formula va a farsi benedire per sempre, salvo che non la ripristini con una macro di tipo Worksheet_Change: un change in col 1 ripristina la formula in colonna 2, e viceversa.
In alternativa, ti fai due colonne di servizio per inputare a scelta il codice o la descrizione, ambedue soggette a convalida da elenco, e poi ti ricostruisci nelle colone adiacenti i risultati: per il codice

Codice: Seleziona tutto

=Se(E'_presente_il_codice_inputato;prendi_la_cella_col_codice_inputato;cerca.vert(La_descrizione;tabella_anagrafica;1;0))

Per la descrizione sara' una formula complementare, ma dando la priorita' al codice, cioe'

Codice: Seleziona tutto

=Se(E'_presente_il_codice_inputato;Cerca.vert(Il_Codice;Tabella_anagrafica;2;0);La_Descrizione_inputata)

Ciao.

In realtà il file che mi ha mandato Flash se gli dai un occhio era molto vicino all'obbiettivo.

Io ero partito inizialmente pensando ad una sorte di verifica della condizione all'interno della convalida.

Ad esempio:

Per Cella contenente codice:
Origine: = SE (Cella contenente descrizione <>""; INDICE(Listino; CONFRONTA(Codice inserito;Codice;);2);"");Codice)
In parole: se esiste già una descrizione cerca il codice corrispondente dalla matrice listino, altrimenti visualizza l'elenco a discesa contenente i codici.

Per Cella contenente descrizione stesso ragionamento ma naturalmente non funziona perchè la convalida dati anche se mi suggerisce il menu solo in mancanza di una selezione del codice in caso della descrizione e viceversa, non mi esegue la formula nell'altro caso.

[Anthony47]

Ma devo per forza leggere tutto per capire quale era l' obiettivo?

Ciao.

[dottbarbi]

Ma devo per forza leggere tutto per capire quale era l' obiettivo?

Ciao.

Bhe, cercavo di essere il più esauriente possibile..forse sono stato un po' ridondante...

[Anthony47]

Se la richiesta e' di avere sul secondo foglio due colonne, la prima convalidata da elenco=codici e la seconda da elenco=descrizione, volendo pero' contemporaneamente avere nella cella una formula che dato il codice estrae la descrizione oppure data la descrizione estrae il codice...
Allora mi pare che la macro di Flash faccia gia' una cosa analoga; andrebbe solo adattata alle ultime informazioni che hai messo.
Io pero' suggerisco questa macro:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B26:C47")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 Then
Target.Offset(0, 1).Value = _
Sheets("Listino").Range("Descrizione").Range("A1").Offset(Application.WorksheetFunction.Match(Target, Sheets("Listino").Range("Codice"), 0) - 1, 0)
Else
Target.Offset(0, -1).Value = _
Sheets("Listino").Range("Codice").Range("A1").Offset(Application.WorksheetFunction.Match(Target, Sheets("Listino").Range("Descrizione"), 0) - 1, 0)
End If
Application.EnableEvents = True
End Sub


La mia macro assume che ci sia un intervallo denominato Codice e un altro denominato Descrizione sul foglio Listino; va inserita sul "modulo di codice" di Foglio2:
-tasto dx sul tab col nome Foglio2; scegli Visualizza codice
-copi il codice e lo incolli sul frame vuoto di dx (se c' e' gia' un' altra macro di Worksheet_Change essa va eliminata)

A questo punto, vai su Foglio2, provi a inputare nell' area B26:C47 alternativamente un codice o una descrizione e la macro ti inserisce "l' altro valore"; B26:C47 sono le celle con la convalida su Codice e Descrizione.

Spero che questo funzioni anche per te.
Ciao, fai sapere.

[dottbarbi]

Ciao Antony,

Sto studiando un po' la tua funzione prima di inserirla perchè vorrei riuscire a capire quello che mi stai suggerendo.
Una domanda: Ma questa funzione mi permetterebbe di mantenere la convalida con elenco sia nel codice che nella descrizione oppure no?


Ciao

[Anthony47]

La macro fa quello che ho capito era la richiesta, come descritto nel post precedente.
Ovviamente mantiene la convalida, se inserita.

Ciao.

[dottbarbi]

La macro fa quello che ho capito era la richiesta, come descritto nel post precedente.
Ovviamente mantiene la convalida, se inserita.

Ciao.

Grazie, per la info, è che dato che il foglio nel quale la dovrei infilare è molto più complesso di come l'ho rappresentato (ho cercato di semplificare) devo cercare di capire ogni azione della macro da te suggeritami per poterla riadattare.

Ciao

[dottbarbi]

Penso di aver compreso la formula ma quando ho provato ad implementarla non ha funzionato, forse perché ho delle celle unite e l'immissione è intervallata (non l'avevo detto ma era per non rendere problematico il tutto..)

Il Codice viene inserito dalla riga 26 alla 46 ma la colonna di riferimento è l'unione delle colonne da H ad O.
La Descrizione è nelle corrispondenti righe dalla 26 alla 46 e la colonna di riferimento è l'unione delle colonne da P ad AZ.

Questa pagina di immissione è poi replicata altre due volte per un totale di 3 pagine, quindi le immissioni avvengono nelle righe:

- dalla 26 alla 46; quindi il Range è ("H26:AZ46") (Colonne Codice+Colonne Descrizione di Pagina 1)
- dalla 84 alla 104; quindi il Range è ("H84:AZ104") (Colonne Codice+Colonne Descrizione di Pagina 2)
- dalla 142 alla 162; quindi il Range è ("H142:AZ162") (Colonne Codice+Colonne Descrizione di Pagina 3)

[dottbarbi]

Penso di aver compreso la formula ma quando ho provato ad implementarla non ha funzionato, forse perché ho delle celle unite e l'immissione è intervallata (non l'avevo detto ma era per non rendere problematico il tutto..)

Il Codice viene inserito dalla riga 26 alla 46 ma la colonna di riferimento è l'unione delle colonne da H ad O.
La Descrizione è nelle corrispondenti righe dalla 26 alla 46 e la colonna di riferimento è l'unione delle colonne da P ad AZ.

Questa pagina di immissione è poi replicata altre due volte per un totale di 3 pagine, quindi le immissioni avvengono nelle righe:

- dalla 26 alla 46; quindi il Range è ("H26:AZ46") (Colonne Codice+Colonne Descrizione di Pagina 1)
- dalla 84 alla 104; quindi il Range è ("H84:AZ104") (Colonne Codice+Colonne Descrizione di Pagina 2)
- dalla 142 alla 162; quindi il Range è ("H142:AZ162") (Colonne Codice+Colonne Descrizione di Pagina 3)

Per la prima pagina la funzione dovrebbe quindi diventare così:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H26:AZ46")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Column = 8 Then
Target.Offset(0, .Value = _
Sheets("Listino").Range("Descrizione").Range("A1").Offset(Application.WorksheetFunction.Match(Target, Sheets("Listino").Range("Codice"), 0) - 1, 0)
Else
If Target.Column = 16 Then
Target.Offset(0, -8).Value = _
Sheets("Listino").Range("Codice").Range("A1").Offset(Application.WorksheetFunction.Match(Target, Sheets("Listino").Range("Descrizione"), 0) - 1, 0)
End If
End If
Application.EnableEvents = True
End Sub

..invece non succede niente....

[Anthony47]

Non smettero' mai di sconsigliare l' uso delle celle unite: sono una trappola in cui cadono talvolta anche gli esperti...
Io uso quella tecnica solo in fogli destinati alla "presentazione", non all' elaborazione.

Immagino che sia tardi per consigliarti di evitare la trappola; sono pero' in viaggio, ho bisogno di trovare il tempo di collaudare un paio di cose prima di poterti rispondere.

Ciao.

[dottbarbi]

Ok, aspetto, no problem.
In effetti con le celle unite l'indicazione dell'indirizzo delle celle per le macro non sempre è chiarissimo, ma il foglio che ho realizzato è sia foglio di calcolo che in un qualche senso formato di stampa.

[Anthony47]

Il foglio che ho realizzato è sia foglio di calcolo che in un qualche senso formato di stampa.

Cosa che consiglio di evitare, la prossima volta...

Per la struttura dati descritta la macro e' la seguente:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B26:P47")) Is Nothing Then Exit Sub
On Error GoTo ExitA
Application.EnableEvents = False
If Target.Column = 8 Then
Target.Offset(0, 1).Value = _
Sheets("Listino").Range("Descrizione").Range("A1").Offset(Application.WorksheetFunction.Match(Target, Sheets("Listino").Range("Codice"), 0) - 1, 0)
Else
Target.Offset(0, -8).Value = _
Sheets("Listino").Range("Codice").Range("A1").Offset(Application.WorksheetFunction.Match(Target, Sheets("Listino").Range("Descrizione"), 0) - 1, 0)
End If
ExitA:
Application.EnableEvents = True
End Sub


Ho aggiunto l' istruzione OnError per uscire con gli eventi abilitati anche in caso di errore, non si sa mai.

Ciao.