[Excel] Convalida Dati e Righe Vuote

[dottbarbi]

Buongiorno a tutti.

Ho un problema elementare che però non riesco a risolvere.
Sul Foglio1 ho un Listino composto da potenzialmente infinite righe e 3 colonne comprese alcune righe vuote.
Ho nominato la zona A$:C$ "Listino"; la zona A$:A$ "Codice", La zona B$:B$ "Descrizione", La zona C$:C$ "Prezzo". I nomi sono definiti a livello di cartella di lavoro.
Nel Foglio2 ad esempio alla cella A1 inserisco il codice in una cella che valido con la Convalida Dati - Consenti Elenco - Origine: =Codice. questo fa si che attraverso la formula =INDICE(Listino; CONFRONTA(I26;Codice;); 2) la cella A2 mi propone la descrizione del codice scelto presa dal Listino al foglio1.

Mi si presenta correttamente il menu a tendina con l'elenco dei codici come riportati nel Foglio1 che però mi riporta anche gli spazi vuoti nonostante io abbia spuntato l'opzione ignora celle vuote.

Come posso eliminare tali spazi vuoti dalla Convalida Dati senza modificare il Listino nel Foglio1?

Grazie

[Anthony47]

Per quello che ne so, excel assume che una cella vuota in un elenco di convalida sia intenzionalmente vuota, quindi e' parte dell' elenco.
La cosa piu' semplice che mi viene in mente:
-con una macro di worksheet_change intercetti la modifica all' interno del tuo elenco originale, copi l' elenco in un' area libera e la ordini in modo da lasciare le celle vuote in coda (oppure usi un range dinamico per far riferimento solo alle celle piene), e poi usi questo nuovo elenco come sorgente convalida.
(Potresti dover usare un altro evento compatibile con la tua struttura dati, es Worksheet_Activate, per innescare tale processo)

Se non basta lo spunto, posta ancora e vedremo come procedere insieme.
Ciao

[dottbarbi]

Leggendo alcuni post similari all'argomento mi era venuta in mente una soluzione simile, però, premessa la mia conoscenza non eccelsa di excel, la soluzione non mi sembrava ottimale.

Pensavo che so ad una specie di formula da applicare alla convalida dati, anche se forse sarebbe molto più semplice forse ridefinire ogni volta l'intervallo a livello di cartella di lavoro.

Il fatto è che l'applicazione che sto elaborando dovrà essere usata da persone con scarse conoscenza di pc, quindi più semplice possibile.
Il foglio1 è un Listino con tanto di righe vuote ed altro già impaginato per la stampa dove gli ho lasciando ampie possibilità di modifica definendo le varie aree con colonne intere, per evitare che magari aggiungendo un articolo questo non venisse visto dalla formula di ricerca nel foglio2.

Cosa intendi quando dici:....oppure usi un range dinamico per far riferimento solo alle celle piene..

L'argomento mi suona interessante ma sconosciuto.

Ciao e Grazie

[Anthony47]

Il problema principale che vuoi eliminare quale e'
-gli spazi vuoti nell' elenco perche' cosi' si possono inserire spazi vuoti nella cella di convalida?
-gli spazi vuoti nell' elenco perche' danno fastidio nella scelta dei valori?

Per quanto riguarda la domanda "Cosa intendi quando dici:....oppure usi un range dinamico etc etc", tale metodo e' un complemento alla procedura suggerita nel post precedente, per cui le celle vuote o le lasci in coda oppure le escludi proprio (dopo aver fatto la copia e l' ordinamento) usando una definizione di range "dinamico"; ma si tratta di una ciliegina che si puo' aggiungere quando hai fatto la torta.

Ciao, fatti sentire.

[dottbarbi]

Il problema fastidioso è come tu hai ipotizzato è la presenza di spazi vuoti nell'elenco per la scelta dei valori.
(che corrispondono alle righe lasciate vuote nel listino per esigenze di impaginazione ed alle celle in corrispondenza dei titoli).
Soprattutto quelle in mezzo a gruppi di codici, non tanto quelli in fondo che potrebbero essere un fastidio trascurabile.

Ti posto un esempio:

COLONNA A COLONNA B COLONNA C
(cella vuota) LISTINO 2009
(riga vuota)
(cella vuota) Articoli di tipo 1
(riga vuota)
Art. a Descrizione a Prezzo a
Art. b Descrizione b Prezzo b
(riga vuota)
(cella vuota) Articoli di tipo 2
(riga vuota)
Art. c Descrizione c Prezzo c
Art. d Descrizione d Prezzo d
(righe vuote)
.....
Ovviamente la posizione delle righe vuote non è fissa perché il listino può essere modificato in qualsiasi momento.
Dato che ho definito come "codice" tutta la colonna A la convalida mi presenta una situazione di codici alternata a spazi vuoti, mentre io vorrei che mi proponesse solo le celle in colonna A dove è presente del contenuto.

Ciao

[dottbarbi]

Il problema fastidioso è come tu hai ipotizzato è la presenza di spazi vuoti nell'elenco per la scelta dei valori.
(che corrispondono alle righe lasciate vuote nel listino per esigenze di impaginazione ed alle celle in corrispondenza dei titoli).
Soprattutto quelle in mezzo a gruppi di codici, non tanto quelli in fondo che potrebbero essere un fastidio trascurabile.

Ti posto un esempio:

