Office: Dateiname variabel gestaltbar?

Hallo,

ich benötige eine variable Verknüpfungen auf ca. 100 externe Exceldateien. D.h. ich möchte per Auswahl in einer Formel den Namen der Datei ändern wie eine Variable.
Bsp.: Verknüpfung auf "c:\stat4711.xls". Das "stat" bleibt konstant die 4711 möchte ich aber variable ersetzen/verändern können z.B. auf 4712.

Gibt es eine Formel (eventuell über VERKETTEN) mit der ich also in einer Zelle den Dateibezug ändern kann? Hat jemand eine Idee? Ich benötige es für Datenansichten mit individueller Auswahl der Quelldatei.

Danke, Woody

:)

 

Hi,

VERKETTEN bringt hier nicht viel, da lediglich einzelne Strings zu einem Gesamtstring verknüpft werden.

Was Du suchst, ist der Befehl INDIREKT(Bezug), mit dem Du eine Zieladresse aus Einzelteilen zusammenfügen kannst. Beispiel: In Zelle A1 steht"J". In Zelle A2 steht 25. Wenn Du in Zelle A3 die Formel
=Indirekt(A1&A2) schreibst, wird in A3 der Inhalt von Zelle J25 ausgegeben. Das funktioniert prinzipiell auch mit Adressen, die Pfad- und Dateiangaben enthalten, um so auf externe Dateien zuzugreifen.

Die Sache hat allerdings den Haken, dass die externe Datei geöffnet sein muss (getestet bis XL2000; ob's in XP auch so ist weiß ich nicht).

Ein varaibler Verweis auf eine externe Datei ist damit also leider kaum zu realisieren.

Falls Du eine Lösung finden solltest, würde mich das auch interessieren. Habe schon einige Nächte mit dem Problem gekämpft und auch die MS-Hotline konnte mir nicht helfen.

Ciao

Johannes

 

Klasse!

Danke für den tollen Hinweis. Nach ein paar Hürden hat es nun geklappt. Ich kann nun per Dropdown Menu die Bezüge zu den Quelldateien in meinen Sverweisen sehr komfortabel ändern.
Der Nachteil der im Hintergrund geöffneten Dateien ist angesichts der hohen Flexibilität akzeptabel (Okay, das Öffnen von über 200 Dateien dauert eben).

Nochmals vielen Dank.

Schönen Gruß
Woody

*Smilie *lachen.gif*

 

Gratuliere! Kannst Du Dein Vorgehen etwas genauer beschreiben? Ist bestimmt für auch für andere interessant.

Danke schon mal

Johannes

 

Okay, gute Idee. Hier also meine detailierte Lösung:

Ich habe über 200 gleich aufgebaute Reportdateien.
In einer Analysetabelle lasse ich mir Werte aus diesen Reports anzeigen (immer gleiche Zellenbezüge, nur der Dateinamenbezug variiert).

Hier eine Beispiel:
=WVERWEIS(F$8;'[stat4711.xls]Tabelle1!A:X;$H$5;FALSCH)

Der Dateiname beginnt immer mit "stat", nur die Nummer variiert. Hierfür wollte ich zur flexiblen Änderung eine schnelle Verstellmöglichkeit für einen Teil des Dateinamens haben.

Mittels der INDIREKT Funktion sieht meine Formel nun z.B. so aus:

=WVERWEIS(F$8;INDIREKT($A10&$B10&$C10);$H$5;FALSCH)

In Zelle A10 steht '[stat
in Zelle B10 steht 4711
in Zelle C10 steht .xls]Tabelle1!A:X;$H$5;FALSCH)

Der Dateiname wird also zusammengesetzt. Die Zelle B10 bezieht ihren Wert aus einer anderen Zelle (Zeilenbezeichnung in der Analysetabelle) welche ich über Gültigkeit/Liste meine über 200 Dateinummern zu Auswahl (per Dropdown Menü)beinhaltet.

So kann man sich die Daten aus den gewünschten Dateien anzeigen lassen und schnell wechseln. Die Auswahl der gewünschten Datei per Dropdown Menü ist natürlich optional. Direktes Eingeben tut´s auch.

Nun kann ich in Meetings "online" sehr flexibel gewünschte Vergleiche generieren und so sehr schnell auf die Wünsche meiner "Kunden" reagieren.

Achtung: im Hintergrund müssen alle Dateien geöffnet sein, welche ausgewählt wurden.

Ich hoffe andere haben auch dafür Verwendung oder noch bessere Ideen.

Schönen Gruß
Woody *top.gif*

 

Hallo Woody,

danke für die Darstellung. Nur eine kurze Rückfrage: Müsste nicht in C10 lediglich

".xls]Tabelle1!A:X"

stehen? So, wie Du es beschrieben hast, ergibt sich für die INDIREKT-Funktion kein gültiger Bezug, sondern z.B.:

=WVERWEIS(F$8;INDIREKT("'[stat4711.xls]Tabelle1!A:X;$H$5;FALSCH)");$H$5;FALSCH)

Ciao

Johannes

 

Exakt! Du hast natürlich Recht. Da war ich wohl etwas zu schnell mit dem Einfügen ;-)

Woody

 

Hallo, ich habe mir das mit interesse durchgelesen.

Hab noch eine Frage dazu, das Ergebnis und die Eingabe für INDIREKT kommt doch nicht aus der Verlinkten Datei, muss die Datei dann geöffnet sein.?
Auf die andere Datei greift doch 'NUR' VERWEIS zu..... oder denke ich da falsch.......


Upppps, sorry, hab gar nicht auf das Datum geschaut da ich über die Suche den Artikel gefunden habe.....*rolleyes.gif*

Hier noch mein Problemfall:

Code:

Zum ändern habe ich nur das Jahr, aber leider in sehr vielen Zellen.......

 

Guten Morgen.!
Ich habe es gestern abend und heute morgen noch versucht wie in #5 umzusetzen, erhalte aber nur den Fehler BEZUG. :-(

Im original VERWEIS steht ja
'\ und nach sheet1 '
Muss ich bei INDIREKT die Hochkommas auch mit in die Zellen schreiben, oder nur den Pfad ab backslash backslash .??

Danke

 

Hallo,

wie Johannes bereits vor fast 15 Jahren festgestellt hat: Wenn Indirekt() einen Bezug auf eine andere Mappe enthält, muss die andere Mappen offen sein.
Ist diese Bedingung nicht erfüllt, kannst Du Dir weitere Versuche sparen.

Wenn die Mappe offen ist, hast Du die Formel falsch aufgebaut. Ohne die Formel zu sehen, kann Dir keiner sagen, was falsch ist.

Grüße
EarlFred

 

Danke schon mal.

=VERWEIS(10000000;INDIREKT(X1&Y1&Z1))
mit
X1='\server\Ordner\Unterordner
Y1=Jahr2017
Z1=[MAPPE1.xlsm]Sheet1'!$B:$B

und die Mappe1 ist auch offen.......

X1 mit Hochkomma Backslash Backslash server backslash Ordner backslash Unterordner

 

Backslashs werden sichtbar, wenn Du z. B. 2 davon hintereinander schreibst.

Da die Mappe ja offen ist, ist die Pfadangabe falsch.

Tipp:
Wenn die Zielmappe offen ist, erstelle die Formel ohne Indirekt(). Den Bereich in der Zielmappe durch wechseln in die Mappe und aufziehen des Bereichs markieren. Dann enthält die Formel den syntaktisch richtigen Bezug.
=Formel(...Bezug...)

Dann Indirekt() um den Bezug und den Bezug in Anführungszeichen setzen.
=Formel(...Indirekt("Bezug")...)

Wenn die Formel damit noch richtig arbeitet, ersetze das Jahr durch
=Formel(...Indirekt("Bezug_vor_Jahr" & ZelleMitJahr & "Bezug_nach_Jahr")...)

 

Wenn man dann noch beim Dropdownfeld das Ereignis "OnChange" als Makro hinterlegt, kann man das doch einfach lösen, dass die Datei geöffnet sein muss: (Sofern dies vom Bearbeiter auch gewünscht wird)

Das ComboBox_Change Ergeinis öffnet die Datei *Smilie
Hoffe konnte mit meinem Ansatz auch noch was sinnvolles zum Thema beitragen *Smilie

LG
Yuri!

 

Hallo Yuri,

grundsätzlich eine gute Idee.

Du hast dann aber keine vbafreie Mappe mehr. Zudem kann man, wenn man auf VBA-Lösungen umschwenkt, auch ganz andere, dann formelfreie Wege gehen.

Ich rate davon ab, VBA und Formeln zu vermischen, es sei denn, man verwendet UDFs.

Grüße
EarlFred

 

Stimmt, daran hab ich nicht gedacht.
Aber: Ich hab das mal eben ein bisschen weiter gesponnen.
Wenn man mittels Zellbezug und dem Dropdown arbeitet, dann kann man
über Sverweis (sofern die Pfade der Dateien auf einem Tabellenblatt hinterlegt sind) sich mit

Code:

den Pfad basteln, so braucht man dann nur auswählen, geht mit der Maus auf die Zielzelle und braucht nur mal eben auf den Link klicken *Smilie
Tadaa... VBA-Free...

Danke für den Input EarlFred *Smilie

LG
Yuri