Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[Excel] Convalida dati doppia particolare

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[Excel] Convalida dati doppia particolare

Postdi dottbarbi » 15/07/09 08:11

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.
dottbarbi
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Sponsor
 

Re: [Excel] Convalida dati doppia particolare

Postdi Flash30005 » 15/07/09 15:32

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

ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel] Convalida dati doppia particolare

Postdi Flash30005 » 15/07/09 16:24

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
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 16/07/09 14:45

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
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 16/07/09 16:31

dottbarbi ha scritto: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?
dottbarbi
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi Flash30005 » 16/07/09 20:38

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
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 17/07/09 14:26

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.
dottbarbi
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi Anthony47 » 17/07/09 14:55

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.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 17/07/09 16:14

Anthony47 ha scritto: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.
dottbarbi
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi Anthony47 » 17/07/09 19:43

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

Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 20/07/09 16:27

Anthony47 ha scritto: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...
dottbarbi
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi Anthony47 » 21/07/09 23:32

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.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 22/07/09 08:10

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
dottbarbi
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi Anthony47 » 22/07/09 22:52

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

Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 23/07/09 15:46

Anthony47 ha scritto: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
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 24/07/09 14:38

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
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 24/07/09 15:31

dottbarbi ha scritto: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, 8).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....
dottbarbi
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi Anthony47 » 26/07/09 10:48

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.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Convalida dati doppia particolare

Postdi dottbarbi » 27/07/09 07:45

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.
dottbarbi
Utente Junior
 
Post: 34
Iscritto il: 22/05/07 16:57

Re: [Excel] Convalida dati doppia particolare

Postdi Anthony47 » 28/07/09 16:26

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.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "[Excel] Convalida dati doppia particolare":


Chi c’è in linea

Visitano il forum: Nessuno e 9 ospiti