Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Das Update ist abgeschlossen, Ihr könnt unser Forum jetzt wieder wie gewohnt nutzen. Eventuelle Bugs und Auffälligkeiten postet Ihr bitte im entsprechenden Forum im Bereich Forumstechnik. x


aus geschlossener Datei Daten übernehmen mit Pfad in Zelle
#1
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'
ABCDEFGHIJ
12Pfad:C:\temp\
13Datei:2022.xls
14BlattBilanz (1)
15Spalte:R
16
17Aktiva
18Bereich3.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)
19A.Anlagevermögen3.280.000,00 €Zellbereich 1:'C:\temp\[2022.xls]Bilanz (1)'!$C$15:$C$28
20A.I.Immaterielle Vermögensgegenstände280.000,00 €Zellbereich 2:'C:\temp\[2022.xls]Bilanz (1)'!$E$15:$E$28
21A.I.1.Selbst geschaffene gewerbliche Schutzrechte und ähnliche Rechte und Werte2005150.000,00 €Pfad über Zelle:0
22A.I.2.entgeltlich erworbene Konzessionen, gewerbliche Schutzrechte und ähnliche Rechte und Werte sowie Lizenzen an solchen Rechten und Werten200620.000,00 €
23A.I.3.Geschäfts- oder Firmenwert200710.000,00 €
24A.I.4geleistete Anzahlungen2012100.000,00 €
25A.II.Sachanlagen3.280.000,00 €
26A.II.1.Grundstück, grundstücksgleiche Rechte und Bauten einschließlich der Bauten auf fremden Grundstücken20093.280.000,00 €

ZelleFormel
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":

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

.xlsb   2022.xlsb (Größe: 11,14 KB / Downloads: 6)
Antworten Top
#2
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.
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Nutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • Rabe
Antworten Top
#3
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...


Angehängte Dateien
.xlsb   Bilanz Test.xlsb (Größe: 116,43 KB / Downloads: 3)
Der sicherste Ansatz für einen Irrtum ist der Glaube, alles im Griff zu haben.
Nur, weil ich den Recorder bedienen kann, macht mich das noch lange nicht zum Musiker.

Ciao, Ralf

[-] Folgende(r) 1 Nutzer sagt Danke an Ralf A für diesen Beitrag:
  • Rabe
Antworten Top
#4
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.
Zum übersetzen von Excel Formeln:

http://dolf.trieschnigg.nl/excel/index.p...gids=en+de
[-] Folgende(r) 1 Nutzer sagt Danke an snb für diesen Beitrag:
  • Rabe
Antworten Top
#5
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.
Antworten Top
#6
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
Antworten Top
#7
Hallöchen,

Zitat: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
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste