Office: Zeilenreihe ausfüllen wenn Wert in Quelle vorhanden

Hallo zusammen
Ich habe ein Blatt 'All Items' das ich als Quelle nutzen will. wenn in diesem Blatt beginnend ab F5-F2000 eine Menge eingegeben wird, dann soll im Blatt 'Order' in der Spalte A ab A5 die Werte aus Blatt 'All Items' ab A5 übernommen werden. Das habe ich mit folgender Formel geschafft, jedoch werden Zeilen eins zu eins übernommen und ich möchte im Blatt 'Order' die leeren Zeilen überspringen und in der folgenden Zeile erst den nächsten Wert befüllen.

Formel im Blatt 'All Items' Zelle A5, dann runtergezogen und Reihenfolge ausgefüllt
=WENN('All Items'!F5="";"";'All Items'!A5)

Quellblatt 'All Items'


Zielblatt 'Order' / diese 3 Werte sollen in Zellen A5-A7 aufgelistet werden


Vielen Dank und Grüsse

 

Du bist im falschen Forum. Bildbearbeitung ist ein anderes.
Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.

Ich baue keine Datei nach, die Zeit hat schon jemand investiert.

Bilder statt Datei | Excel ist sexy!
Eine hochgeladene Arbeitsmappe erhöht die Wahrscheinlichkeit, dass Du eine Lösung für Dein Problem erhältst.
Erstelle folglich bitte eine Demomappe, aus der deine Aufgabenstellung klar erkennbar ist und lade diese Mappe mit einem aussagekräftigen Dateinamen hoch.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender*, UserForm*, Forum* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten ändern. Schaue Datei
http://hajo-excel.de/gepackt/fremd/Datei_verschluesseln.zip
Falls Du den Download des Forums nicht benutzen möchtest, beachte bitte: von unsicheren Servern file-upload lade ich keine Datei herunter (lt. Einschätzung meines Virenprogramms)

Das ist nur meine Meinung zu dem Thema.

 

Moin marko-mare

Wenn du ein Subset deiner Daten sehen möchtest dann verwende den Filter.
Ansonsten erkläre warum Daten dupliziert werden sollten.

 

Hallo,

da du nirgends eine Angabe zu deiner Excel-Version gemacht hast, nehme ich an, dass du eine aktuelle Version (Excel365 bzw. Excel2019) hast.

Ich habe dir in der beiliegenden Datei 2 Arbeitsblätter eingefügt:
Arbeitsblatt "All Items" enthält die strukturierte Tabelle "TabItems" mit den Quelldaten.

Arbeitsblatt "Order" enthält die strukturierte Tabelle "TabOrders" (ab Zeile 4): Diese wurde mittels einer PowerQuery-Abfrage erstellt. Ändern sich die Daten in Tabelle "TabItems", muss die Tabelle "TabOrders" explizit aktualisiert werden über ihr Kontextmenü mit dem Befehl "Aktualisieren".

