733 Aufrufe
Gefragt in Tabellenkalkulation von
Guten Tag!

Ich habe ein Excel-Registerblatt, das mittels Aggregat-Formel auf ein zweites Registerblatt zugreift. Sprich: In dem zweiten Registerblatt habe ich Stammdaten, wenn ich dort ein "x" ausfülle, werden die Daten in das erste Registerblatt eingetragen. Weitere Daten werden mittels Sverweis in das erste Blatt vom zweiten übernommen. Also die Spalten A-G werden automatisch befüllt.

Ich habe nun folgendes Problem: in meinem ersten Registerblatt werden also automatisch Zeilen eingefügt, sobald ich im zweiten ein "x" mache. Es gibt in dem ersten Blatt aber auch Zuständigkeiten (Spalten H-I), die ich direkt im ersten Blatt mittels Dropdown eintrage - diese verschieben sich aber nicht mit, wenn eine neue Zeile über die Aggregat-Formel eingefügt wird. Die Inhalte werden dadurch falsch.

Kann mir irgendwer einen Tipp geben, wie das zu lösen ist?

Danke im Voraus!

Grüße, Anton

24 Antworten

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Anton,

verschieben sich die Spalten H bis I nicht? Wie werden die neuen Zeilen eingefügt?

Gruß

M.O.
0 Punkte
Beantwortet von
Hallo M.O.,

danke für die Rückmeldung. Also die Spalten A-G werden über die Aggregats- bzw. sverweis-Formeln befüllt (die Formeln stehen da schon drin), die anderen Spalten sind leer und werden mittels Dropdown befüllt.

Also werden die Zeilen nicht verschoben, sondern nur  befüllt.

Kann ich das evtl. mit einer Hilfsspalte lösen, oder einem Makro, sodass die Daten, die direkt im zweiten Blatt manuell (bzw. mittels Dropdown) eingefügt werden, dann in derselben Zeile, wie sie waren, stehen bleiben?

Danke!

Lg. Anton
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Anton,

wenn die Spalten mittels Formel befüllt werden, ist es klar, dass die Daten, die du per Hand eingibst sich nicht ändern. Ob dir eine Hilfsspalte wage ich zu bezweifeln, obwohl ich den Aufbau deiner Datei nicht kenne.

Entweder du fügst neue Datensätze nur am Ende des Eingabeblattes ein. oder du löst das Einfügen über ein Makro.

Falls du das mit einem Makro lösen willst, dann könntest du mal eine Beispieldatei mit ein paar Dummydaten zur Verfügung stellen. Wie das funktioniert kannst du hier nachlesen: Klick mich!

Gruß

M.O.

0 Punkte
Beantwortet von

Hallo M.O.,

ich bedanke mich schon im voraus für Deine Hilfe.

Hier eine Musterdatei.

Es gibt darin schon Makros, für eine automatische Gliederung (da die Originaldatei fast 2000 Datenzeilen hat, und sich bei meiner Version mit der "Formelkopierfunktion" die Gliederung immer ändern würde). Das Passwort ist immer leer (also keines, wenn der Blattschutz aufgehoben wird). Die Datenzeilen (also jene, die die Daten mittels Aggregat oder Sverweis aus der Datentabelle holen) sollen nämlich schreibgeschützt sein. 

Es wäre super, wenn Du mir helfen könntest.

Grüße,

Anton

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Anton,

ich schaue mir das mal an.

Gruß

M.O.
0 Punkte
Beantwortet von
Hallo M.O.,

herzlichen Dank schon mal für Deine Bemühungen!

Gruß,

Anton
0 Punkte
Beantwortet von
Hallo M.O.,

darf ich Dich noch etwas fragen? Wenn ich z.B. die restlichen Daten auch in die Datentabelle übernehme und dann mittels sverweis in die Ergebnistabelle schreiben lasse, kann ich dann auch mittels Formel oder ähnlichem das so machen, dass, wenn ich in der Ergebnisliste einen anderen Wert habe, dieser in der Datentabelle überschrieben wird?

Das wäre evtl. auch eine Lösung ...

Danke nochmals für Deine Hilfe!

Gruß,

Anton
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Anton,

wenn du auch diese über SVERWEIS in die beiden Tabellenblätter übernehmen willst, dann ändern sich diese Daten natürlich auch, wenn sich der Suchbegriff ändert. Das ist dann wie deine anderen Daten in den Spalten C bis F.

Gruß

M.O.
0 Punkte
Beantwortet von
Hallo M.O.,

danke für Deine Antwort.

Ich möchte aber gerne wissen, ob es möglich ist, wenn ich die sverweis-Formel in den Ergebnistabellen ändere, dass sich die Daten dann automatisch in der Datentabelle aktualisieren. zB., wenn jemand in der falschen Tabelle arbeitet und mittels Dropdown Daten auswählt, dass diese dann in der Datentabelle auch dieselben sind.

