[Excel] Dateien relativ referenzieren

[SaxnPaule]

Hallo Community,

ich stehe mal wieder vor einer Excel Herausforderung, bei der ich ohne Hilfe nicht weiterkomme.

Ich habe jeweils eine Datei "Datenblatt.xlsx" in einem Unterverzeichnis mit der Mitgliedsnummer. Also:
Stammdaten/001/Datenblatt.xlsx
Stammdaten/002/Datenblatt.xlsx
Stammdaten/003/Datenblatt.xlsx

Im Überverzeichnis möchte ich jetzt eine Übersichtsliste aller Mitglieder erstellen.
Stammdaten/Uebersicht.xlsx

Diese soll nun in jedes einzelne Verzeichnis gehen und aus dem Tabellenblatt "aktuell" z.B. die Zelle B2 auslesen

Beispiel:

Nummer	Name
001	=[Datenblatt.xlsx]aktuell!$B$2
002
003

Allerdings fehlt mir die Referenz auf den Unterordner, welcher immer gleich dem Wert in der Spalte "Nummer" ist.
Also quasi "[/$A2/Datenblatt.xlsx]aktuell!$B!2


Kann mir jemand weiterhelfen?

 

[halwe]

Laut Hilfe funktionieren indirekte Verweise auf eine nicht geöffnete Arbeitsmappe nicht. Wir haben das hier schon mal diskutiert.

 

[MickH74]

Du könntest mit VBA die Arbeitsmappen nach der Reihe öffnen und auslesen. Direkt in Excel und ohne VBA geht es nicht afaik.

 

[SaxnPaule]

Mhh schade, das ist leider keine Lösung, da es 180 Mitglieder sind

 

[Haplo]

Wenn Du einen "modernes" Excel mit PowerQuery hast, geht das.
Ist (insb. wenn man noch nie mit PowerQuery gearbeitet hat) nicht ganz simpel, aber es gibt viele Online-Anleitungen dazu. Z.B. diese hier: www.myexcelonline.com

 

[Alchemist]

Naja für sowas ist VBA ja gerade da.

http://www.office-loesung.de/ftopic463291_0_0_asc.php

Public Function GetValue(path$, file$, sheet$, range_ref$)
    'Holt einen Wert aus einer _geschlossenen_ Arbeitsmappe
    'Nur in VBA zu gebrauchen; nicht aus einer Tabellenzelle heraus
    '© John Walkenbach / Übersetzt von Thomas Ramel

    'Die GetValue-Funktion, benötigt die vier unten angeführten Arugmente:
    'path: Das Laufwerk und den Pfad der geschlossenen Datei (z.B. "C:\Daten")
    'file: Der Dateiname der Arbeitsmappe (z.B. "MeineDatei.xls")
    'sheet: Der Name des TabellenblattesThe worksheet name (z.B. "Tabelle1")
    'ref: Der Zellbezug (z.B. "A1")
Dim arg                  As String
    'Sicherstellen, d*** die Datei exisiert
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "Datei nicht gefunden"
        Exit Function
    End If
    'Den Aufruf-String zusammenstellen
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
          Range(range_ref).Range("A1").Address(, , xlR1C1)
    'Ausführen des XL4-Makros
    GetValue = ExecuteExcel4Macro(arg)
End Function

Public Sub HoleWert()
Dim rngZelle             As Range
    Application.ScreenUpdating = False

    For Each rngZelle In ActiveSheet.Range("A1:C10")
        rngZelle = GetValue("\\server\Pfad\", "Dateiname.xls", "Tabelle1", rngZelle.Address)
    Next rngZelle
    Application.ScreenUpdating = True
End Sub

 

[halwe]

Ich würde eher dazu tendieren, irgendwo alle benötigten Daten mit festen Verweisen abzuholen, in einem separaten Arbeitsblatt zwischenzuspeichern und dann auf diese Daten zuzugreifen.

 

[SaxnPaule]

Okay, ich habe das ganze jetzt umgebastelt und in eine Datei gepackt. Funktioniert auch ganz gut, AAAABER:

Nummer	Name
001	Mustermann
002	0

Ich habe die Tabellenblätter alle mit 001, 002, 003, ... benannt.
Die Felder fülle ich mit folgender Formel:

=INDIREKT(TEXT([@Nummer];"000")&"!A4")

Funktioniert auch prima, allerdings nur dann, wenn die Quellzelle auch gefüllt ist. Ist sie leer, wird eine 0 angezeigt.

Leider bekomme ich es nicht hin die Zelle leer anzuzeigen, wenn die Referenz auch leer ist.
Folgendes habe ich bereits versucht:

=WENN(ISTLEER(INDIREKT(TEXT([@Nummer];"000")&"!A4"));"";INDIREKT(TEXT([@Nummer];"000")&"!A4"))

und

=WENN((INDIREKT(TEXT([@Nummer];"000")&"!A4"))="";"";INDIREKT(TEXT([@Nummer];"000")&"!A4"))

Auch eine Prüfung auf 0 anstatt "" führt nicht zum gewünschten Ergebnis.

Ich habe es hinbekommen:

=WENN(INDIREKT(TEXT([@Nummer];"000")&"!A4")=0;"";INDIREKT(TEXT([@Nummer];"000")&"!A4"))

 

[halwe]

Oder eben einfach beim Format der entsprechenden Ergebniszelle zwei ; hinter den Formatcode schreiben, z. B. "Standard;;" oder "0;;", damit wird die Anzeige der Nullen am einfachsten unterdrückt.