Problemi con la funzione "Indiretto" & "Scarto" in convalida dati da elenco

Question

Salve,

purtroppo non solo le macro mi fanno sudare ma ora anche le funzioni.

Devo creare una "convalida dati basata" su quanto inserito in altra cella (sempre attraverso la convalida).

Come ho trovato in rete nella seconda convalida ho messo la funzione " indiretto (cella che contiene il nome dell'intervallo da caricare)" e ho creato con la funzione scarto gli intervalli dinamici.

Purtroppo la  convalida  non funziona  (non non scende la tendina!) se imposto il nome delle celle in modo dinamico (la formula penso sia  corretta perchè se scrivo il nome dell' intervallo nella casella in alto a sinistra vedo le celle selezionate correttamente) mentre se imposto il nome dell' intervallo in modo statico funziona.

Nello specifico ho messo nella seconda convalida (quella che non funziona):

=INDIRETTO("Elenco"&B$4)

B4 è la cella con la prima convalida che contiene la classe scolastica da caricare (ad es. TerzaA)

Per l'intervallo dinamico "ElencoTerzaA" la seguente formula (riferito a):

=SCARTO(ElencoStudenti!$A$2;0;0;CONTA.VALORI(ElencoStudenti!$A$2:$A$31);1)

Domanda: la funzione scarto ed indiretto sono utilizzabili con file excel salvato in versioni 2003?

Forse il problema è un'altro?

Avete suggerimenti?

grazie

MF

Answer 1

Ciao MecFala,

Si,  avevo detto già all' inizio che con il nome statico non avevo problemi ma è stato proprio per eliminare gli spazi vuoti nell' elenco a tendina della convalida che sono passato a "litigare" con la funzione scarto.

Purtroppo, credo che i tuoi tentativi ad usare una formula siano destinati a fallire, se non per altro, poichè gli intervalli B2:B31 sui fogli  delle classe (3A:3D e 4A:4C) non contengono delle celle vuote. Una cella che contiene una formula, anche una formula che potrebbe restituire una stringa vuota, ovvero "", non può mai essere vuota!

Pertanto ti consiglierei l'aiuto dell'amico VBA. Quindi prova qualcosa del genere:

• Fai clic dx sulla linguetta del foglio di interesse
• Seleziona l'opzione Visualizza Codice dal **** menu contestuale risultante
• Incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim srcSH As Worksheet

    Dim rngClasse As Range, rngStudente As Range

    Dim rngConvalidaDati As Range

    Dim arrClasse As Variant, arrFogli As Variant

    Dim arrStudenti As Variant, arrConvalida() As Variant

    Dim Res As Variant

    Dim i As Long, LRow As Long

    Const sCellaClasse As String = "B4"                                   '<<=== Modifica

    Const sCellaStudente As String = "B6"                               '<<=== Modifica

    Const sClasse As String = "TerzaA,TerzaB,TerzaC,TerzaD," _

          & "QuartaA,QuartaB,QuartaC"                                     '<<=== Modifica

    Const sFogli As String = "3A,3B,3C,3D,4A,4B,4C"                '<<=== Modifica

    With Me

        Set rngClasse = .Range(sCellaClasse)

        Set rngStudente = Me.Range(sCellaStudente)

    End With

    If Not Intersect(rngClasse, Target) Is Nothing Then

        On Error GoTo XIT

        Application.EnableEvents = False

        arrClasse = Split(sClasse, ",")

        arrFogli = Split(sFogli, ",")

        With rngClasse

            If Not .Value = vbNullString Then

                Res = Application.Match(.Value, arrClasse, 0)

                If Not IsError(Res) Then

                    Set srcSH = ThisWorkbook.Sheets(arrFogli(Res - 1))

                    With srcSH

                        LRow = .Cells(Rows.Count, "B").End(xlUp).Row

                        Set rngConvalidaDati = .Range("B2:B" & LRow)

                    End With

                    arrStudenti = rngConvalidaDati.Value

                    i = 1

                    Do Until arrStudenti(i, 1) = vbNullString

                        ReDim Preserve arrConvalida(1 To i)

                        arrConvalida(i) = arrStudenti(i, 1)

                        i = i + 1

                    Loop

                    With rngStudente

                        .ClearContents

                        .Activate

                        With .Validation

                            .Delete

                            .Add Type:=xlValidateList, Formula1:=Join(arrConvalida, ",")

                            .IgnoreBlank = True

                            .InCellDropdown = True

                            .InputTitle = ""

                            .ErrorTitle = ""

                            .InputMessage = ""

                            .ErrorMessage = ""

                            .ShowInput = True

                            .ShowError = True

                        End With

                    End With

                End If

            Else

                With rngStudente

                    .ClearContents

                    .Validation.Delete

                End With

            End If

        End With

    End If

XIT:

    Application.EnableEvents = True

End Sub

'<<=========

Potresti scaricare il mio file di prova (in formato Excel 97-2003) MecFala20170409.xls a:

https://www.dropbox.com/s/2xbkhcxyir2nuo6/MecFala20170409.xls?dl=0

===

Regards,

Norman

Answer 2

Il problema consiste nel fatto che, almeno dalle prove che ho fatto, la funzione INDIRETTO che richiama la cella B4, che a sua volta contiene l'elenco delle classi, non supporta la funzione SCARTO.

Pertanto se vuoi che il giochino funzioni, i nomi delle classi TerzaA, TerzaB ecc...  li devi riferire non a una formula SCARTO ma al loro effettivo intervallo.

Mi spiego meglio:

in B4, come convalida dati hai messo l'elenco delle classi nella forma TerzaA; TerzaB;........ e questo è OK.

Ora, al nome TerzaA devi riferire l'intervallo =3A!$A$2:$A$31 e non più SCARTO(...),

a TerzaB l'intervallo =3B!$A$2:$A$31 ecc....

quindi in B6, come convalida dati: =INDIRETTO($B$4)

NB: certo che così l'elenco risultante in B6 avrà anche degli spazi vuoti in coda, ma tant'è.....

Answer 3

Ringrazio chi per l'ennesima volta mi ha suggerito come risolvere.

Purtropo (mea culpa ........) non ho risolto!

Per farla breve allego una versione allegerita del file "incriminato":

https://www.dropbox.com/s/64ugc0rpz49f2sr/RIEPILOGO\_STAGE\_MOD.xls?dl=0

dove si vedono problemi con la convalida con la classe TerzaA

(Si,  ho anche scaricato lo zip di suggerito  da Norman ma non sono riuscito a "capitalizzare" l'esempio!)

saluti e grazie di nuovo

MF

Answer 4

Prova a modificare la funzione INDIRETTO così:

=INDIRETTO(B$4)

Answer 5

Ciao MecFala,

purtroppo non solo le macro mi fanno sudare ma ora anche le funzioni.

Devo creare una "convalida dati basata" su quanto inserito in altra cella (sempre attraverso la convalida).

Come ho trovato in rete nella seconda convalida ho messo la funzione " indiretto (cella che contiene il nome dell'intervallo da caricare)" e ho creato con la funzione scarto gli intervalli dinamici.

Purtroppo la  convalida  non funziona  (non non scende la tendina!) se imposto il nome delle celle in modo dinamico (la formula penso sia  corretta perchè se scrivo il nome dell' intervallo nella casella in alto a sinistra vedo le celle selezionate correttamente) mentre se imposto il nome dell' intervallo in modo statico funziona.

Nello specifico ho messo nella seconda convalida (quella che non funziona):

=INDIRETTO("Elenco"&B$4)

B4 è la cella con la prima convalida che contiene la classe scolastica da caricare (ad es. TerzaA)

Per l'intervallo dinamico "ElencoTerzaA" la seguente formula (riferito a):

=SCARTO(ElencoStudenti!$A$2;0;0;CONTA.VALORI(ElencoStudenti!$A$2:$A$31);1)

Domanda: la funzione scarto ed indiretto sono utilizzabili con file excel salvato in versioni 2003?

Certo! Credo che queste funzioni siano disponabili in ogni versione di Excel, almeno da Excel 97 in poi. 

Forse il problema è un'altro?

Avete suggerimenti?

Prova a scaricare e studiare il file e il tutorial di Debra Dalgleish (http://www.contextures.com/):

   **http://www.contextures.com/DataValDynamic.zip**

Se tu dovessi avere ancora dei problemi, siamo sempre qui!

===

Regards,

Norman