Einen sverweis auf einen anderen kann ich ja nicht machen, wegen Zirkelbezug, soweit ich das verstehe.

Ich hoffe, ich habe mich verständlich ausgedrückt.

Gruß,

Anton
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Anton,

du kannst entweder einen Wert in eine Zelle schreiben oder eine Formel. Steht in einer Zelle ein Wert, dann kannst du diesen Wert nicht mit einer Formel überschreiben. Das Verbessern von eingegebenen Werten kannst du nur über ein Makro machen.

Zu deinem Problem mit den sich nicht verschiebenden Einngaben habe ich mal etwas gebastelt.

Ergänze im VBA-Projekt der Arbeitsmappe die folgenden Zeilen:

'Makro zum Einlesen der Daten wird aufgerufen
Call einlesen

In das VBA-Projekt der Tabelle "DATEN" kopiere den folgenden Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngSpalte As Long

If Not Intersect(Target, Range("R:S")) Is Nothing Then
   lngSpalte = Target.Column
   Call Abgleich(lngSpalte)
End If

End Sub


In ein Standardmodul deiner Arbeitsmappe kopiere den folgenden Code:

Global arrDatenA As Variant
Global arrDatenB As Variant
Global strTabelleA As String
Global strTabelleB As String

Sub einlesen()
Dim lngLetzte As Long
Dim z As Long

'Namen der Ergebnistabellen festlegen
strTabelleA = "Ergebnisliste A"
strTabelleB = "Ergebnisliste B"

'Daten aus Ergebnisliste A einlesen, erst ab Zeile 10
With ThisWorkbook.Worksheets(strTabelleA)
 'Blattschutz aufheben
 .Unprotect
   'letzte Zelle mit einem Eintrag in Spalte C suchen
   For z = 10 To .Cells(Rows.Count, 3).End(xlUp).Row
     If .Cells(z, 3).Value = "" Then
       lngLetzte = z - 1
       Exit For
     End If
   Next z
 'Daten in Array einlesen
 arrDatenA = .Range(.Cells(10, 3), .Cells(lngLetzte, 31))
'Blattschutz wieder setzen
 .Protect
End With

'Daten aus Ergebnisliste b einlesen, erst ab Zeile 10
With ThisWorkbook.Worksheets(strTabelleB)
 .Unprotect
 'letzte Zelle mit einem Eintrag in Spalte C suchen
   For z = 10 To .Cells(Rows.Count, 3).End(xlUp).Row
     If .Cells(z, 3).Value = "" Then
       lngLetzte = z - 1
       Exit For
     End If
   Next z
 'Daten in Array einlesen
 arrDatenB = .Range(.Cells(10, 3), .Cells(lngLetzte, 31))
 .Protect
End With

End Sub

Sub Abgleich(lngSpalte As Long)
Dim strName As String
Dim arrDaten As Variant
Dim lngLetzte As Long
Dim d As Long
Dim s As Long
Dim z As Long

'Eingabe in Spalte R, dann aus Ergebnisliste B die Daten auslesen
If lngSpalte = 18 Then
  strName = strTabelleB
  arrDaten = arrDatenB
 Else
  'sonst Ergebnisliste A auswählen
  strName = strTabelleA
  arrDaten = arrDatenA
End If
 
'Nun Spalten G bis U vergleichen und Werte übernehmen
With Worksheets(strName)
  'Blattschutz aufheben
  .Unprotect
  'letzte Zelle mit einem Eintrag in Spalte C suchen
   For z = 10 To .Cells(Rows.Count, 3).End(xlUp).Row
     If .Cells(z, 3).Value = "" Then
       lngLetzte = z - 1
       Exit For
     End If
   Next z
 
   'vorhande Inhalte der Spalten G bis U löschen
  .Range(.Cells(10, 7), .Cells(.UsedRange.SpecialCells(xlCellTypeLastCell).Row, 21)).ClearContents
  'nun vorhandene Daten zurückschreiben
  For z = 10 To lngLetzte
     'Abgleich der Nummern
    For d = 1 To UBound(arrDaten, 1)
     If .Cells(z, 3).Value = arrDaten(d, 1) Then
        For s = 5 To 19
          .Cells(z, s + 2) = arrDaten(d, s)
        Next s
        Exit For
     End If
    Next d
   Next z
  'Blattschutz wieder aktivieren
  .Protect
End With

'Datenblätter wieder neu einlesen
Call einlesen

End Sub


Probiere mal einer Kopie deiner Mappe aus, ob der Code so funktioniert, wie du willst.

Gruß

M.O.

...