PDA

Visualizza versione completa : excel - funzione INDIRETTO


derekk
07-10-2017, 08.44.53
ciao a tutti, volevo porvi un quesito al quale proprio non riesco a venirne a capo
Ho un file che si chiama ottobre, devo prendere dei valori nel file settembre.
Mi serve che le formule nell riga C3:C5 cambino in base al nome specificato in a3:a5 in modo dinamico su un altro file situato in z:\2017\settembre.xlsx.
Ho usato la funzione
+INDIRETTO("'Z:\2017\settembre.xlsx'!"&A3&"_rim") che punta sulla cella a cui ho assegnato il nome tizio_rim nel file settembre.xlsx.
Tutto funziona con il file aperto, mentre se è chiuso mi dà errore. Ho letto infatti che INDIRETTO non funziona sui file chiusi e allora sono qua a chiedere aiuto. Qualcuno conosce un modo alternativo?
In più vorrei che la formula variasse oltre che in base al nome in A3, anche in base al mese in a1. Si riesce?
Non so se sono riuscito a spiegarmi ma allego un file di esempio.
Grazie

Alexsandra
09-10-2017, 20.43.43
puoi usare una macro oppure una formula con un collegamento diretto tipo

='Z:\2017\[settembre.xlsx]Foglio1'!A1

modificando i percorsi dell'esempio con i valori delle varie celle.

Alexsandra
09-10-2017, 20.51.07
Ho visto il tuo esempio, ma quanti nomi devi gestire?
mi riferisco a tizio, caio, sempronio etc...

derekk
10-10-2017, 21.09.36
sono 12 nomi, ma possono cambiare nel tempo

Alexsandra
12-10-2017, 00.02.43
e perchè allora non usare un foglio solo senza tanti salti?
basta solo modificare il layout in cui inserisci i dati.
se vuoi ti posto un'immagine di come lo farei io, poi mi dici se ti va bene

derekk
12-10-2017, 08.59.35
.... EDIT ...
al seguente link
http://www.xtremevbtalk.com/excel/312473-indirectex-indirect-closed-workbooks.html
ho trovato un codice che dovrebbe permettere alla funzione INDIRECTEX di andare a lavorare sui file chiusi! La formula restituisce però sempre 0. Di vba non ne capisco molto, quindi se qualcuno riesce ad aiutarmi...

PS: a me serve che ci siano 12 file, 1 per ogni mese (gennaio, febbraio ....) perchè in realtà il foglio di calcolo è più complicato dell'esempio che ho postato. per ogni persona c'è un foglio che calcola le ore, le assenze, straordinari, assenze, recuperi ore ecc.... e i calcoli deve farli per mese

Alexsandra
14-10-2017, 18.37.20
.... EDIT ...
al seguente link
http://www.xtremevbtalk.com/excel/312473-indirectex-indirect-closed-workbooks.html
ho trovato un codice che dovrebbe permettere alla funzione INDIRECTEX di andare a lavorare sui file chiusi! La formula restituisce però sempre 0. Di vba non ne capisco molto, quindi se qualcuno riesce ad aiutarmi...

PS: a me serve che ci siano 12 file, 1 per ogni mese (gennaio, febbraio ....) perchè in realtà il foglio di calcolo è più complicato dell'esempio che ho postato. per ogni persona c'è un foglio che calcola le ore, le assenze, straordinari, assenze, recuperi ore ecc.... e i calcoli deve farli per mese

Io invece intendevo di usare 1 file solo con 12 fogli (1 x mese) e lavorare sulle colonne impostando il layout.

derekk
14-10-2017, 22.40.50
non riesco a fare ciò che mi chiedi, calcola che ogni foglio (tizio, caio ecc) ha 160 righe e 30 (o 31) colonne, non ho studiato excel, sono autodidatta e magari voi più esperti fareste gli stessi calcoli con 1/3 delle righe che ho usato io... c'è un tabellone con i turni di lavoro sul foglio principale, e poi 1 foglio per ogni persona, dove si calcola tutto. Poi ho un foglio riepilogativo con tutti i dati di tutte le persone. La possibilità di utilizzare la funzione con file chiusi faceva proprio al mio caso, per automatizzare tutto: ma non è un grosso problema, al cambio mese faccio trova/sostituisci nelle formule...

Alexsandra
14-10-2017, 23.20.02
Ok, un paio di cose.

i file sono tutti in una cartella a loro dedicata?
** se non lo sono ti consiglio di metterceli

devi importare un intervallo o un singolo dato (singola cella)

derekk
14-10-2017, 23.32.23
Ottobre.xlsm
A2=ottobre
A3=tizio
B3=+INDIRETTO("'Z:\2017\settembre.xlsx'!"&A3&"_rim")
Quindi va a prendere il valore della cella chiamata tizio_rim nel file settembre. Poi, se riuscissi a sostituire in modo dinamico anche il nome del file (che corrisponde al mese precedente) ancora meglio

Alexsandra
14-10-2017, 23.41.49
Non è quello che chiedevo. nel tuo file di esempio ho trovato 2 file (settembre e ottobre) e al proprio interno avevano 1 foglio (foglio 1) che usavi come riepilogo e 1 foglio per ogni dipendente.

Ora dal tuo ultimo post parli di ottobre e mi sembra di capire che importi dal file settembre le rimanenze di ferie etc.. oltre alle ore fatte.
ma queste rimanenze, che non vedo nel file di settembre, sono in una cella.
per cui devi solo importare una cella.

giusto così??

derekk
14-10-2017, 23.59.46
Sono parecchi riferimenti che devo importare, per ogni utente saranno una decina, sono singole celle che ho rinominato per comodità, vorrei che le formule cambiassero dinamicamente in base al nome del dipendente....e anche in base al mese. Mi spiace che non riesco a spiegarmi bene:fool: con settembre aperto la formula funziona se chiuso no.

Alexsandra
15-10-2017, 08.59.33
con una formula devi inserire il collegamento diretto, come ti avevo già lasciato qualche post sopra. la formula è

='C:\Users\User\Desktop\[settembre.xlsx]sempronio'!$B$33

in cui la parte in verde è il percorso del file, tra le parentesi quadre va inserito il nome del file completo di estensione e subito dopo il nome del foglio seguito dall'indirizzo della cella. occhio agli apici all'inizio della formula e dopo il nome del foglio.

se vuoi evitare di inserire manualmente il nome del foglio etc. si deve usare vba, ma ho bisogno di altre informazioni che non mi hai ancora dato.

derekk
15-10-2017, 18.45.00
scusa ma questa formula è quella classica,
ho un file Ottobre.xlsm
in A3=tizio
in A4=caio
ecc...
B3=+INDIRETTO("'Z:\2017\settembre.xlsx'!"&A3&"_rim ")
che sarebbe così : =INDIRETTO("'Z:\2017\settembre.xlsx'!"tizio_rim")
in b4 sarebbe =INDIRETTO("'Z:\2017\settembre.xlsx'!"caio_rim")
ecc...
io vorrei cambiare solo i valori in a3, a4 e non tutte le formule, quelle vorrei cambiassero dinamicamente in base al valore di a3, a4 ecc....
Grazie per l'aiuto

Alexsandra
15-10-2017, 20.45.39
Mah .... quì c'è qualche problema di comunicazione. Leggi quello che scrivo??

1) INDIRETTO non funziona con i file chiusi
2) Per usare una formula deve essere esposta come nel mio precedente post
3) Se vuoi altro si deve usare VBA

Chiarito questo, se vogliamo continuare mi devi dare delle informazioni. Se non riesci a spiegarti bene, basta solo che rispondi alle mie domande.

derekk
15-10-2017, 21.16.07
Con vba credo sia l'unica possibilità.
Sorry:dntknw:

Alexsandra
16-10-2017, 20.44.04
Ok. torno a dire che questo è un uso anomalo del processo, comunque .....

