Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1220to1224
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Verknüpfung INDIREKT

Verknüpfung INDIREKT
chris58
Hallo !
Ich habe mir mit Hilfe anderer, diese Formel zusammengebaut. Die funktioniert aber leider nicht richtig.
Es kommt immer #Wert! wenn ich auf Verknüpfung gehe. Ich will mit dieser Formel von der Datei
"Bilanzarbeiten" aus dem Tabellenblatt "Saldenliste" alle Werte die in D4:D10000 stehen zusammenfassen.
Die Beträge von D4:D10000 sollen monatlich zusammengefasst werden, wenn sie die Bedingung "Anlagenkauf" erfüllen. Ich habe diese Formel aus 4 Formeln zusammengetragen, doch es klappt halt nicht.
Bitte um Hilfe, wenn möglich
chris
=SUMME(INDIREKT((MONAT([Bilanzarbeiten.xls]Saldenliste!$A$4:$A$10000)=ZEILE(D1)) *([Bilanzarbeiten]Saldenliste!C$6:$C$10000="Anlagenkauf")*[Bilanzarbeiten]Saldenliste!$D$6:$D$10000))

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Verknüpfung INDIREKT
01.07.2011 21:53:34
fcs
Hallo Chris,
ich kann jetzt nicht nachvollziehen, wie du auf die Idee ekommen bist hier INDIREKT zu verwenden, aber die Funktion für solche Anwendungen ist SUMMENPRODUKT.
INDIREKT liefert grundsätzlich keine Ergebnisse, wenn die Quelldatei geschlossen ist, sollte also generell nur für Verweise auf Zellen/Zellbereiche in der gleichen Arbeitsmappe benutzt werden.
Passe deine Formel wie folgt an, wenn beide Dateien (Datei mit Formel und Datei "Bilanzarbeiten.xls" geöffnet sind.
=SUMMENPRODUKT((MONAT([Bilanzarbeiten.xls]Saldenliste!$A$6:$A$10000)=ZEILE(D1))
*([Bilanzarbeiten.xls]Saldenliste!C$6:$C$10000="Anlagenkauf")*
[Bilanzarbeiten.xls]Saldenliste!$D$6:$D$10000)

Achte darauf, dass die Bereiche in der Formel die gleichen Zeilenbereiche beinhalten! Hier jeweils 6 bis 10000.
Gruß
Franz
Anzeige
AW: Verknüpfung INDIREKT
01.07.2011 22:41:56
chris58
Hallo !
Ich habe das eingebaut und es funktioniert bedingt. Dürfte aber nicht an der Formel liegen. Ich kann ab Monate März die Daten übernehmen. Jänner und Februar gehen nicht. Da kommt die Zahl 0,00. Hast du eine Ahnung, an was das liegen kann. Da steht dann immer: Die Zelle oder das Diagramm, das Sie versuchen zu ändern ist geschützt usw.
Das Blatt ist aber nicht geschützt..............an was kann das liegen............hast du eine Ahnung ?
Ich habe einen Screen dazugepackt............bin am verzweifeln
Das wegen dem INDIREKT............desswegen, weil ich glaubte, wenn ich die Datei von der die Daten geholt werden, schliesse, das der Bezug dann wieder weg ist.
chris
https://www.herber.de/bbs/user/75563.jpg
Anzeige
AW: Verknüpfung INDIREKT
02.07.2011 00:46:57
fcs
Hallo Chris,
die Grafik mit der Fehlermeldung sagt mir nichts über mögliche Fehlerursachen.
Wenn es dich nicht stört, dass in den Zellen ggf. "#BEZUG!" steht, wenn die Datei Bilanzarbeiten.xls geschlossen ist, dann kannst du mit INDIREKT arbeiten. Die Formel Schaut dann so aus:
=SUMMENPRODUKT((MONAT(INDIREKT("'[Bilanzarbeiten.xls]Saldenliste'!A6:A10000"))=ZEILE(D1))*
(INDIREKT("'[Bilanzarbeiten.xls]Saldenliste'!C6:C10000")="Anlagenkauf")*
INDIREKT("'[Bilanzarbeiten.xls]Saldenliste'!D6:D10000"))
Die Datei hat dann in der tat keine Verknüpfung zur Datei "Bilanzarbeiten.xls".
Da bei dir die Monate in einer Zeile angeordnet sind, dürfte in der Formel für Januar/Jänner aber nicht ZEILE(D1) stehen. Es müsste dort dann SPALTE(A1) stehen.
Alternativ: In der Zeile 2 trägst du für jeden Monat den 1. des Monats als Datum ein und formatierst das Datum als "MMM". Dann kannst du in Zelle B3 =SPALTE(A1) ersetzen durch =MONAT(B$2).
Gruß
Franz
Anzeige
AW: Verknüpfung INDIREKT
02.07.2011 13:34:37
chris58
Hallo Franz !
Erstmal danke für deine Bemühung, Es funktioniert besstens. Habe allerding nicht ZEILE eingegeben, sondern habe das so gelassen und trotz allem funktioniert das ganze bestens. Meinst du, das es da zu Problemen kommen könnte ?
Ich habe heute alle Formeln in meine Liste eingegeben. Mußte bei jeder Eingabe in eine Zelle, das D1 (= sicher Monat)auf D2, D3 usw. ändern, darum melde ich mich jetzt erst. Jedenfalls nochmals danke für deine Hilfe. Eine Frage hätte ich noch: Was muß ich weglassen, wenn ich (bei einer zweiten Liste) keine Summe pro Monat haben will, sondern nur die reinen Datensätze verknüpfen will.
Jedenfalls danke nochmals, bin begeistert
Gruß
chris
Anzeige
AW: Verknüpfung mit VBA
03.07.2011 16:12:03
chris58
Hallo !
Ich habe diesen Code aus einem anderen generiert. Nun möchte ich aber nicht in die selbe Mappe kopieren, sondern von einer anderen Mappe (jedoch im gleichen Ordner liegend) kopieren und einfügen lassen.
Kann mir hier ein User helfen, wenn ich von der Mappe "Saldenliste sowie alle Buchungen" aus dem Tabellenblatt "Saldoliste" kopieren und einfügen will. Außerdem sollte der Code in die nächste freie Zeile ab "Zeile 3" kopieren.
Danke für die Hilfe
chris58
Private Sub CommandButton1_Click()
'Bedingtes Kopieren
EinfügeZeile = 3                                                       ' Ab dieser Zeile  _
weden die Daten eingefügt
TabellenBeginn = 3                                                   ' Ab hier beginnt die  _
Suche nach x
TabName = "Bilanzarbeiten"                                     ' In dieses Tabellenblatt  _
wird kopiert
TabName2 = "Saldenliste sowie alle Buchungen"     ' Aus diesem Tabellenblatt wird kopiert
Application.ScreenUpdating = False                          ' Bildschirmanzeige ausschalten
' Hier kann die Grösse der zu prüfenden Tabelle angegeben werden
Tabellengrösse = 4000
'Bedingung prüfen und kopieren
For Zeile = TabellenBeginn To Tabellengrösse
If Worksheets(TabName2).Cells(Zeile, 4) = "WEK" Or Worksheets(TabName2).Cells(Zeile,  _
4) = "WEK" Then
ZelleA = Worksheets(TabName2).Cells(Zeile, 1)
ZelleB = Worksheets(TabName2).Cells(Zeile, 2)
ZelleE = Worksheets(TabName2).Cells(Zeile, 5)
ZelleF = Worksheets(TabName2).Cells(Zeile, 6)
ZelleL = Worksheets(TabName2).Cells(Zeile, 12)
ZelleR = Worksheets(TabName2).Cells(Zeile, 18)
Worksheets(TabName).Cells(EinfügeZeile, 1) = ZelleA
Worksheets(TabName).Cells(EinfügeZeile, 2) = ZelleB
Worksheets(TabName).Cells(EinfügeZeile, 3) = ZelleE
Worksheets(TabName).Cells(EinfügeZeile, 4) = ZelleF
Worksheets(TabName).Cells(EinfügeZeile, 5) = ZelleL
Worksheets(TabName).Cells(EinfügeZeile, 6) = ZelleR
EinfügeZeile = EinfügeZeile + 1
End If
Next Zeile
Application.ScreenUpdating = True          'Bildschirmanzeige wieder einschalten
End Sub

Anzeige
AW: Verknüpfung mit VBA
03.07.2011 21:42:35
fcs
Hallo Chris,
falls du die Lösung für deine vorherige Frage noch nicht hast:
Zellwerte kannst du wie folgt per INDIREKT übernehmen.
=INDIREKT("'[Bilanzarbeiten.xls]Saldenliste'!Z"&ZEILE(B5)&"S"&SPALTE(B5);FALSCH)
oder
=INDIREKT("'[Bilanzarbeiten.xls]Saldenliste'!"&ZELLE("adresse";A5))
Bei deinem Makro muss du dann die Arbeitsmappen mit in die Objektadressierung einbauen und es muss geprüft werden ob die Datei mit der zu durchsuchenden Tabelle schon geöffnet ist. Zweckmäßiger Weise verwendet man hierzu entsprechende Objektvariablen. Die gefundenen Werte muss man nicht unbedingt in Variablen zwischenspeichern. Die Werte in der Quelltabelle kann man auch direkt in die Zieltabelle übertragen.
Gruß
Franz
Private Sub CommandButton1_Click()
'Deklaration der Variablen
Dim wbZiel As Workbook, wbQuelle As Workbook, sQuelldatei As String
Dim wksZiel As Worksheet, wksQuelle As Worksheet
Dim Zeile As Long, Einfuegezeile As Long, TabellenBeginn As Long, Tabellengroesse As Long
'Bedingtes Kopieren
On Error GoTo Fehler
'Zieldatei - in diese Datei wird kopiert
Set wbZiel = ActiveWorkbook 'oder = Thisworkbook
Einfuegezeile = 3            ' Ab dieser Zeile werden die Daten eingefügt
TabellenBeginn = 3          ' Ab hier beginnt die Suche nach x
Set wksZiel = wbZiel.Worksheets("Bilanzarbeiten") ' In dieses Tabellenblatt wird kopiert
'Nächste Einfügezeile in Zieltabelle
With wksZiel
Einfuegezeile = Application.WorksheetFunction.Max(Einfuegezeile, _
.Cells.SpecialCells(xlCellTypeLastCell).Row + 1)
End With
sQuelldatei = "Saldenliste sowie alle Buchungen.xls" 'Name der Quelldatei
'Prüfen ob Quelldatendatei schon geöffnet
For Each wbQuelle In Application.Workbooks
If UCase(wbQuelle.Name) = UCase(sQuelldatei) Then
'Prüfen, ob Quelldatei im gleichen Verzeichnis wie Zieldatei
If UCase(wbQuelle.FullName) = _
UCase(wbZiel.Path & Application.PathSeparator & sQuelldatei) Then
'Alles OK - Quelldatei ist geöffnet
Else
If MsgBox("Zur Zeit geöffnete Quelldatei """ & sQuelldatei _
& """ ist in einem anderen Verzeichnis gespeichert als die aktive Zieldatei." _
& vbLf & vbLf & "Die Quelldatei schliessen und korrekte Quelle öffnen?", _
vbQuestion + vbOKCancel, "Quelldatei - prüfen - öffnen") = vbOK Then
'Quelldatei schreibgeschützt öffnen
wbQuelle.Close
Set wbQuelle = Workbooks.Open( _
Filename:=wbZiel.Path & Application.PathSeparator & sQuelldatei, _
UpdateLinks:=False, ReadOnly:=True)
Else
GoTo Beenden
End If
End If
Exit For
End If
Next
If wbQuelle Is Nothing Then
'Quelldatei ist noch nicht geöffnet - Datei schreibgeschützt öffnen
Set wbQuelle = Workbooks.Open( _
Filename:=wbZiel.Path & Application.PathSeparator & sQuelldatei, _
UpdateLinks:=False, ReadOnly:=True)
End If
Set wksQuelle = wbQuelle.Worksheets("Saldoliste") ' Aus diesem Tabellenblatt wird kopiert
Application.ScreenUpdating = False              ' Bildschirmanzeige ausschalten
' Hier kann die Grösse der zu prüfenden Tabelle angegeben werden
With wksQuelle
'Letzte Zeile mit Daten in Spalte 4
Tabellengroesse = .Cells(.Rows.Count, 4).End(xlUp).Row
End With
'Bedingung prüfen und kopieren
For Zeile = TabellenBeginn To Tabellengroesse
If wksQuelle.Cells(Zeile, 4) = _
"WEK" Or wksQuelle.Cells(Zeile, 4) = "WEK" Then
wksZiel.Cells(Einfuegezeile, 1) = wksQuelle.Cells(Zeile, 1) 'Spalte A --> A
wksZiel.Cells(Einfuegezeile, 2) = wksQuelle.Cells(Zeile, 2) 'Spalte B --> B
wksZiel.Cells(Einfuegezeile, 3) = wksQuelle.Cells(Zeile, 5) 'Spalte E --> C
wksZiel.Cells(Einfuegezeile, 4) = wksQuelle.Cells(Zeile, 6) 'Spalte F --> D
wksZiel.Cells(Einfuegezeile, 5) = wksQuelle.Cells(Zeile, 12) 'Spalte L --> E
wksZiel.Cells(Einfuegezeile, 6) = wksQuelle.Cells(Zeile, 18) 'Spalte R --> F
Einfuegezeile = Einfuegezeile + 1
End If
Next Zeile
Fehler:
With Err
Select Case .Number
Case 0 ' Alles ist ok
Case Else
MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description
End Select
End With
Beenden:
Set wbZiel = Nothing: Set wbQuelle = Nothing
Set wksZiel = Nothing: Set wksQuelle = Nothing
Application.ScreenUpdating = True          'Bildschirmanzeige wieder einschalten
End Sub

Anzeige
AW: Verknüpfung mit VBA
03.07.2011 23:17:26
chris58
Hallo !
Ich habe deine Version eingebaut, doch da kommt der Fehler 9, Index außerhalb des gültigen Bereichs !
Naja, das wars dann, ich bringe meine Daten nicht von dieser Datei rüber.
Danke jedenfalls
chris58
AW: Verknüpfung mit VBA
04.07.2011 09:14:08
fcs
Hallo Chris,
der Fehler "Index außerhalb des gültigen Bereichs" deutet normalerweise darauf hin, dass ein Name in einer Auflistung nicht gefunden wird. Ich hatte mit den Namen getestet, die du in deiner Frage angegeben hattest. Da lief das Makro einwandfrei durch.
Prüfe also nochsmals die Namen der Tabelleblätter bei
Set wksQuelle = wbQuelle.Worksheets("Saldoliste")
und
Set wksZiel = wbZiel.Worksheets("Bilanzarbeiten")
Diese müssen natürlich genau so geschrieben werden wie in der jeweiligen Arbeitsmappe.
Deaktiviere vorübergehend die Zeile "On Error Goto Fehler"
Dann bricht das Makro in der Zeile ab, in der der Fehler auftritt.
Gruß
Franz Sielck
Anzeige
AW: Verknüpfung mit VBA
04.07.2011 13:01:18
chris58
Hallo !
Habe bei On Error Goto Fehler ein ´gesetzt und dann bricht der Code bei dieser Zeile ab.
Das ist das Tabellenblatt in der Mappe "Buchungen sowie alle Buchungen" aus dem die Daten geholt werden sollen. Muß ich da einen Pfad angeben ?
Hier die Fehlerzeile´(gelb unterlegt):
Set wksQuelle = wbQuelle.Worksheets("Saldoliste") ' Aus diesem Tabellenblatt wird kopiert
chris58
AW: Verknüpfung mit VBA
04.07.2011 22:58:37
fcs
Hallo Chris,
die Arbeitsmappe hat das Makro scheinbar korrekt gefunden, wenn es bis zu dieser Zeile kommt. Eine Pfadangabe ist hier nicht erforderlich. Das besorgt das vorangestellt Objekt "wbQuelle".
Prüfe also nochmals die genaue Schreibweise des Namens der Tabelle aus der die Daten kopiert werden sollen. Evtl. steht in der Arbeitsmappe hinter "Saldoliste" ja noch ein Leerzeichen.
Gruß
Franz
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige