Excel Convalida di Convalida Ordinata e Doppi

[boman]

Salve a tutti. Rompo subito le scatole cercando una soluzioni da voi che siete i guru di excel visto che sono sfinito da 4 giorni senza risultati. ho letto un po' qua e un po' la ma non son riuscito a trovare una soluzione facile alla mia portata (molto bassa). Veniamo al dunque in una casella sotto la colonna "committente" del foglio chiamato SCHEDA_ORARIA devo fare una convalida di un elenco dinamico posto nel foglio ELENCO_PRATICHE sempre sotto la colonna "committente". Quello che vorrei fare:
1) ..son riuscito con una formuletta a non farmi visualizzare le caselle vuote dal menu a discesa.. ma come posso fare a ad ordinarlo alfabeticamnte? e come posso evitare i doppi?
2) nella casella a fianco (foglio SCHEDA ORARIA), nella colonna "lavoro", vorrei che la convalida di un elenco sempre sotto ELENCO PRATICHE mi scremasse i lavori sulla base della scelta del "committente" e stessi requisiti di sopra cioè ordinato alfabeticamnte e eliminati i doppi.
3) nella casella a fianco (foglio SCHEDA ORARIA), nella colonna "sub-lavoro", vorrei che la convalida di un elenco sempre sotto ELENCO PRATICHE mi scremasse i sub-lavori sulla base della scelta del "lavoro" e stessi requisiti di sopra cioè ordinato alfabeticamnte e eliminati i doppi.

foglio ELENCO PRATICHE:


foglio SCHEDE ORARIE:




file excel:
http://rapidshare.com/files/438051636/G ... ginale.xls



Concludo col dire che non so usare macro (c'ho proavato più volte ma sembro un imbecille) e vba.
spero possiate aiutarmi!!

[ricky53]

Ciao,
prima di tutto: benvenuto nel forum!

QUI c'è una discussione dove viene trattata una problematica simile alla tua esigenza, provala.

Vengono utilizzate macro e Flash ha spiegato molto bene come operare.

Volendo puoi provare le formule suggerite da Anthony ma, a mio avviso, è un po' più complesso utilizzarle.

Ciao

[Anthony47]

Ciao boman e benvenuto anche da parte mia.
In aggiunta al lnk che ti ha inviato Ricky ti segnalo questa altra discussione assolutamente uguale alla tua: viewtopic.php?p=484004#p484099
La differenza e' nell' elenco di partenza (A:C in quella discussione, D:F nel tuo caso) e nello spazio libero richiesto per 3 colonne di appoggio (H:J, devono diventare almeno L:N nel tuo caso); oltre che alle celle in cui va applicata la convalida, che nella macro proposta si imposta in modo esplicito.

Prova ad adattare e fai sapere dove arrivi, noi siamo qui.

[boman]

Ciao e grazie a tutti intanto per l'interessamento.
Anthony, come ho scritto nel primo post mi riconfermo impedito con le macro!
Ho fatto una prova con l'esempio del post che mi hai riportato che avevo già provato anche alcuni giorni fa e tutto fila perfettamente. Ora ho cercato di adattare quella macro al mio file (vedi sotto) ma quando vado a fare la convalida nella cella E6 con origine =Conv1 mi dice "impossibile trovare un intervallo denominato specificato". Dove Sbaglio?

Codice: Seleziona tutto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CheckArea = "E6:G6"    '<<< Celle con Convalide
FoglioList = "ELENCO_PRATICHE"
If Application.Intersect(Target, Range(CheckArea)) Is Nothing Then Exit Sub
CWS = ActiveSheet.Name
'Sheets(FoglioList).Select
Application.ScreenUpdating = False
Application.EnableEvents = False
CFcol = Target.Column - Range(CheckArea).Column + 1
Sheets(FoglioList).Cells(1, 7 + CFcol).Range("D2:F" & Rows.Count).Clear
aaa = Sheets(FoglioList).Range(Sheets(FoglioList).Cells(2, CFcol), Sheets(FoglioList).Cells(Rows.Count, CFcol).End(xlUp)).Address
For Each cella In Sheets(FoglioList).Range(Sheets(FoglioList).Cells(2, CFcol), Sheets(FoglioList).Cells(Rows.Count, CFcol).End(xlUp))
XstraCk = 1
If CFcol > 1 Then If Range(CheckArea).Range("D2").Value <> Sheets(FoglioList).Cells(cella.Row, 1).Value Then XstraCk = 0
If CFcol > 2 Then If Range(CheckArea).Range("E2").Value <> Sheets(FoglioList).Cells(cella.Row, 2).Value Then XstraCk = 0
If XstraCk = 1 And Application.WorksheetFunction.CountIf(Sheets(FoglioList).Range("L1:L10000").Offset(0, CFcol - 1), cella.Value) = 0 Then
Sheets(FoglioList).Cells(Rows.Count, 7 + CFcol).End(xlUp).Offset(1, 0).Value = cella.Value
End If
Next cella
Sheets(FoglioList).Range("L2", Sheets(FoglioList).Range("L" & Rows.Count).End(xlUp)).Name = "Conv1"
Sheets(FoglioList).Range("M2", Sheets(FoglioList).Range("M" & Rows.Count).End(xlUp)).Name = "Conv2"
Sheets(FoglioList).Range("N2", Sheets(FoglioList).Range("N" & Rows.Count).End(xlUp)).Name = "Conv3"
'Sheets(CWS).Select
Target.Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[ricky53]

Ciao,
tu stavi facendo la convalida manualmente e volevi inserire il nome elenco "Conv1" nella cella "E6"?

Il messaggio di errore significa che il nome elenco "Conv1" non è stato definito.

Tu avevi eseguito la macro prima di fare la convalida ?

Controlla perchè la macro dovrebbee definire il nome ...

[boman]

Allora, ho rifatto tutto di nuovo sempre seguendo la procedura descritta da Anthony nel post viewtopic.php?p=484004#p484099 ed ho cambiato soltanto i valori nella macro. Ora non mi da più errore nella convalida manuale ma dalle tendina non scende nulla!

tu stavi facendo la convalida manualmente e volevi inserire il nome elenco "Conv1" nella cella "E6"?

Si esatto stavo facendo la convalida manualmente

Tu avevi eseguito la macro prima di fare la convalida ?

Si ho seguito le istruzioni del post sopra 1) ho copiato il codice della macro prima della convalida modificandolo delle parti che "credevo opportune" cioè dal codice originale ho modificato le lettere delle colonne che ritenevo opportune.. ma secondo me è proprio qui che non ci ho azzeccato.. ho scritto male qualcosa! 2) ho fatto le convalide manuali nelle tre celle affiancate con =Conv1 =Conv2 =Conv3 3) son tornato sulla macro e ho modificato l'istruzione CheckArea (quella marcata <<<) inserendo le vere celle in cui ho inserito le Convalide e cioè E6:G6

Controlla perchè la macro dovrebbee definire il nome ...

...non ho capito?!

[Anthony47]

Credo che ci siano piccoli errori nelle seguenti righe; la correzione e' in colore rosso:

Sheets(FoglioList).Cells(1, 11 + CFcol).Range("A2:C" & Rows.Count).Clear

For Each cella In Sheets(FoglioList).Range(Sheets(FoglioList).Cells(2, CFcol+3), Sheets(FoglioList).Cells(Rows.Count, CFcol+3).End(xlUp))

If CFcol > 1 Then If Range(CheckArea).Range("A1").Value <> Sheets(FoglioList).Cells(cella.Row, 1+3).Value Then XstraCk = 0
If CFcol > 2 Then If Range(CheckArea).Range("B1").Value <> Sheets(FoglioList).Cells(cella.Row, 2+3).Value Then XstraCk = 0

Sheets(FoglioList).Cells(Rows.Count, 11 + CFcol).End(xlUp).Offset(1, 0).Value = cella.Value

Ciao

[ricky53]

Ciao boman,
ti rispondo su

Controlla perchè la macro dovrebbee definire il nome ...
...non ho capito?!

Codice: Seleziona tutto

Sheets(FoglioList).Range("L2", Sheets(FoglioList).Range("L" & Rows.Count).End(xlUp)).Name = "Conv1"


questa linea di codice definisce il nome elenco "Conv1"

[boman]

Scusate il ritardo nella risposta ma oltre ai problemi con excel ho problemi anche con l'adsl!
Ritornado ad excel ho fatto varie prove ma non riesco a farlo andare. A volte mi da errore run-time 1004, a volte dalla tendina mi fa vedere tutti gli elementi ma anche i doppi e nella tendina della cella a fianco non mi screma in base alla scelta della cella precedente, a volte invece non mi fa vedere proprio nulla! Ho apportato anche le modifiche di Anthony però non capisco perchè mi ha segnato range che partono da A e da B... non capisco cosa debba trovare in quelle celle/colonne.
Ho anche un'altro dubbio.. ammettiamo che funzioni (sicuramente).. ma funzionerà anche con tutte le righe successive? perchè sia il foglio ELENCO_PRATICHE sia e sopratutto il foglio SCHEDA_ORARIA sono in costante aggiornamento e per quest'ultimo avrei bisogno delle convalide (cioè delle tendine) per almeno 2000 righe.

Se qualche buon-anima avesse voglia di dare uno sguardo al file....

http://rapidshare.com/files/438585436/GESTIONE_LAVORI_4prova.xls

...ve ne sarei veramente grato.

[Anthony47]

Nel file che hai mandato c' e' una riga XstraCk = 0; l' istruzione giusta e'

Codice: Seleziona tutto

XstraCk = 1

, come avevi scritto qualche post fa.

Ciao

[boman]

Grazie Anthony... Bene!! ora funziona!!... solo nella prima riga però!! infatti già dalla seconda riga la prima cella di convalida (colonna committente) mi presenta l'elenco giusto poi, scelto il committente, nella seconda cella di convalida(colonna lavoro) mi dovrei trovare elenco abbinato al committente scelto.. invece mi ritrovo l'elenco del riga superiore. Per spiegarmi meglio vi allego quest'immagine

(per la scelta fatta "topolino" nella convalida colonna lavoro mi dovrei trovare "auto")
la macro che ho è questa:

Codice: Seleziona tutto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CheckArea = "E6:G2000"    '<<< Celle con Convalide
FoglioList = "ELENCO_PRATICHE"
If Application.Intersect(Target, Range(CheckArea)) Is Nothing Then Exit Sub
CWS = ActiveSheet.Name
'Sheets(FoglioList).Select
Application.ScreenUpdating = False
Application.EnableEvents = False
CFcol = Target.Column - Range(CheckArea).Column + 1
Sheets(FoglioList).Cells(1, 11 + CFcol).Range("A2:C" & Rows.Count).Clear
aaa = Sheets(FoglioList).Range(Sheets(FoglioList).Cells(2, CFcol), Sheets(FoglioList).Cells(Rows.Count, CFcol).End(xlUp)).Address
For Each cella In Sheets(FoglioList).Range(Sheets(FoglioList).Cells(2, CFcol + 3), Sheets(FoglioList).Cells(Rows.Count, CFcol + 3).End(xlUp))
XstraCk = 1
If CFcol > 1 Then If Range(CheckArea).Range("A1").Value <> Sheets(FoglioList).Cells(cella.Row, 1 + 3).Value Then XstraCk = 0
If CFcol > 2 Then If Range(CheckArea).Range("B1").Value <> Sheets(FoglioList).Cells(cella.Row, 2 + 3).Value Then XstraCk = 0
If XstraCk = 1 And Application.WorksheetFunction.CountIf(Sheets(FoglioList).Range("L1:L10000").Offset(0, CFcol - 1), cella.Value) = 0 Then
Sheets(FoglioList).Cells(Rows.Count, 11 + CFcol).End(xlUp).Offset(1, 0).Value = cella.Value
End If
Next cella
Sheets(FoglioList).Range("L2", Sheets(FoglioList).Range("L" & Rows.Count).End(xlUp)).Name = "Conv1"
Sheets(FoglioList).Range("M2", Sheets(FoglioList).Range("M" & Rows.Count).End(xlUp)).Name = "Conv2"
Sheets(FoglioList).Range("N2", Sheets(FoglioList).Range("N" & Rows.Count).End(xlUp)).Name = "Conv3"
'Sheets(CWS).Select
Target.Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[Anthony47]

Veramente e' la prima volta che dici che il lavoro lo vuoi fare su tutte le righe del foglio, e i file finora allegati avevano la convalida solo sulla prima riga...
Prova a modificare l' inizio della macro, prima controllando se Target ricade all' interno del range su cui vuoi che la macro faccia il suo lavoro, e successivamente modificando CheckArea = "E6:G6" '<<< Celle con Convalide in

Codice: Seleziona tutto

CheckArea = "E" & Target.row & ":G" & Target.row    '<<< Celle con Convalide


Ciao

[boman]

Anthony che dire... grazie!!
Ma grazie anche a ricky.
Avete aiutato un povero pecorone smarrito!
Funziona tutto. da oggi pomeriggio sto testando e sto apportando alcune modifiche per ora fila tutto.
Grazie grazie ancora!

[boman]

Ho parlato troppo presto comunque i ringraziamenti sono sempre validi.
ho provato a cliccare sul numero di una riga a caso (nell'immagine la 11) e mi restituisce questo errore:



cliccando su debug mi apre la finestra con il codice e mi evidenzia questa stringa:

Codice: Seleziona tutto

aaa = Sheets(FoglioList).Range(Sheets(FoglioList).Cells(2, CFcol), Sheets(FoglioList).Cells(Rows.Count, CFcol).End(xlUp)).Address

non so se voglia dire che c'è un errore li.

[Anthony47]

Quella riga era solo di debug, la puoi cancellare.
Pero' credo che avrai errore nella riga successiva.

Stai selezionando contemporaneamente piu' celle, per gestire metti subito all' inizio questa:

Codice: Seleziona tutto

If Selection.Count > 1 Then Exit Sub


Ciao

[boman]

No no sto selezionando più celle in una volta.
ho inserito la stringa che mi hai dato all'inizio del codice ma ora cliccando su qualsiasi cella mi restituisce un'altro errore "errore di compilazione: non valido all'interno di una ruotine"

[Anthony47]

Nell' immagine postata si vede, e lo scrivi, che selezionavi tutta la riga...

Mi posti le prime 5 righe della macro? Che versione di excel hai?

Ciao

[boman]

il file è nato con excel 2002 che ho in ufficio e ora ci so lavorando a casa con excel 2007.
Scusa non pensavo che selezionare la riga potesse equivalere a "sezionare più celle".
Ti posto tutto il codice

Codice: Seleziona tutto

If Selection.Count > 1 Then Exit Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CheckArea = "E" & Target.Row & ":G" & Target.Row    '<<< Celle con Convalide
FoglioList = "ELENCO_PRATICHE"
If Application.Intersect(Target, Range(CheckArea)) Is Nothing Then Exit Sub
CWS = ActiveSheet.Name
'Sheets(FoglioList).Select
Application.ScreenUpdating = False
Application.EnableEvents = False
CFcol = Target.Column - Range(CheckArea).Column + 1
Sheets(FoglioList).Cells(1, 11 + CFcol).Range("A2:C" & Rows.Count).Clear
aaa = Sheets(FoglioList).Range(Sheets(FoglioList).Cells(2, CFcol), Sheets(FoglioList).Cells(Rows.Count, CFcol).End(xlUp)).Address
For Each cella In Sheets(FoglioList).Range(Sheets(FoglioList).Cells(2, CFcol + 3), Sheets(FoglioList).Cells(Rows.Count, CFcol + 3).End(xlUp))
XstraCk = 1
If CFcol > 1 Then If Range(CheckArea).Range("A1").Value <> Sheets(FoglioList).Cells(cella.Row, 1 + 3).Value Then XstraCk = 0
If CFcol > 2 Then If Range(CheckArea).Range("B1").Value <> Sheets(FoglioList).Cells(cella.Row, 2 + 3).Value Then XstraCk = 0
If XstraCk = 1 And Application.WorksheetFunction.CountIf(Sheets(FoglioList).Range("L1:L10000").Offset(0, CFcol - 1), cella.Value) = 0 Then
Sheets(FoglioList).Cells(Rows.Count, 11 + CFcol).End(xlUp).Offset(1, 0).Value = cella.Value
End If
Next cella
Sheets(FoglioList).Range("L2", Sheets(FoglioList).Range("L" & Rows.Count).End(xlUp)).Name = "Conv1"
Sheets(FoglioList).Range("M2", Sheets(FoglioList).Range("M" & Rows.Count).End(xlUp)).Name = "Conv2"
Sheets(FoglioList).Range("N2", Sheets(FoglioList).Range("N" & Rows.Count).End(xlUp)).Name = "Conv3"
'Sheets(CWS).Select
Target.Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[Anthony47]

If Selection.Count > 1 Then Exit Sub la devi mettere subito DOPO Private Sub Worksheet_SelectionChange(ByVal Target As Range); quindi in seconda riga.

Ciao

[boman]

anthony che dire.... grazie.