corretta sintassi cerca vet su tabella esterna

Question

Salve

vorrei sapere la corretta sintassi per la formula cerca vert dato che non riesco ad applicarla su una tabella contenuta in un altra cartella di lavoro excel.

Se inserisco i riferimenti di cella della matrice funziona, ma se aggiungo altre celle al database sono costretto a cambiare sempre la formula per aggiornare il file e lavorando con una tabella che aumenta costantemente di giorno in giorno questa operazione diventa un po scomoda. Non riesco tuttavia a scrivere corrttamente la formula.

Ho povato piu volte ma funziona solo se la tabella si trova nello stesso foglio in cui si trova la formula cerca vert, mentre invece non funziona se la tabella e' in un altra cartella di lavoro.

Grazie mille

Banjer71

Answer 1

Ciao Banjer71

Grazie mille Norman,  davvero!

Grazie a te,  Banjer, per il cortese riscontro.

Se il mio codice risolva il problema, per chiudere questo thread, vorrei chiederti gentilmente di segnare la mia risposta come Risposta. In questo modo, tu aiuterai anche coloro che potessero cercare soluzioni ai problemi simili negli archivi della Community.

Solo oggi a Londra? 

No, Vado a Londra molto spesso perchè abito a soli 40 km.

Buona domenica.

===

Regards,

Norman

Answer 2

Ciao Banjer71,

vorrei sapere la corretta sintassi per la formula cerca vert dato che non riesco ad applicarla su una tabella contenuta in un altra cartella di lavoro excel.

Se inserisco i riferimenti di cella della matrice funziona, ma se aggiungo altre celle al database sono costretto a cambiare sempre la formula per aggiornare il file e lavorando con una tabella che aumenta costantemente di giorno in giorno questa operazione diventa un po scomoda. Non riesco tuttavia a scrivere corrttamente la formula.

Ho povato piu volte ma funziona solo se la tabella si trova nello stesso foglio in cui si trova la formula cerca vert, mentre invece non funziona se la tabella e' in un altra cartella di lavoro.

Un approccio ad una tabella di ricerca dinamica in una cartella di lavoro chiusa potrebbe essere di rivolgersi a VBA.

Per dimostrare questo approccio, prova qualcosa del genere:

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

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

Option Explicit

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

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range, Rng2 As Range

    Dim Nme As Name

    Const sColonneTabella = "A:C"                                                '<<=== Modifica

    Const sPrimaCellaTabella As String = "A1"                              '<<=== Modifica

    Const sNomeTabella As String = "MiaTabella"                       '<<=== Modifica

    With Me

        Set Rng = Intersect(.Range(sPrimaCellaTabella).CurrentRegion, _

                            .Columns(sColonneTabella))

    End With

    If Rng Is Nothing Then

        Exit Sub

    Else

        ThisWorkbook.Names.Add Name:=sNomeTabella, RefersTo:=Rng

    End If

End Sub

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

• Alt+Q per chiudere l'editor di VBA e tornare a Excel.
• Salva il file con l'estensione xlsm.

Questo codice automaticamente crea o aggiorna l'intervallo del nome definito MiaTabella assegnato alla tabella.

Per le mie prove ho creato un file MyDataBase.xlsm con una tabella di ricerca nell'intervallo A1:C50 del primo foglio.  Ho poi creato un file di prova, TestLookup.xlsx in cui ho inserito delle semplice formule del tipo:

=CERCA.VERT(A2;'C:\Users\NDJ\Dropbox\MyDatabase.xlsm'!MiaTabella;2;0)

Se le formule fossero immesse quando il primo file è aperto, non sarebbe necessario includere anche il percorso del primo file in quanto verrà automaticamente aggiunta alla chiusura del file.

Queste formule restituiranno i risultati voluti, independentmente di eventuali modifiche alla tabella di ricerca dinamica, e independentente del fatto che quel file sia chiuso o aperto. 

Potresti scaricare il file zippato **MyDatabase.zip.**che comprende le mie due file, MyDatabase.xlsm e TestLookup.xlsx,  a:

https://www.dropbox.com/s/s3hu9bh3r1had61/MyDatabase.zip?dl=0

Anche se non cambia il principio di questo suggerimento, ti consiglierei anche di prendere in considerazione l'uso delle due funzioni INDICE e  CONFRONTO, anzichè CERCA.VERT, per la tua esigenza in quanto sono più flessibile e non sono volatile. 

===

Regards,

Norman

Answer 3

Grazie mille Norman,  davvero!

Provero' tutto quello che hai detto, sei stato davvero prezioso!  Solo oggi a Londra? Se ripassi da Londra ti devo almeno un Caffè :)

A presto

Banjer

Answer 4

Ciao Banjer71,

ti ringrazio tantissimo, usando i tuoi esempi funziona tutto, ora non mi resta che provare su i miei file. Non conosco nulla di VBA pero', quindi se ho capito bene, posso copiare ed incollare quello che hai scritto cambiando solo le parti in neretto con i miei riferimenti e nomi di tabella.

Sì, proprio così. Tuttavia, per quanto riguarda il nome della tabella, se non ci sia un nome esistente, il nome verrà creato dal codice; in tal caso potresti anche lasciare il nome invariato.

