Elenco a discesa senza celle vuote
Pino
"A1:A5".
Se l'intervallo presenta delle celle vuote, l'elenco a discesa ad esso
collegato visualizzera' anch'esso quest'ultime.
Mi spiego meglio con un esempio:
Se
A1=pippo
A2=VUOTA
A3=pluto
A4=VUOTA
A5=VUOTA
allora l'elenco a discesa mi deve visualizzare solamente i due dati "pippo"
e "pluto" *senza visualizzarmi anche le celle vuote*.
L'elenco a discesa dovra' contenere un numero di elementi variabili in base
a quanti dati *non vuoti* sono presenti nell'intervallo "A1:A5".
--
Ciao e grazie a tutti
Pino
Alfonso Spagnuolo
news:bv6ioq$oo4ed$1...@ID-89213.news.uni-berlin.de...
**********************
Perché non filtri prima l'intervallo è poi applichi la
Convalida-Elenco?
Filtro Automatico-Celle Non Vuote
Ciao
Alfonso Spagnuolo
SergioBS
una soluzione in VB che automaticamente.....
Sergio
"Alfonso Spagnuolo" <alfocentauriTOGL...@tiscali.it> ha scritto
nel messaggio news:%23Y60Ybd...@TK2MSFTNGP12.phx.gbl...
SergioBS
"Alfonso Spagnuolo" <alfocentauriTOGL...@tiscali.it> ha scritto
nel messaggio news:%
> Perché non filtri prima l'intervallo è poi applichi la
> Convalida-Elenco?
>
> Filtro Automatico-Celle Non Vuote
>
Caro Alfonso,
premesso che secondo me il tipo di soluzione proposto è inadeguato..per
curiosità ho provato la tua soluzione, ma non funziona... capisco la voglia
di contribuire, ma.....
Sergio
PS: Scusa ma stasera mi sento rombiballe....
fernando cinquegrani
"Pino" <mai...@tin.it> ha scritto nel messaggio news:bv6ioq$oo4ed$1...@ID-89213.news.uni-berlin.de...
: Nella cella "B1" ho creato un elenco a discesa collegato all'intervallo
in A1:A5 l'elenco da te indicato
in B1:B5 =RIF.RIGA()-CONTA.VUOTE(INDIRETTO("$A$1:A" & RIF.RIGA())) [inserire con Cntl+Maiusc+Invio]
in C1:C5
=SE(VAL.ERRORE(INDICE($A$1:$A$5;CONFRONTA(RIF.RIGA();$B$1:$B$5;FALSO)));"";INDICE($A$1:$A$5;CONFRONTA(RIF.RIGA();$B$1:$B$5;FALSO)))
[inserire con Cntl+Maiusc+Invio]
in D1 l'origine della tua cella di convalida sarà
=SCARTO($C$1:$C$5;0;0;RIGHE($A$1:$A$5)-SOMMA(1*($C$1:$C$5=""));1)
ci sono sicuramente modi di semplificare.
non ne ho il tempo.
.f
http://www.prodomosua.it
Pino
messaggio news:#xcuVVe5...@TK2MSFTNGP10.phx.gbl...
> "Pino" <mai...@tin.it> ha scritto nel messaggio
news:bv6ioq$oo4ed$1...@ID-89213.news.uni-berlin.de...
> : Nella cella "B1" ho creato un elenco a discesa collegato all'intervallo
> : "A1:A5".
> :
> : Mi spiego meglio con un esempio:
> :
> : A1=pippo
> : A2=VUOTA
> : A3=pluto
> : A4=VUOTA
> : A5=VUOTA
> :
> : allora l'elenco a discesa mi deve visualizzare solamente i due dati
"pippo"
> : e "pluto" *senza visualizzarmi anche le celle vuote*.
> in A1:A5 l'elenco da te indicato
> in B1:B5 =RIF.RIGA()-CONTA.VUOTE(INDIRETTO("$A$1:A" & RIF.RIGA()))
[inserire con Cntl+Maiusc+Invio]
> in C1:C5
>
=SE(VAL.ERRORE(INDICE($A$1:$A$5;CONFRONTA(RIF.RIGA();$B$1:$B$5;FALSO)));"";I
NDICE($A$1:$A$5;CONFRONTA(RIF.RIGA();$B$1:$B$5;FALSO)))
> [inserire con Cntl+Maiusc+Invio]
> in D1 l'origine della tua cella di convalida sarà
> =SCARTO($C$1:$C$5;0;0;RIGHE($A$1:$A$5)-SOMMA(1*($C$1:$C$5=""));1)
Funziona alla grande!!!!!!, mi aspettavo una delle sue *strabilianti e
geniali* soluzioni con l'uso delle formule matriciali, siamo nel campo del
Fantaexcel... :-) ......, ne approfitto per chiederle quanto segue:
Con l'uso delle formule matriciali da lei suggerite, ho in sostanza l'elenco
dei dati dell' intervallo (A1:A5) ripetuto anche nell'intervallo (C1:C5),
anche se opportunamente filtrato.
E' possibile scrivere del codice che mi esamini l'intervallo (A1:A5) e mi
crei l'opportuno elenco, filtrato dalle celle vuote, da collegare alla cella
(la B1) dove e' presente l'elenco a discesa?
Chiaramente l'intervallo (A1:A5) di partenza *non* deve essere sovrascritto
dai dati filtrati.
Credo che devo cmq usare una matrice di appoggio che conterra' i dati
filtrati, ma poi sorge il problema di come collegarla all'elenco a discesa.
In pratica, vorrei evitare di copiare gli stessi dati (anche se filtrati)
nell'intervallo (C1:C5), avendo in tal modo la massima ottimizzazione.
Ciao e grazie mille
Pino
fernando cinquegrani
: E' possibile scrivere del codice che mi esamini l'intervallo (A1:A5) e mi
: crei l'opportuno elenco, filtrato dalle celle vuote, da collegare alla cella
: (la B1) dove e' presente l'elenco a discesa?
: Chiaramente l'intervallo (A1:A5) di partenza *non* deve essere sovrascritto
: dai dati filtrati.
clic destro sulla scheda/foglio > visualizza codice
nell'editor vba incolla
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim unici As New Collection, x As Range
Dim j As Long, alfa As String, myrange As Range, myvalid As Range
Set myrange = Range("A1:A10")
Set myvalid = Range("B1")
If Application.Intersect(Target, myvalid) Is Nothing Then Exit Sub
On Error Resume Next
For Each x In myrange
If x <> "" Then unici.Add x, x.Text
Next x
For j = 1 To unici.Count
alfa = alfa & unici(j) & ","
Next j
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="" & alfa & ""
End With
End Sub
le due istruzioni
Set myrange = Range("A1:A5")
Set myvalid = Range("B1")
definiscono rispettivamente il range dei dati originari e la cella di convalida.
l'elenco di convalida sarà costituito dai dati originari,
depurati dei valori nulli e dei valori duplicati
e ordinati alfabeticamente.
buonanotte
.f
fernando cinquegrani
Microsoft MVP
http://www.prodomosua.it
Barbara
> "fernando cinquegrani" <f.cinq...@xroxxmxxxa.it> ha scritto nel
> messaggio news:#xcuVVe5...@TK2MSFTNGP10.phx.gbl...
>
> > "Pino" <mai...@tin.it> ha scritto nel messaggio
> news:bv6ioq$oo4ed$1...@ID-89213.news.uni-berlin.de...
> > : Nella cella "B1" ho creato un elenco a discesa collegato
all'intervallo
> > : "A1:A5".
> > :
> > : Mi spiego meglio con un esempio:
> > :
> > : A1=pippo
> > : A2=VUOTA
> > : A3=pluto
> > : A4=VUOTA
> > : A5=VUOTA
> > :
> > : allora l'elenco a discesa mi deve visualizzare solamente i due dati
> "pippo"
> > : e "pluto" *senza visualizzarmi anche le celle vuote*.
>
Un'altra soluzione senza VBA:
in B1:B5:
=INDICE(rng;PICCOLO(SE(RIF.RIGA(rng)=SE(rng<>"";CONFRONTA(rng;rng;));RIF.RIG
A(rng));RIF.RIGA(rng)))
origine della convalida:
=SCARTO($B$1;;;CONTA.VALORI($B:$B)-CONTA.SE($B:$B;#NUM!))
Ciao
Barbara
Alfonso Spagnuolo
> Caro Alfonso,
> premesso che secondo me il tipo di soluzione proposto è
inadeguato..per
> curiosità ho provato la tua soluzione, ma non funziona...
capisco la voglia
> di contribuire, ma.....
>
> Sergio
>
> PS: Scusa ma stasera mi sento rombiballe....
>
****************************
Gentile Sergio BS,
cosa non funziona? La mia soluzione oppure il "tuo" modo di
applicarla?
Hai un elenco che presenta celle vuote, che vengono filtrate
con Filtro automatico-Non Vuote, in quanto con questo
comando selezioni SOLO le celle con valori (testuali o
numerici).
A siffatto elenco vuoi applicare un elenco a discesa? Allora
Convalida-Elenco fa al caso tuo (e immagino tu "sappia" come
funziona).
Hai scritto che questo elenco è variabile, in quanto
conterrà altri dati? Confermo ancora che Convalida-elenco fa
al caso tuo: prova ad aggiungere delle righe all'elenco
siffatto e vedrai che l'elenco a discesa sarà aggiornato con
i nuovi valori.
Aspetta...mi sembra già di sentire la tua obiezione:"E se
aggiungo dati al termine dell'elenco?" Ebbene, funziona
anche in questo caso: basta modificare il riferimento finale
dell'elenco in Convalida: se ad es. l'intervallo è A1:A10 e
pensi di aggiungere in futuro dati fino ad A20, puoi sempre
digitare questo intervallo in sede di convalida &A&1:&A&20.
Noterai che, non appena inserirai nuovi dati all'intervallo
suddetto, l'elenco stesso si aggiornerà, non era questo che
volevi?
E noterai anche che, con questa piccola modifica, non
comparirà NESSUNA cella vuota, cosa che invece volevi
evitare.
E allora? Non ti piace questa soluzione?
Tieni presente che la mia era solo una proposta e che la
soluzione di Fernando è sicuramente più funzionale e più
adatta alle tue esigenze.
Aggiungo un'ultima postilla: giustamente hai detto che ho
voglia di contribuire e lo farò con quel poco che so e
continuerò a farlo al meglio delle mie possibilità (che
aumenteranno di giorno in giorno, stanne certo!), e se ci
sarà qualcosa di sbagliato nei miei interventi, verrò
corretto da quanti ne sanno più di me e imparerò nuove cose.
Mi capita di frequente e non mi vergogno certo di ammettere
di non sapere tanto, ma...continuo lo stesso.
Motivo per cui, gentile Sergio BS, la tua ironia te la
rispedisco al mittente...con o senza Filtro automatico...
Alfonso Spagnuolo
Pino
"fernando cinquegrani" <f.cinq...@xroxxmxxxa.it> ha scritto nel
messaggio news:#xcuVVe5...@TK2MSFTNGP10.phx.gbl...
>
> "Pino" <mai...@tin.it> ha scritto nel messaggio
news:bv6ioq$oo4ed$1...@ID-89213.news.uni-berlin.de...
> : Nella cella "B1" ho creato un elenco a discesa collegato all'intervallo
> : "A1:A5".
> : Se l'intervallo presenta delle celle vuote, l'elenco a discesa ad esso
> : collegato visualizzera' anch'esso quest'ultime.
> :
> : A2=VUOTA
> : A3=pluto
> : A4=VUOTA
> : A5=VUOTA
>
> in B1:B5 =RIF.RIGA()-CONTA.VUOTE(INDIRETTO("$A$1:A" & RIF.RIGA()))
[inserire con Cntl+Maiusc+Invio]
> in C1:C5
>
=SE(VAL.ERRORE(INDICE($A$1:$A$5;CONFRONTA(RIF.RIGA();$B$1:$B$5;FALSO)));"";I
NDICE($A$1:$A$5;CONFRONTA(RIF.RIGA();$B$1:$B$5;FALSO)))
> [inserire con Cntl+Maiusc+Invio]
> in D1 l'origine della tua cella di convalida sarà
> =SCARTO($C$1:$C$5;0;0;RIGHE($A$1:$A$5)-SOMMA(1*($C$1:$C$5=""));1)
> ci sono sicuramente modi di semplificare.
> non ne ho il tempo.
In luogo di quest'ultima formula, ho inserito come *origine* la seguente:
=INDIRETTO("$C$1:$C$"&CONTA.VALORI(C1:C5))
che quando le celle "vuote" sono tutte in coda alla lista (come cio' si
verifica nell'intervallo C1:C5), sembra funzionare.
Cosa ne pensa????
Ciao e grazie mille
Pino
From: "fernando cinquegrani" <f.cinq...@xroxxmxxxa.it>
Newsgroups: microsoft.public.it.office.excel
Sent: Thursday, January 29, 2004 4:59 AM
Subject: Re: Elenco a discesa senza celle vuote
> : E' possibile scrivere del codice che mi esamini l'intervallo (A1:A5) e
mi
> : crei l'opportuno elenco, filtrato dalle celle vuote, da collegare alla
cella
> : (la B1) dove e' presente l'elenco a discesa?
> : Chiaramente l'intervallo (A1:A5) di partenza *non* deve essere
sovrascritto
> : dai dati filtrati.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim unici As New Collection, x As Range
> Dim j As Long, alfa As String, myrange As Range, myvalid As Range
> Set myrange = Range("A1:A10")
> Set myvalid = Range("B1")
> If Application.Intersect(Target, myvalid) Is Nothing Then Exit Sub
> On Error Resume Next
> For Each x In myrange
> If x <> "" Then unici.Add x, x.Text
> Next x
> For j = 1 To unici.Count
> alfa = alfa & unici(j) & ","
> Next j
> With Target.Validation
> .Delete
> .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
> xlBetween, Formula1:="" & alfa & ""
> End With
> End Sub
>
Il codice da lei scritto funziona perfettamente, con l'aiuto della guida Vba
ho cercato di studiarmelo nei dettagli per imparare cose a me nuove, come la
gestione dell'insieme "Collection" .
Se ho ben capito, nel codice ha utilizzato l'insieme collection "unici" in
luogo di un array, in quanto poi sarebbe stato un po' piu' complesso
eliminare gli eventuali elementi doppi presenti nell'intervallo di origine
(A1:A5).
Giusto?
Inoltre non sono riuscito a capire ne' a trovare qualcosa sulla guida in
merito all'argomento
(Formula1:="" & alfa & ""), in particolare, ho provato a sostituire
quest'ultima istruzione con la seguente:
(Formula1:=alfa)
e mi sembra che non ci sia differenza, almeno ai fini dei risultati
ottenuti, con la precedente assegnazione da lei impostata.
La guida dice semplicemente che il suddetto argomento puo' essere assegnato
anche ad una stringa con elementi separati da virgole (proprio come la
variabile stringa "alfa") e *non aggiunge* altro.
Ciao e grazie milleeeeeeee
PS:Ma c'e' qualche problema in Excel che non e' riuscito ancora a
risolvere???
Credo proprio di no.... ;-))
Se c'e', credo sia per i limiti di Excel stesso e non di certo per i
suoi... :-)))))
Pino
> Hai un elenco che presenta celle vuote, che vengono filtrate
> con Filtro automatico-Non Vuote, in quanto con questo
> comando selezioni SOLO le celle con valori (testuali o
> numerici).
>
> A siffatto elenco vuoi applicare un elenco a discesa? Allora
> Convalida-Elenco fa al caso tuo (e immagino tu "sappia" come
> funziona).
>
Forse anche io che *ho posto il quesito* sbagliero'(e' umano sbagliare!), ma
la soluzione da te proposta, credo funzioni solo quando dopo aver applicato
il filtro, copio le celle filtrate in un altro intervallo e poi imposto
l'origine della convalida a quest'ultimo intervallo.
Puo' darsi sia un problema di versione-aggiornamento di Excel, io uso la
97'.
Aspetta...mi sembra già di sentire la tua obiezione:"E se
> aggiungo dati al termine dell'elenco?" Ebbene, funziona
> anche in questo caso: basta modificare il riferimento finale
> dell'elenco in Convalida: se ad es. l'intervallo è A1:A10 e
> pensi di aggiungere in futuro dati fino ad A20, puoi sempre
> digitare questo intervallo in sede di convalida &A&1:&A&20.
Digitando la formula da te suggerita (preceduta dal segno uguale), non viene
accettata e viene segnalato un messaggio di errore.
Forse anche in questo caso puo' darsi sia un problema di versione-aggior. di
Excel , oppure una mia cattiva interpretazione.
SergioBS
il mouse l'intervallo dove ci sono i dati diciamo A1:A5
a questo punto devi andare su Dati->Filtro automatico e appare il menù a
discesa
ora vai sul menù a tendina e selezioni "celle non vuote"
poi ti posizioni nella casella B1 e vai su Dati -> Convalida
dalla casella "consenti" seleziona "elenco"
ti posizioni quindi nella casella "origine" e poi con il mouse selezioni le
celle A1:A5 e vedrai che nella casella origine appare: =$A$1:$A$5 (oppure
scrivicelo tu)
Dai OK e a questo punto il tutto dovrebbe funzionare:
io utilizzo XP, ma non mi sembra un'aggiunta recente..
Sergio
(anche se forse voleva risponderti Alfonso)
"Pino" <mai...@tin.it> ha scritto nel messaggio
news:bvedcr$r00rr$1...@ID-89213.news.uni-berlin.de...
SergioBS
proposto:
: A1=pippo
: A2=VUOTA
: A3=pluto
: A4=VUOTA
: A5=VUOTA
la funzione FILTRO AUTOMATICO seleziona tutte le celle della colonna fino a
quando trova una cella vuota, a meno che tu non specifichi al filtro
automatico le celle su cui filtrare.
per quanto riguarda poi la funzionalità, dipende dal numero di righe totali
e di vuote: se sono poche filtrare prima la selezione non è certamente
comodo, anche se meglio di niente...
per quanto riguarda il resto non mi va di polemizzare: ritengo giusto che
ognuno, se può, cerchi di dare una mano: appena posso anche io cerco di dare
il mio piccolo aiuto, se non altro per riconoscenza, visto che ho ricevuto
molti aiuti da molte persone di questo NG: cerco però per rispettare sia chi
scrive che chi legge, di dare delle risposte "adeguate" alla domanda, e non
la prima cosa che mi viene in mente: se reputo la mia soluzione non adeguata
preferisco non rispondere: forse questo non è lo spirito giusto per
avvicinarsi ad un NG..e se sbaglio mi scuso con tutti.
Sergio
"Alfonso Spagnuolo" <alfocentauriTOGL...@tiscali.it> ha scritto
nel messaggio news:OKN70615...@TK2MSFTNGP10.phx.gbl...
Pino
"SergioBS" <asda...@asdas.com> ha scritto nel messaggio
news:bvef9s$rvlbu$1...@ID-201124.news.uni-berlin.de...
> ... allora per sperimentare la soluzione di Alfonso tu devi selezionare
con
> il mouse l'intervallo dove ci sono i dati diciamo A1:A5
>
> a questo punto devi andare su Dati->Filtro automatico e appare il menù a
> discesa
> ora vai sul menù a tendina e selezioni "celle non vuote"
> poi ti posizioni nella casella B1 e vai su Dati -> Convalida
> dalla casella "consenti" seleziona "elenco"
> ti posizioni quindi nella casella "origine" e poi con il mouse selezioni
le
> celle A1:A5 e vedrai che nella casella origine appare: =$A$1:$A$5 (oppure
> scrivicelo tu)
> Dai OK e a questo punto il tutto dovrebbe funzionare:
>
> io utilizzo XP, ma non mi sembra un'aggiunta recente..
>
> Sergio
>
Ho fatto esattamente i suddetti passaggi, ma non funziona.
SergioBS
o meglio, quella di Barbara, sia quella che meglio si adatta alle tue
esigenze...
Sergio
"Pino" <mai...@tin.it> ha scritto nel messaggio
news:bveh6g$rljhi$1...@ID-89213.news.uni-berlin.de...
fernando cinquegrani
| Nella cella "B1" ho creato un elenco a discesa collegato all'intervallo
| "A1:A5".
| Se l'intervallo presenta delle celle vuote, l'elenco a discesa ad esso
| collegato visualizzera' anch'esso quest'ultime.
"pippo"
| e "pluto" *senza visualizzarmi anche le celle vuote*.
|
| L'elenco a discesa dovra' contenere un numero di elementi variabili in
base
| a quanti dati *non vuoti* sono presenti nell'intervallo "A1:A5".
mi scuso se non rispondo alle specifiche domande poste.
il quesito è molto interessante e il tempo che possiamo
dedicargli nel newsgroup è molto scarso.
abbiamo pensato con barbara di approfondirlo con un file
d'esempio del tipo di altri prodotti da RIO
(http://groups.google.it/groups?th=3b2198d8dad578a1
per chi non sa cos'è RIO). saremo lieti se vorrete
collaborare con noi. vi terremo informati.
.f
fernando cinquegrani
RIO, Risorse in Italiano per gli utenti di Office
Alfonso Spagnuolo
> Un'altra soluzione senza VBA:
>
> in B1:B5:
>
=INDICE(rng;PICCOLO(SE(RIF.RIGA(rng)=SE(rng<>"";CONFRONTA(rn
g;rng;));RIF.RIG
> A(rng));RIF.RIGA(rng)))
> origine della convalida:
> =SCARTO($B$1;;;CONTA.VALORI($B:$B)-CONTA.SE($B:$B;#NUM!))
>
> Ciao
> Barbara
*****************************
Ciao Barbara,
tanto per completare la mia conoscenza su questa tua
utilissima trovata, mi sembra di capire che la formula di
controllo da inserire in sede di convalida non puň essere
digitata subito.
In altre parole, occorre prima compattare l'elenco, portare
i valori errore alla fine dell'elenco stesso e poi inserire
la formula.
Difatti, ho provato con
=SCARTO($A$1;;;CONTA.VALORI($A:$A)-CONTA.SE($A:$A;"")) ma
non viene accettata.
E' cosě?
Ciao
Alfonso Spagnuolo
Alfonso Spagnuolo
confermo che la mia soluzione funziona, se vuoi ti mando un
file.
Comunque, concordo sul fatto che la soluzione di
Fernando-Barbara sia quella più completa.
La mia era solo una proposta, ecco perché mi sono un po'
alterato, mi sembrava che non fosse accettato quello che
avevo scritto solo perché non rispondeva pienamente alle tue
esigenze.
Se ho esagerato, mi scuso:-)
Naturalmente non voglio litigare con nessuno, ok?
Alla prossima
Alfonso Spagnuolo
mark...@universatitalia.it
trovo il post un po' datato, spero di trovarti ancora disponibile per un cortese chiarimento.
Dopo avere fatto tesoro delle tue preziose informazioni riguardo il VBA sopra (utilissoma l'eliminazione delle celle vuote), ho necessità di adattare lo script per la variante dell'elenco di dati da convalidare in un secondo foglio di lavoro. Mi spiego:
> Elenco di origine nel Foglio1, colonna "E" (contiene intestazione ed elenco superiore ai 1000 records, dalla cella A2)
> Convalida in Foglio2, colonna "B"
Con l'elenco di origine in "E" del Foglio1 che contiene celle vuote alternate da dati vorrei mantenere lo scrip che mi elimina le celle vuote (da te già previsto) portando la convalida nel nella colonna "B" del Foglio". Dove devo intervenire, puoi fornirmi un suggerimento?
Quesito accessorio (al quale non ho trovato ancora riposta in rete):
per la convalida su indicata, l'elenco a tendina consente la selezione dei records convalidati (ora ottimizzato eliminando le celle vuote) ma non consente di effettuare una ricerca, sia questa effettuata digitando semplicemente una lettera o un numero, oppure un filtro/un trova. Esiste un modo che consenta di effettuare una ricerca? Mi sarebbe utile considerato che in questo caso i records in questione sono codici di 12 cifre.
Ciao e grazie.
Giulio