Convalida a due scelte e con elementi dinamici

[alby60]

Ciao,
ho la seguente necessità: gestire la convalida di due colonne, la seconda condizionata dalla scelta fatta con la prima.
Faccio l’esempio delle regioni per spiegare la mia esigenza ma i miei dati non riguardano le regioni.
Esempio nel Foglio1, in A2 ho una convalida che mi propone l’elenco delle regioni italiane, in B2 dovranno comparire le province della ragione scelta in A2.
Ho operato in questo modo: ho definito dei nomi Elenco: Regioni (foglio2 da A2 a A21), poi i nomi elenco delle regioni in B, C, D, …. partendo dalla riga 2 contengono le province delle regioni italiane.
In Foglio 1 in A2 ho la convalida elenco con in origine “=Regioni”, in B2 ho la convalida elenco con in origine “=Indiretto(A2)” tutto va bene se i nomi elenco li definisco in questo modo: ad esempio per il Lazio in “Riferito a” ho scritto =Foglio2!$A$2:$A$6.
Se voglio fare il nome elenco a lunghezza variabile in modo dinamico ossia se ho 5 elementi si deve dimensionare automaticamente A2:A6, se ho 10 si deve dimensionare automaticamente A2:A11
Ho sostituito la formula esistente per il nome del Lazio in

Codice: Seleziona tutto

=SCARTO(Foglio2!$A$2;0;0;CONTA.VALORI(Foglio2!$A$2:$A$100); 1)

Ho scritto “100” un valore molto grande per poi dimensionarsi al valore effettivo ma la convalida in B2 rimane vuota, prima del cambiamento mi dava i valori corretti.

Ho provato in Z2 a impostare la convalida “=Lazio” e in Z2 vedo le province del Lazio, quindi la formula con SCARTO è scritta bene

Potete aiutarmi a trovare una soluzione per avere i nomi elenchi a lunghezza dinamica.
Grazie anticipato

[Anthony47]

Purtroppo con Indiretto non puoi usare un range dinamico. Ma si puo' fare lo stesso.
Guarda l' immagine



L' area arancione (riga superiore) corrisponde alla prima convalida; ho chiamato quell' intervallo "Scelta1".
L' area in rosa conterra', in colonna, gli elenchi di ogni prima scelta; ho chiamato l'area arancione+rosa "Scelta2"

La prima convalida (da Elenco) sara' quindi impostata come =Scelta1

Per la seconda convalida (sempre da "Elenco")invece userai la formula

Codice: Seleziona tutto

=SCARTO(scelta2;1;CONFRONTA(A1;Scelta1;0)-1;-1+CONTA.VALORI(INDICE(scelta2;;CONFRONTA(A1;Scelta1;0)));1)


Funzionalmente corrisponde a Indiretto(A1)

Ciao

[ricky53]

Ciao,
dopo aver letto quanto proposto da Anthony (è una soluzione molto interessante) verifica anche quanto ti scrivo:

Prova con la funzionalità “Elenco”.

Opera in questo modo (la descrizione è valida per la versione di Office 2003):
dalla Barra dei menu, Dati, Elenco, Crea elenco
in “Seleziona la posizione dei dati per l’elenco” seleziona l’intervallo di celle con le regioni (da “A1:A21”)
metti la spunta a “L’elenco include intestazioni”
dai conferma

I vari nomi elenco li hai già
Regioni,
Nomi Regioni (....)
Le convalide pure … dovrebbe funzionare senza fare altro

Per prova: inserisci una nuova regione e la troverai nell'elenco delle regioni in “A2” … certo dovrai inserire un nuovo “Nome Elenco” da associare alla nuova regione con le provincie ad essa collegate.

Invece per le provincie non c’è problema: l’elenco si ridimensionerà automaticamente e non dovrai fare altro.

Prova e … sono QUI.

[alby60]

Ciao, ho provato la soluzione di Anthony e quella di Ricky, tutte e due mi hanno risolto il problema. Sono partito da un nuovo file ed ho inserito tutto dall'inizio, volevo essere sicuro di avere un file pulito. Grazie e Grazie siete grandi.

[snoopino]

Buon giorno,
come sempre ringrazio il forum e in particolare Anthony47 per l'aiuto :-)

Allora, di seguito il primo foglio di lavoro:



Quindi, data e ora in automatico grazie alla macro fornita da Anthony47, "fonte della chiamata" a tendina collegata a "motivo della chiamata" sempre a tendina. Ovviamente la prima scelta influenza la seconda scelta grazie al range dinamico.

Di seguito, invece, il secondo foglio di lavoro:



dove in giallo ho dato il nome "fonte1" mentre in verde ho dato il nome "fonte2"

Sotto, infine, le macro utilizzate sia per la data e ora automatica più le macro per ripulire la seconda scelta se cambia la prima:



Uploaded with ImageShack.us

Fin qui tutto perfetto, se non fosse che adesso che ho creato il mio simpatico file, non riesco a risolvere il problema delle celle vuote che mi compaiono nel menù a tendina della seconda scelta.

Potete aiutarmi? Esiste forse una macro che posso inserire per spiegare al foglio che deve liberarmi dalle celle vuote di tutta la colonna D?

Ringrazio in anticipo :-)

[Flash30005]

Ciao Snoopino e benvenuto nel Forum

