aus geschlossener Datei Daten übernehmen mit Pfad in Zelle

[Rabe]

Hi zusammen,

von einem Kollegen habe ich folgende Anfrage:
gegeben sind zwei Dateien, die eine heißt Bilanz-Test.xlsb und die andere 2022.xlsb.

In die Bilanz-Test sollen Daten aus der 2022 entnommen werden, wobei Pfad, Dateiname und Blattname in der Bilanz-Test in Zellen stehen sollen.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J
12		Pfad:		C:\temp\
13		Datei:		2022.xls
14		Blatt		Bilanz (1)
15		Spalte:		R
16
17	Aktiva
18					Bereich	3.280.000,00 €			Notiz:	=XVERWEIS(E21;'C:\temp\[2022.xlsb]Bilanz (1)'!$C$15:$C$28;'C:\temp\[2022.xlsb]Bilanz (1)'!$E$15:$E$28;0)
19	A.	Anlagevermögen				3.280.000,00 €			Zellbereich 1:	'C:\temp\[2022.xls]Bilanz (1)'!$C$15:$C$28
20		A.I.	Immaterielle Vermögensgegenstände			280.000,00 €			Zellbereich 2:	'C:\temp\[2022.xls]Bilanz (1)'!$E$15:$E$28
21			A.I.1.	Selbst geschaffene gewerbliche Schutzrechte und ähnliche Rechte und Werte	2005	150.000,00 €			Pfad über Zelle:	0
22			A.I.2.	entgeltlich erworbene Konzessionen, gewerbliche Schutzrechte und ähnliche Rechte und Werte sowie Lizenzen an solchen Rechten und Werten	2006	20.000,00 €
23			A.I.3.	Geschäfts- oder Firmenwert	2007	10.000,00 €
24			A.I.4	geleistete Anzahlungen	2012	100.000,00 €
25		A.II.	Sachanlagen			3.280.000,00 €
26			A.II.1.	Grundstück, grundstücksgleiche Rechte und Bauten einschließlich der Bauten auf fremden Grundstücken	2009	3.280.000,00 €

Zelle	Formel
F18	=F19+F37+F52+F53+F54
F19	=F20+F25+F30
J19	="'" & D12&"[" & D13 & "]" & "Bilanz (1)'!$C$15:$C$28"
F20	=SUMME(F21:F24)
J20	="'" & D12&"[" & D13 & "]" & "Bilanz (1)'!$E$15:$E$28"
F21	=XVERWEIS(E21;'C:\temp\[2022.xlsb]Bilanz (1)'!$C$15:$C$28;'C:\temp\[2022.xlsb]Bilanz (1)'!$E$15:$E$28;0)
J21	=XVERWEIS(E21;$J$19;$J$20;0)
F22	=XVERWEIS(E22;'C:\temp\[2022.xlsb]Bilanz (1)'!$C$15:$C$28;'C:\temp\[2022.xlsb]Bilanz (1)'!$E$15:$E$28;0)
F23	=XVERWEIS(E23;'C:\temp\[2022.xlsb]Bilanz (1)'!$C$15:$C$28;'C:\temp\[2022.xlsb]Bilanz (1)'!$E$15:$E$28;0)
F24	=XVERWEIS(E24;'C:\temp\[2022.xlsb]Bilanz (1)'!$C$15:$C$28;'C:\temp\[2022.xlsb]Bilanz (1)'!$E$15:$E$28;0)
F25	=SUMME(F26:F29)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Mit direkter Pfadeingabe in der Formel (siehe Spalte F) funktioniert es.

In Spalte J habe ich es mit zusammengesetzter Formel versucht => klappt aber nicht.

Wie löse ich das denn?

Gruß Ralf

Hier die zwei Dateien für das Verzeichnis "temp":

  Bilanz Test.xlsb (Größe: 10,84 KB / Downloads: 5)

  2022.xlsb (Größe: 11,14 KB / Downloads: 6)

[BoskoBiati]

Hi,

ich denke, das wird ohne VBA nicht gehen. Mit Formeln wäre INDIREKT die Funktion der Wahl, aber die funktioniert nicht mit geschlossenen Dateien.

[Ralf A]

Hi,

immer schlecht, wenn das gewünschte Ergebnis von den bereitgestellten Daten abweicht. Aber... da sag ich nur, selbst dran Schuld, dann musst Du es Dir selbst anpassen...

Lösung in Tabelle2 erfolgte über Power Query...
Pfadangabe in Tabelle1 muss angepasst werden, in Tabelle2 rechtsklick in die Tabelle und auf Aktualisieren klicken. 
Was mit Spalte R sein soll? Keine Ahnung, gibts in Deinem Beispiel nicht...

[snb]

UDF:

Code:

Function F_snb(y)
  sn = GetObject("J:\download\2022.xlsb").Sheets(1).UsedRange

  For j = 1 To UBound(sn)
    If sn(j, 2) = Val(y) Then Exit For
  Next
  F_snb = sn(j, 4)
End Function

In F21:

PHP-Code:

=F_snb(E21) 


NB. 2022.xlsb : Usedrange fängt nicht an in $A$1: brrrr.

[Rabe]

Hallo Ralf,

danke für Deine Lösung mit PQ. So geht es.

Daß die Bezeichnung in Spalte D nicht mit der Nummerierung aus Spalte E und deswegen den Werten in Spalte F übereinstimmt, kommt daher, daß die Quelle Daten so rausgegeben werden. Das ziehen wir dann in der Weiterverarbeitung glatt.

Gruß Ralf

---

Hi snb,

danke. Auch so klappt es (natürlich, da Lösung von Dir).

Mal schauen, welche Lösung der Kollege vorzieht.

Gruß Ralf

Daß die Daten nicht in A1 anfangen, ist dem Umstand geschuldet, daß ich zur Erzeugung der Beispieldateien die nicht benötigten Zellen geleert habe.

[Rabe]

Hi,

er hat es nun mit VBA gelöst:
er übernimmt die Strings für Pfad, Datei- und Blatt-Name über den Bezug auf die entspr. Zellen in Variablen und setzt dann die Formel zusammen und schreibt diese dann in die Ziel-Zelle.
Dadurch wird dann jeweils der einzelne Bilanz-Wert aus der geschlossenen Übersichtsdatei geholt.

Danke allen!

Gruß Ralf

[schauan]

Hallöchen,

NB. 2022.xlsb : Usedrange fängt nicht an in $A$1: brrrr.

da hat doch auch jemand Gründe dafür dargelegt, zuweilen nicht in Zeile/Spalte 1 zu beginnen - so-arbeitest-du-in-excel-mit-der-rs1-methode