1) siamo in Ottobre e apri il file Ottobre.xls, a Novembre apri Novembre.xls etc...
2) Importi dal file settembre.xls i dati nelle celle A3,A4,A5 etc.. (nomi dipendenti e i valori dalle celle C3,C4,C5 etc..

è corretto questo?

3) Quanti dipendenti ci possono essere nella colonna A

derekk
16-10-2017, 21.42.59
è esatto. 12 dipendenti

Alexsandra
17-10-2017, 23.00.52
Prova questa macro con queste impostazioni

1) apri il file ottobre.xlsm
2) nel foglio 1 in A1 digita la data 01/10/2017 e applica la formattazione personalizzata "mmmm"
3) Ora in A1 ti compare il mese di ottobre per esteso.
4) In A2 inseirsci la formula =TESTO(A1-1;"mmmm") e ti compare il mese precedente (settembre)
5) entra nell'editor di VBA (Alt+F11) crea un modulo e inserisci questo codice

Sub prova1()

cart = "C:\Users\User\Desktop\" '
nomeFgl = "Foglio1" 'foglio di origine da cui prelevare i dati
nomeFl = ActiveSheet.Cells(2, 1) 'nome del file da aprire
r = 3: c = 1

For t = 3 To 14
indi = Chr(64 + c) & r
indi1 = Chr(64 + c + 2) & r

perc = "'" & cart & "[" & nomeFl & "]" & nomeFgl & "'!" & Range(indi).Range("A1").Address(, , xlR1C1)
perc1 = "'" & cart & "[" & nomeFl & "]" & nomeFgl & "'!" & Range(indi1).Range("A1").Address(, , xlR1C1)

Sheets("Foglio1").Cells(t, 1) = ExecuteExcel4Macro(perc)
Sheets("Foglio1").Cells(t, 3) = ExecuteExcel4Macro(perc1)
r = r + 1
Next t

End Sub

6) Cambia la 1° riga di codice con il tuo percorso che deve terminare con la barra rovesciata \
7) torna al foglio1 di Excel e puoi inserire una forma rettangolare a cui abbini la macro appena inserita.
8) clicca sul rettangolo e fai girare la macro.

La macro importa i valori della colonna A (nomi dipendenti) e della colonna C (ore giacenza)
per i 12 dipendenti.

derekk
18-10-2017, 17.33.37
beh che dire, funziona alla grande! :rotolo:
con qualche riga hai risolto un problema enorme (per me)
adesso sto cercando di adattarla al mio foglio originale e ci sto studiando un po' sopra
Ma vorrei delle dritte
1) come modifico le celle da importare? A me interessano le colonne N Q T W Z AD AN AP BE, sempre partendo dalla riga 3: adesso sono 13 dipendenti per ognuno di loro ho unito 3 celle: quindi tizio è A3, caio A6 ecc...
2) è possibile far partire la macro all'apertura del file (senza cliccare sul tasto della macro?)

Grazie

Alexsandra
18-10-2017, 20.30.25
....
2) è possibile far partire la macro all'apertura del file (senza cliccare sul tasto della macro?)

si basta che metti il codice nel foglio (foglio 1 di ottobre.xls) nell'evento activate e ogni volta che attivi il foglio1 parte la macro

per il resto cambia il codice con questo

Sub prova1()

cart = "C:\Users\User\Desktop\" '
nomeFgl = "Foglio1" 'foglio di origine da cui prelevare i dati
nomeFl = ActiveSheet.Cells(2, 1) 'nome del file da aprire
r = 3: c = 1

For t = 3 To 15
indi = Chr(64 + c) & r
indi1 = Chr(64 + c + 2) & r
perc = "'" & cart & "[" & nomeFl & "]" & nomeFgl & "'!" & Range(indi).Range("A1").Address(, , xlR1C1)
perc1 = "'" & cart & "[" & nomeFl & "]" & nomeFgl & "'!" & Range(indi1).Range("A1").Address(, , xlR1C1)
Sheets("Foglio1").Cells(r, 1) = ExecuteExcel4Macro(perc)
Sheets("Foglio1").Cells(r, 3) = ExecuteExcel4Macro(perc1)
For x = 14 To 28
indi3 = Chr(64 + x) & r
perc = "'" & cart & "[" & nomeFl & "]" & nomeFgl & "'!" & Range(indi3).Range("A1").Address(, , xlR1C1)
Sheets("Foglio1").Cells(r, x) = ExecuteExcel4Macro(perc)
x = x + 2
Next
x = 30
indi4 = Cells(r, x).Address
perc = "'" & cart & "[" & nomeFl & "]" & nomeFgl & "'!" & Range(indi4).Range("A1").Address(, , xlR1C1)
Sheets("Foglio1").Cells(r, x) = ExecuteExcel4Macro(perc)

