Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

CERCA.VERT con riferimenti esterni dinamici

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

CERCA.VERT con riferimenti esterni dinamici

Postdi sceriffopeschiera » 27/12/13 02:05

Buongiorno a tutti,
ho un problema con la funzione CERCA.VERT che proprio non riesco a risolvere. Apparentemente, sembrerebbe un problema semplice, ma non riesco a venirne a capo.
Il problema è questo, il CERCA.VERT dovrebbe “pescare” dati da diversi file Excel, ovvero da una fonte esterna "variabile" .
Più nel dettaglio, ho diversi file strutturati esattamente nella stessa maniera, uno per ogni mese (2013_08, 2013_09, 2013_10, 2013_11, etc…). Il file nel quale andrò ad inserire il CERCA.VERT conterrà anche una cella che indicherà in quale dei diversi file mensili, il CERCA.VERT dovrà pescare i dati.
Il valore da cercare (primo argomento del CERCA.VERT) e la colonna Indice (terzo argomento) non variano.
Il problema sta nel secondo argomento della funzione (argomento Matrice_Tabella). Non riesco a creare un riferimento dinamico che venga identificato come tale dalla funzione CERCA.VERT.
Supponendo quanto segue:
1. i file riferiti a ciascun periodo (2013_08, 2013_09, 2013_10, 2013_11, etc…) si trovano sul desktop in una cartella che possiamo chiamare “PIPPO” per comodità
2. il valore da cercare è in cella A1
3. l’indice (terzo argomento della funzione) è fisso, come dicevo, e supponiamo sia 3.
Nella cella B1 ho il riferimento del file excel dal quale va estratto il dato (2013_08, 2013_09, 2013_10, 2013_11, etc…)

Come dovrebbe essere la formula? CERCA.VERT($A$1;????????;3;falso)

Spero di aver spiegato in maniera esaustiva il problema. Ringrazio anticipatamente per il suppporto
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Sponsor
 

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi peppo55 » 27/12/13 16:33

Ciao sceriffopeschiera

credo che non si possa fare.
In B1 hai il percorso del file. Esempio: 'C:\pippo\AAA.xlsx'!AAA
quindi nella matrice_tabella sarà : CERCA.VERT(A1;B1;3;falso)
però B1 non viene interpretato come percorso del file.
In una analoga situazione, avevo risolto con un' unica formula con i SE( )
Naturalmente ha dei limiti per quanto riguarda il numero di files da gestire
e dalla versione di Excel che usi.
peppo

Excel 2010
peppo55
Utente Senior
 
Post: 154
Iscritto il: 30/09/12 13:51

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi wallace&gromit » 27/12/13 17:20

quello che puoi fare è creare un foglio di appoggio nel file in cui esegui il cerca.vert.
In cella A1 c'è il riferimento al nome del file, per es. "2013_08.xls"
in A2 inserisci la formula:
Codice: Seleziona tutto
=INDIRETTO(INDIRIZZO(RIF.RIGA();RIF.COLONNA();;;"C:\pippo\["&A1&"]Foglio1"))

copi e incolli nelle altre celle.
Poi su un'altra pagina esegui il cerca.vert

A rigor di logica questa formula dovrebbe funzionare anche inserita direttamente nella formula cerca.vert (ma per qualche arcano motivo che magari qualcuno saprà spiegare mi funziona solo quando il file cui faccio riferimento è anch'esso aperto).
stato 2014: Office2003/2013 su win7
Avatar utente
wallace&gromit
Utente Senior
 
Post: 1421
Iscritto il: 16/01/12 14:21

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi wallace&gromit » 27/12/13 17:37

EDIT: Ops, per lo stesso arcano motivo non funziona neanche questa formula, mi sembrava di averla testata correttamente, invece chiudendo il file il riferimento salta!
stato 2014: Office2003/2013 su win7
Avatar utente
wallace&gromit
Utente Senior
 
Post: 1421
Iscritto il: 16/01/12 14:21

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi Anthony47 » 27/12/13 18:47

La sintassi del Cerca.Vert per un file diverso dal corrente e' del tipo
Codice: Seleziona tutto
=CERCA.VERT(A1;'D:\Directory\[NomeFile.xls]NomeFoglio'!Matrice;3;0)


Pero' non si puo' usare la funzione INDIRETTO (per calcolare nome file /Foglio / matrice di lavoro) se il file corrispondente non e' aperto (vale anche per w&g); devi quindi ripiegare su una macro.
Usando l' evento WorksheetChange potrai elaborare la formula e inserirla nella cella dove vuoi leggere il risultato; ad esempio:
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" And Target.Count = 1 Then   '<< La cella col nome file
Dim myPath As String, myFile As String, myForm As String
    myPath = "D:\PROVA\"   '<< Il percorso dei file, con lo "\" finale
    myFile = Range("B1").Value  '<< La cella che contiene il nome file
    myForm = "=CERCA.VERT(A1;'" & myPath & "[" & myFile & "]Giocatori'!$A$1:$C$1000;2;0)"   '<< NomeFoglio e Intervallo
    Range("E1").FormulaLocal = myForm
End If
End Sub
Tasto dx sul tab col nome del foglio su cui lavori, scegli Visualizza codice: ti si aprira' l' editor delle macro. Copia il codice e incollalo el frame di dx; personalizza le istruzioni marcate << rispettando rigorosamente la sintassi indicata.

Poi torna sul foglio excel, prova a modificare la cella col nome file e controlla che nella cella desiderata sia presente la formula e il risultato desiderato.

Ciao, fai sapere.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi sceriffopeschiera » 29/12/13 03:03

Grazie davvero a tutti per le vostre indicazioni e suggerimenti. La soluzione di Anthony è perfetta, produce esattamente il risultato che volevo, GRAZIE MILLE!!! :D

A beneficio degli altri utenti che incappino nello stesso mio problema, specifico che il nome del file contenuto nella cella B1 deve comprenderne l'estensione (.xls, .xlsx). Per intenderci, tornando al mio esempio, nella cella B1 dovremo avere 2013_08.xlsx, 2013_09.xlsx, 2013_10.xlsx, 2013_11.xlsx, etc…
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi mirko876 » 29/03/16 09:38

Ciao,

Io ho un problema molto simile che si dovrebbe risolvere con una macro:
Ho le colonne B C D dalla riga 3 alla riga 10000 in cui ho dei cerca vert con indirizzi che vorrei rendere dinamici. Nella colonna E ho il nome del file Excel da cui devo andare a leggere l'indirizzo.

La formula della cella B3 dovrebbe diventare la seguente: Cerca.vert(A3;E3&"A1:B100";2;FALSO).
Per C3:Cerca.vert(A3;E3&"A1:C100";3;FALSO).
Dove in E3 ho il file Excel di riferimento il foglio.

Qualcuno mi saprebbe indicare cosa devo inserire nella macro?

Grazie in anticipo
mirko876
Newbie
 
Post: 1
Iscritto il: 29/03/16 09:29

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi Anthony47 » 29/03/16 22:42

Non ho capito se ogni riga ha in colonna E il file (+ il foglio) a cui fare riferimento o se il file (+ il foglio) e' sempre lo stesso (e allora dove sitrova?).
Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "CERCA.VERT con riferimenti esterni dinamici":


Chi c’è in linea

Visitano il forum: Nessuno e 4 ospiti