Alternativ habe ich im Arbeitsblatt "Order" eine Formellösung (ab Zeile 20) eingefügt. Diese verwendet die 2 Formeln:
Zelle Order!A20: =TabItems[#Kopfzeilen]
Zelle Order!A21: =FILTER(TabItems;NICHT(ISTLEER(TabItems[Ordering Qty])))

 

Hallo zusammen
Vielen Dank für die schnellen Rückmeldungen und Lösungsvorschläge.
@Exl121150 die Lösung mit der PowerQuery-Abfrage scheint mir am besten zu sein, jedoch bräuchte es noch eine kleine Überarbeitung, sowie ein Makro für die Aktualisierung. Wenn es dir möglich wäre, habe ich nun ein Demo-File "Bestell-Formular" mit provisorisch 10 Positionen hochgeladen und bräuchte folgende Anpassung:
- Es handelt sich um ein Bestellformular
- Arbeitsblatt "All Items" ist die Quelle, ich werde alle Zellen, bis auf Spalte 'F' "Ordering Qty" sperren, sodass die Kunden nur die Bestellmenge angeben können. Die Mengen sind jedoch begrenzt (höchstens verfügbare Menge aus Spalte 'I'), das habe ich mittels Datenüberprüfung bereits gesetzt
- Arbeitsblatt "Order" enthält weniger Spalten als Arbeitsblatt "All Items" und in diesem Blatt sollen nur die bestellten Positionen aufgelistet/zusammengefasst werden. Da werde ich ebenfalls das ganze Blatt sperren, bis auf Zelle 'D2'
- Der Befehl "Aktualisieren" im Arbeitsblatt "Order" soll mit einem Makro ausgeführt werden, der mit dem Anklicken des Textfeldes "Submit Order" im Arbeitsblatt "All Items" ausgeführt wird. Beim Ausführen vom Makro soll auch ins Arbeitsblatt "Order" rüber gesprungen werden.
Nochmals vielen Dank und Grüsse
marko-mare

 

Hallo,

in der beiliegenden Datei habe ich deine Vorgaben eingebaut und beide Arbeitsblätter gesperrt (ohne Passwörter): Das Arbeitsblatt "All Items" kann händisch gesperrt werden, während das Blatt "Order" im Aktualisierungsmakro entsperrt und am Schluss wieder gesperrt wird.

Die Schaltfläche "Submit Order" habe ich mit dem Aktualisierungsmakro verknüpft.

 

Hallo, sieht super aus, jedoch habe ich einen Debug-Fehler, wenn ich eine weitere Position bestelle und Makro ausführe:
"Der 'Microsoft.Mashup.OleDb.1'-Provider ist nicht auf dem lokalen Computer registriert."
Da ich die Datei verschiedenen Kunden zuschicken muss, werden einige wahrscheinlich das gleiche Problem haben.
Zum Debuggen ist folgender Befehl im Makro markiert:
".ListObjects("TabOrder").TableObject.Refresh"

Danke nochmals

 

Hallo,

offenbar hat diese "Fehlermeldung" eine ganze Reihe anderer User geärgert/genervt - meist wird berichtet, dass das Ganze unterbrechungsfrei funktioniert hat und ab einem gewissen Zeitpunkt dieser Fehler in unregelmäßigen Abständen zuschlägt - man gewinnt irgendwie/fast den Eindruck, dass ein Synchronisationsproblem vorliegt, das akut wird, sobald irgendwelche Windows-Konstellationen eintreten.
Es wird stets berichtet, dass bei händischer Durchführung der Abfrageaktualisierung dieser Fehler nie auftritt - er also nur auftritt, wenn man das Refreshing per VBA-Makro durchführen lässt.
Zwei User im folgenden Hilfeanfrage-Link wollen ein Rezept dagegen gefunden haben, das ich dir nicht vorenthalten will, da es noch dazu leicht umzusetzen ist - hoffentlich hilft es. Ich konnte die Wirksamkeit nicht überprüfen, weil bei mir dieses Problem bisher nicht auftrat.

vba microsoft.mashup.oledb.1 provider is not registered on the local machine - Microsoft Tech Community
Prozedere:
1) Aktive Zelle in den Abfragebereich der Tabelle "TabOrder" im Arbeitsblatt "Order" stellen, also zB. in Order!A5
2) Natürlich den Blattschutz für das Arbeitsblatt "Order" aufheben
3) Im Menüband > Tabulator "Daten" > Gruppe "Abfragen und Verbindungen" > Im DropDown-Button "Alle aktualisieren" den Befehl "Verbindungseigenschaften..." anklicken, sodass das Dialogfenster "Abfrageeigenschaften" erscheint.
4) Dort im Tabulator "Verwendung" das Häkchen aus der Checkbox "Bei 'Alle aktualisieren' diese Verbindung aktualisieren" entfernen.
5) Dafür jedoch ein Häkchen in der Checkbox "Schnelles Laden von Daten aktivieren" setzen.
6) Mit "OK"-Button bestätigen.

In der beiliegenden Excel-Datei habe ich das Häkchen, wie in der Grafik zu sehen, bereits so eingestellt.

 

@Exl121150 diese Massnahme hat geholfen, nun funktioniert es einwandfrei und ich kann nun mein Bestellformular fertigstellen.
Vielen Dank für die schnelle und effektive Hilfe.
Viele Grüsse

 

@Exl121150 Sorry der erneuten Störung, mir ist aber ein weiterer Bug aufgefallen, nämlich wenn ich beide Blätter mit Passwort schütze und dann die Bestellung zusammenstelle, beim Ausführen vom Makro wird das Passwort verlangt. Dies ist problematisch, denn das Passwort kann ich den Kunden nicht weitergeben. Das Problem habe ich nicht, wenn das Blatt 'Order' ohne Passwort geschützt ist, aber dann könnten die Kunden den Blattschutz aufheben und Änderungen vornehmen, was nicht vorkommen dürfte.
Somit bräuchte ich die Lösung, wo ich beide Blätter mit Passwort schützen kann, und dann beim Ausführen vom Makro zwischen den Beiden Blätter nicht nach dem Passwort gefragt wird. Bitte beachten, dass ich bei der Abfrage "Schnelles Laden von Daten" aktiviert habe, um das vorherige Problem zu lösen.
Vielen Dank und Viele Grüsse

 

Hallo,

du kannst ja bei der Unprotect / Protect - Methode des Arbeitsblattes den Password-Parameter mit übergeben. Dann fragt Excel nicht nach dem Passwort. Allerdings steht dann das Passwort im Klartext im VBA-Makro. Z.B.:

Code:

Public Sub TabOrderAktualisieren()
  
   With ActiveWorkbook.Worksheets("Order")
    
      .Unprotect Password:="MeinOrderPasswort"
    
      'Abfrage TabOrder aktualisieren
      .ListObjects("TabOrder").TableObject.Refresh
    
      .Activate
      'TabOrder formatieren
      .Columns("B:D").EntireColumn.AutoFit
      .Rows("4:4").RowHeight = 33.75
      .Columns("E:E").ColumnWidth = 9.43
      .Columns("F:F").ColumnWidth = 10.57
      .Columns("H:H").ColumnWidth = 8.57
    
      .Protect Password:="MeinOrderPasswort" ', .....
    
      'Springe nach Order!B2
      .Range("B2").Select
   End With
  
End Sub

Der Blattschutz (alleine) ist nicht als Sicherheitsfeature gedacht von Microsoft, sagt nämlich Microsoft selber.
Die weiteren Schutzmaßnahmen, die es in Excel gibt, kannst du in folgendem Microsoft-Link nachlesen:
Schutz und Sicherheit in Excel - Office-Support (microsoft.com)