Tutta la macro che hai in Worksheets_Change
può essere sostituita da questa

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
Area = "C3:C1000"
If Not Application.Intersect(Target, Range(Area)) Is Nothing Then
Application.EnableEvents = False
ActiveCell.Offset(-1, 0).Value = Date
ActiveCell.Offset(-1, -1).Value = Time
ActiveCell.Offset(-1, 1).ClearContents
Application.EnableEvents = True
End If
End Sub

Poi non ho capito quali righe vuote hai

ciao

[snoopino]

Mi scuso,
ho postato qui dietro invito di Anthony47.
In pratica nel menù a tendina della seconda scelta, oltre le opzioni, mi appaiono delle celle vuote molto antipatiche...
I menù a tendina della seconda scelta sono nella colonna D e speravo in una macro per far sparire le celle vuote...
Per il resto il mio file funziona alla grande...
Grazie mille per aver risposto...
Attendo suggerimenti :-)

[ricky53]

Ciao,
hai provato ad ordinare i dati in modo crescente ad esempio?

[snoopino]

Scusa, come faccio a ordinare in modo crescente le voci del menù a tendina della seconda scelta se sono delle parole?!? -.-"
Grazie comunque per aver risposto :-)

[ricky53]

Ciao,
ordinare i dati in colonna "..." che alimentano la convalida.

Ossia la convalida come l'hai predisposta?
con un intervallo di celle o con un nome elenco che comunque fa riferimento ad un intervallo di celle.

[snoopino]

ho seguito le istruzioni di Anthony47 per avere le due tendine collegate da un range dinamico...
il fatto è che funziona tutto... volevo solo provare ad eliminare le celle vuote che si presentano nella seconda scelta...

[Anthony47]

Ma hai adottato il sistema suggerito ad Alby in questa discussione, o sei ancora rimasto a quanto suggerito "nell' altra discussione" (viewtopic.php?f=26&t=48026&p=561626#p561589)?

Ciao

[snoopino]

io ho fatto questo:

Ciao Alessio,
insomma vuoi mettere su delle celle una Convalida da lista, con la seconda lista dipendente dalla prima.
Se le liste sono statiche, come con regioni e province, lo facciamo tutto da excel.
Sul Foglio2 del tuo file ti fai un elenco in verticale con l' elenco delle Regioni, e questo elenco lo chiami ELE1; supponiamo che sia in colonna A, da A1 ad A20 (giacche’ le regioni sono 20).
Accanto, assegniamo al range C1:V1 (quindi 20 celle) il nome ELEH.
Mettiamo in C1 la formula
CODICE: SELEZIONA TUTTO
=SCARTO(ELE1;RIF.COLONNA()-MIN(RIF.COLONNA(ELEH));0)
che devi confermare NON con Enter MA con Contr+Shift+Enter, e che poi copiamo fino a V1; a questo punto dovremmo avere da C1 a V1 lo stesso elenco di regioni che c’ e’ da A1 ad A20.
Ora sotto ogni Regione elencata in orizzontale, mettiamoci le sue Province; magari per vedere l’ effetto compila solo un paio di elenchi di province, il resto lo metterai se ti soddisfa il risultato.
Andiamo sul Foglio1, nella cella dove vogliamo inserire solo il nome di una Regione; fai Menu ->Dati ->Convalida e ti si apre la finestra di Convalida dati, tab Impostazioni; scegli Consenti Elenco, e nel campo Origine scrivi =ELE1; eventualmente compila Messaggio di input (es “Scegli da elenco”) e Messaggio di errore (es. “Solo da elenco”). Inoltre a questa cella dai il nome CONV1.
A questo punto creiamo un range dinamico: Menu ->Inserisci ->Nome ->Definisci; si apre la finestra di Definisci nome; nel campo in alto scrivi DINA1 mentre nel campo in basso (Riferito a:) scrivi
CODICE: SELEZIONA TUTTO
=SCARTO(ELEH,1,CONFRONTA(CONV1,ELEH,0)-1,15,1)

Infine andiamo sulla cella in cui vogliamo inserire la provincia, e ripetiamo il ciclo di Convalida dati, utilizzando la procedura di prima, ma indicando nel campo “Origine” =DINA1.

Voila.
Se vai sulla cella “regione”, ti compare la freccina e la scritta “Scegli da elenco”; se clicchi sulla freccina ti compare l’ elenco che abbiamo messo su Foglio2. Vai sulla cella “provincia” e se clicchi sulla freccina dovresti vedere le province di quella regione (come compilate su Foglio2).

Ti aiuta?

mi funziona tutto... e alla grande... volevo solo sapere se a questo metodo posso integrare qualcosa per le famose celle vuote come nell'immagine che aggiungo sotto:



Uploaded with ImageShack.us

[Anthony47]

Quanto suggerito ad Alby risolve il problema delle celle vuote, quindi ti suggerisco di usare la soluzione proposta qui: viewtopic.php?f=26&t=97657#p560784
Devi dichiarare un intervallo "scelta1" (corrisponde a C1:I1, area verde) e un altro "scelta2" (ad esempio C20:I20); poi la prima convalida sara' fatta con "=scelta1" e la seconda con la formula

Codice: Seleziona tutto

=SCARTO(scelta2;1;CONFRONTA(A1;Scelta1;0)-1;-1+CONTA.VALORI(INDICE(scelta2;;CONFRONTA(A1;Scelta1;0)));1)

A1 in questa formula corrisponde alla cella con la prima convalida; adatta secondo la tua esigenza.

Ciao