For y = 40 To 42 Step 2
indi5 = Cells(r, y).Address
perc = "'" & cart & "[" & nomeFl & "]" & nomeFgl & "'!" & Range(indi5).Range("A1").Address(, , xlR1C1)
Sheets("Foglio1").Cells(r, y) = ExecuteExcel4Macro(perc)
Next

indi6 = Cells(r, 57).Address
perc = "'" & cart & "[" & nomeFl & "]" & nomeFgl & "'!" & Range(indi6).Range("A1").Address(, , xlR1C1)
Sheets("Foglio1").Cells(r, 57) = ExecuteExcel4Macro(perc)

r = r + 3
Next t

End Sub

derekk
19-10-2017, 16.48.19
tutto ok, il codice funziona benone. Però adesso che l'ho adattato al mio foglio, mi sono reso conto di quando mi dicevi "questo è un uso anomalo del processo".

puoi usare una macro oppure una formula con un collegamento diretto tipo

='Z:\2017\[settembre.xlsx]Foglio1'!A1

modificando i percorsi dell'esempio con i valori delle varie celle.

infatti con il collegamento diretto funziona, ed sino ad oggi infatti il mio file è stato così, ma volevo automatizzare totalmente le formule
ho bisogno di una formula dinamica, cioè che cambia in base ad un paramentro (in A3) nel mio esempio mi sembrava di aver reso bene l'idea...

A3=tizio

con la formula in

C3=("'Z:\2017\settembre.xlsx'!"&A3&"_rim") diventa ("'Z:\2017\settembre.xlsx'!"tizio_rim")
prende il valore di A3 e aggiunge il suffisso_rim (poi ce ne sono altri tipo _LP, _RL ecc).
se A3 cambia, ad esempio A3=caio la formula cambia in ("'Z:\2017\settembre.xlsx'!"caio_rim"), caio_LP, caio_RL ecc...
Facendo il collegamento diretto non funziona, serve indiretto che però funziona solo sui file aperti; ecco la mia domanda:
come ottenere quello che ho detto prima con un codice vba?
Più di così non sono in grado di spiegarmi, sorry
un grosso grazie

Alexsandra
19-10-2017, 20.13.27
..... mi sono reso conto di quando mi dicevi "questo è un uso anomalo del processo".
per gestire 13 dipendenti è assurdo lavorare su 12 file quando puoi tenere tutto in 1 file solo.

Ad ogni modo la macro fa esattamente quello che hai richiesto, anche se è sbagliato andare a cambiare il valore in A3 o A6 etc... sia che tu abbia una formula sia che usi una macro.
Cambiare 1 solo valore implica che si deve cambiare lo steso valore anche nel file del mese precedente. Se nel file Ottobre, in A3 metti tizio, devi fare la stessa modifica anche nel file settembre.
Se tu usi un intervallo denominato a cui assegni un nome (_rim, lp etc...) questo nome è sempre riferito ad una cella. In pratica tu puoi cambiare il nome di tizio con caio, ma sulla riga di caio verranno importati i dati di tizio.

ecco la mia domanda:
come ottenere quello che ho detto prima con un codice vba?
lo hai già, non vedi nessuna formula, ma vedi il valore.
oppure fondi tutto in un file unico e lavori con le formule.

derekk
20-10-2017, 00.10.19
proverò, intanto ti ringrazio moltissimo per la pazienza. Il codice mi sarà utile, ho cercato di capirci qualcosa, forse è la volta buona che mi metto a studiare un po' di vba ..
(B)