Cio' che mi sconsola un po' e scoprire che quello che volevo si puo' fare solo usando il vba?

Non ci sarebbe alcun problema se la tabella non fosse dinamica. Tuttavia, se fosse possibile definire l'intervallo della tabella in modo tale da consentire un successivo aumento delle sue dimensioni, sarebbe possibile utilizzare una semplice formula con equanimità. Comunque,  aspetta e vedi altri eventuali suggerimenti in materia. C'è molta gente di talento in questa Community!

Ho letto della funzione indice e confronto anziche' cerca vert ma ho da poco rimesso mani ad excel e lo sto cercando di studiare man mano che mi si presentano i bisogni lavorativi. (le mie nozioni di base risalgono ad excel 97 e 2000 ed sono 15 anni che non li uso)

Le studiero' subito!!

Benissimo! 

Per fortuna mi hai scritto anche la formula con la sintassi inglese, mi trovo infatti a Londra e ho scoperto che il modo di scrivere le formule e' diverso non tanto nel nome ma anche nelle virgole anziche' i punti e virgola che si usano in Italia. (questa e' una cosa che non mi spiego vabbe' )

Per caso sono a Londra anch'io oggi!

Un po' di domande ancora, stupide ma per me fondamentali, se il database dovesse restringersi invece che ingrandirsi, il codice da te scritto funzionerebbe lo stesso?

Certo - provalo!

Se il database non fosse un file di excel ma una tabella di Access cambierebbe qualcosa?

(in pratica al momento lavoro con un file esportato, quasi ogni giorno, da access in excel , la mia speranza e invece lavorare direttamente sul database di access)

Molto probabilmente, ma dipende ciò che desideri fare esattamente! 

Il foglio in cui cerco i file mi serve  per richiamare dati dalla tabella database, ed una volta richiamati i dati , dovrei cancellarli dal database in quanto sono articoli che devono essere venduti e quindi rimossi.

faccio un esempio: mi vengono richiesti 100 hard disk, io li prendo inserisco la matricola che si aggancia al database e tramite la funzione cerca vert mi appaiono tutti i dati di quell hd. Una volta apparsi mi servira' cancellare i 100 hd dal database.

Avevo pensato ad una macro ma immagino che dovro' ricorrere al vba (che non so ) o esiste un modo per far questo senza il vba?

si potrebbe creare un tasto alla cui pressione vengono cancellati dal file database i dati richiamati?

vorrei avere solo delle risposte che mi guidino  ad indirizzare le mie ricerche ed il mio studio.

Credo questa sia oltre l'ambito della domanda iniziale e, quindi, ti chiederei gentilmente di aprire un nuovo thread.  In questo modo, tu aiuterai anche coloro che potessero cercare una soluzione a problemi simili negli archivi della Community; allo stesso tempo, questo ti offrirà una maggiore possibilità di ottenere una soluzione utile.

===

Regards,

Norman

Answer 5

Ciao Norman

ti ringrazio tantissimo, usando i tuoi esempi funziona tutto, ora non mi resta che provare su i miei file. Non conosco nulla di VBA pero', quindi se ho capito bene, posso copiare ed incollare quello che hai scritto cambiando solo le parti in neretto con i miei riferimenti e nomi di tabella.

Cio' che mi sconsola un po' e scoprire che quello che volevo si puo' fare solo usando il vba?

Ho letto della funzione indice e confronto anziche' cerca vert ma ho da poco rimesso mani ad excel e lo sto cercando di studiare man mano che mi si presentano i bisogni lavorativi. (le mie nozioni di base risalgono ad excel 97 e 2000 ed sono 15 anni che non li uso)

Le studiero' subito!!

Per fortuna mi hai scritto anche la formula con la sintassi inglese, mi trovo infatti a Londra e ho scoperto che il modo di scrivere le formule e' diverso non tanto nel nome ma anche nelle virgole anziche' i punti e virgola che si usano in Italia. (questa e' una cosa che non mi spiego vabbe' )

Un po' di domande ancora, stupide ma per me fondamentali, se il database dovesse restringersi invece che ingrandirsi, il codice da te scritto funzionerebbe lo stesso?

Se il database non fosse un file di excel ma una tabella di Access cambierebbe qualcosa?

(in pratica al momento lavoro con un file esportato, quasi ogni giorno, da access in excel , la mia speranza e invece lavorare direttamente sul database di access)

Il foglio in cui cerco i file mi serve  per richiamare dati dalla tabella database, ed una volta richiamati i dati , dovrei cancellarli dal database in quanto sono articoli che devono essere venduti e quindi rimossi.

faccio un esempio: mi vengono richiesti 100 hard disk, io li prendo inserisco la matricola che si aggancia al database e tramite la funzione cerca vert mi appaiono tutti i dati di quell hd. Una volta apparsi mi servira' cancellare i 100 hd dal database.

Avevo pensato ad una macro ma immagino che dovro' ricorrere al vba (che non so ) o esiste un modo per far questo senza il vba?

si potrebbe creare un tasto alla cui pressione vengono cancellati dal file database i dati richiamati?

vorrei avere solo delle risposte che mi guidino  ad indirizzare le mie ricerche ed il mio studio.

Grazie ancora per il prezioso aiuto ti faro' sapere al piu presto.

Banjer