COLONNA A ! COLONNA B ! COLONNA C
(cella vuota) ! LISTINO 2009 !
(riga vuota)
(cella vuota) ! Articoli di tipo 1 !
(riga vuota)
Art. a ! Descrizione a ! Prezzo a
Art. b ! Descrizione b ! Prezzo b
(riga vuota)
(cella vuota) ! Articoli di tipo 2 !
(riga vuota)
Art. c ! Descrizione c ! Prezzo c
Art. d ! Descrizione d ! Prezzo d
(righe vuote)
.....
Ovviamente la posizione delle righe vuote non è fissa perché il listino può essere modificato in qualsiasi momento.
Dato che ho definito come "codice" tutta la colonna A la convalida mi presenta una situazione di codici alternata a spazi vuoti, mentre io vorrei che mi proponesse solo le celle in colonna A dove è presente del contenuto.

Ciao

Ho inserito punti esclamativi ai cambi di colonna.

[Anthony47]

Allora devi procedere come ti ho suggerito nel primo post; per questo puoi usare la seguente procedura.
Parti dal foglio che ospita i tuoi dati: tasto dx sul tab del nome del foglio, scegli Visualizza codice per aprire e andare sull' editor delle macro, copia il seguente codice e incollalo nel frame di destra.

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If target.column>1 then Exit Sub
Application.EnableEvents=False
Range("A:A").copy Destination:=Sheets("Appoggio").Range("A1")
' ISTRUZIONI PER ORDINARE su foglio Appoggio:
Call Macro1
Application.EnableEvents=True
End Sub


Ti crei un foglio di servizio che chiami "Appoggio", in cui la macro creera' la copia dei dati che serviranno per la convalida.

Registri una macro per ordinare la copia dei dati, chiamandola Macro1 (il nome che ti verra' proposto): parti dal foglio dei tuoi dati iniziali, avvia Registra nuova macro, selezioni il foglio Appoggio, selezioni la colonna A, comandi l' ordinamento per coodice crescente, ritorni al foglio dei dati iniziali, fermi la registrazione;

Torni su excel, foglio Appoggio: Menu /Inserisci /Nome /Definisci; come Nome segni NewConv e come "Riferito a" inserisci

Codice: Seleziona tutto

=Scarto(Appoggio!$A$1;0;0;Conta.valori(Appoggio!$A:$A);1)

Chiudi con Ok.

Infine torni sul tuo foglio e imposti la convalida da elenco, scegliendo come origine =NewConv

Fai sapere dove arrivi, ciao.

[dottbarbi]

Ho provato ad inserire la parte di codice che mi hai scritto.
Ha funzionato la prima volta copiando la colonna A nel foglio appoggio.
Dato che mi interessava copiare da A ad E, ho cancellato il contenuto del foglio "Appoggio" ho sostituito Range("A:A") con Range("A:E") e provato a modificare il listino di partenza, ma ora non accade niente.

Ecco un copia/incolla del codice inserito:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
Application.EnableEvents = False
Range("A:E").Copy Destination:=Sheets("Appoggio").Range("A1")
'ISTRUZIONI PER ORDINARE su foglio Appoggio:
Call Macro1
Application.EnableEvents = True
End Sub

il foglio "Appoggio" rimane vuoto e non viene visualizzato alcun errore

[dottbarbi]

Ho provato ad inserire la parte di codice che mi hai scritto.
Ha funzionato la prima volta copiando la colonna A nel foglio appoggio.
Dato che mi interessava copiare da A ad E, ho cancellato il contenuto del foglio "Appoggio" ho sostituito Range("A:A") con Range("A:E") e provato a modificare il listino di partenza, ma ora non accade niente.

Ecco un copia/incolla del codice inserito:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
Application.EnableEvents = False
Range("A:E").Copy Destination:=Sheets("Appoggio").Range("A1")
'ISTRUZIONI PER ORDINARE su foglio Appoggio:
Call Macro1
Application.EnableEvents = True
End Sub

il foglio "Appoggio" rimane vuoto e non viene visualizzato alcun errore

Mi correggo, ho chiuso e riaperto il file ed ora funziona.
Cerco di andare avanti e ti faccio sapere.

Grazie

[Anthony47]

I dati su Appoggio si copiano solo quando modifichi qualcosa in col A, ho dimenticato di dirtelo.

Ciao.

[dottbarbi]

1) Allora. I dati si aggiornano sul foglio "Appoggio" ma preferirei si aggiornassero in base alle modifiche su colonna 2 (dove risiede il codice).
Quindi penso che andrà modificato l'istruzione sotto, se facessi così?:
If Target.Column <> 2 Then Exit Sub

2)Tenendo sempre conto che il codice che desidero convalidare si trova in colonna 2 come diventa l'espressione sotto:

=Scarto(Appoggio!$A$1;0;0;Conta.valori(Appoggio!$A:$A);1)

Ho provato a sostituire A con B ma la convalida mi dice che l'espressione da luogo ad un errore.

...che tristezza che mi faccio..

[Anthony47]

Nel primo post avevi pero' scritto che il codice era in col A...
Comunque:
Ok per

Codice: Seleziona tutto

If Target.Column <> 2 Then Exit Sub


Per il nome:

Codice: Seleziona tutto

=Scarto(Appoggio!$A$1;0;1;Conta.valori(Appoggio!$B:$B);1)

Ciao.

[dottbarbi]

Hai ragione, è che la prima colonna contiene sempre il solito valore "Art." per questo nel primo post non l'avevo considerata, scusa.

Ho fatto come mi hai detto tu ed ora la convalida mi mostra esattamente l'elenco dei codici senza alcuna cella vuota!

Grazie molte sia per la consulenza che per la pazienza!

Se ti capita indicami qualche bel manuale di vba per excel da studiare che l'argomento mi intriga, ma se non mi faccio un po' di basi serie.

E pensare che sono un vecchio programmatore in Basic...,sigh.

Ciao