suchen in verschiedenen Tabellenblättern

Bild

Betrifft: suchen in verschiedenen Tabellenblättern
von: stef26
Geschrieben am: 27.04.2015 23:13:37

Hallo liebe Excelprofis,
ich möchte mir gerne in meiner Arbeit das Leben etwas vereinfachen und bräuchte da eure Unterstützung.
Ich habe es in einer Beispieldatei versucht zu erklären, da es etwas schwierig ist dies zu erklären...
https://www.herber.de/bbs/user/97326.xlsx
Ich habe ein Tabellenblatt Aufträge. In der befindet sich die Gerätenummer.
Zu jeder Gerätenummer gibt es ein Tabellenblatt mit der Stückliste.
Mich interessieren nur die Materialnummern in der Tabelle "Suchnummern"
Diese sollen dann in der Ergebnisliste übertragen werden.
Ich hoffe ich konnte es einigermaßen erklären. Ich bring das alleine nicht auf die Reihe. Wäre sehr froh wenn ihr mir helfen könntet.
Gruß
Stefan

Bild

Betrifft: geht auch ohne VBA ...
von: der neopa C
Geschrieben am: 28.04.2015 08:42:43
Hallo Stefan,
... mit folgender Formel: =WENNFEHLER(SVERWEIS(C2;INDIREKT(B2&"!A:B");2;);"gibt es nicht")
Gruß Werner
.. , - ...

Bild

Betrifft: Nachfrage
von: stef26
Geschrieben am: 28.04.2015 09:26:34
Hallo Werner,
danke für die Info.
Habe mal versucht die Formel zu zerlegen, damit ich verstehe was die macht.
Bin aber nicht schlau daraus geworden.
Weiß nicht wie das funktionieren soll.
Ziel:
Durch die Tabelle Auftrag erhält man die Gerätenummer.
Über die Stückliste der jeweiligen Geräte Nummer
nur diese in der Zieltabelle auflisten, die in der Suchtabelle definiert wurden.
Nächsten Auftrag in der Auftragsliste mit gleicher Vorgehensweise bearbeiten...
Ich befürchte ohne VBA ist das nicht machbar...?
Gruß
Stefan

Bild

Betrifft: setze die Formel in Deiner Mappe ein ..
von: der neopa C
Geschrieben am: 28.04.2015 09:31:48
Hallo Stefan,
... ich erhalte damit in Deiner Beispielmappe die korrekten Ergebnisse. Du nicht?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: geht auch ohne VBA ...
von: stef26
Geschrieben am: 28.04.2015 12:24:01
Hallo Werner,
danke nochmal für dein Intresse und deine Unterstützung.
Das mit dem Verweis funktioniert wirklich super. Aber wie bekomme ich die Materialnummer in die
Ergebnisliste?
Die Ergebnisliste ist leer und soll irgendwie per Macro gefüllt werden. (Hab ich schlecht erklärt)
Hab ich dann die Materialnummern in der Liste, dann kann ich deine Formel verwenden.
Gruß
Stefan

Bild

Betrifft: das kannst nur Du wissen ...
von: der neopa C
Geschrieben am: 28.04.2015 14:20:06
Hallo Stefan,
... aus Deiner Beispielarbeitsmappe ist nur ersichtlich, dass diese schon vorhanden sein muss.
Gibt irgendwo noch eine Abhängigkeit wo die Materialnummer z.B. von der Gerätenummer definiert ist?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: das kannst nur Du wissen ...
von: stef26
Geschrieben am: 28.04.2015 15:25:12
Hallo Werner,
also es ist so :-)
Im Tabellenblatt Aufträge (lade ich mir täglich von einem Server in das Tabellenblatt)
Hier stehen zum Auftrag die Gerätenummern.
Die Gerätenummern wiederum haben Stücklisten die einmalig aus SAP in das XLS geladen warden.
Problem: Die Stücklisten sind in Wirklichkeit bis zu 2000 Zeilen groß. Ich wiederum brauche nur die
Materialnummern aus den Stücklisten, die ich in einem Tabellenblat Suchnummern eingetragen habe.
Start: Das Ergebnistabellenblatt ist leer.
Über das Tabellenblatt Auftrag(wo die Gerätenummer zu dem Auftrag steht) soll im Tabellnblatt der Gerätestückliste die Materialnummern aus der Gerätestückliste in das Tabellenblatt Ergebnis kopiert warden, welche für mich von Bedeutung(Tabellenblatt Suchnummern) sind.
Und so müsste die ganze Auftragstabelle durchgearbeitet warden und in die Ergebnisliste die für mich intressanten Nummern aus der Gerätestückliste übertragen warden...
Liebe Grüße
Stefan
Ich hoffe ich konnte es so erklären, dass es einigermaßen verständlich rübergekommen ist.

Bild

Betrifft: noch immer nicht eindeutig ...
von: der neopa C
Geschrieben am: 28.04.2015 15:43:48
Hallo Stefan,
... woher soll ich aus Deiner Beispielarbeitsmappe eine eindeutige Zuordnung der Suchnummern zu den Gerätenummern erkennen können?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: noch immer nicht eindeutig ...
von: stef26
Geschrieben am: 28.04.2015 18:43:56
Hallo Werner,
Zuordnung Geräte Nummer zu suchnummer gibt es nicht.
Die Geräte Nummer ist zum Beispiel ein Radio. Das Radio hat eine Stückliste.
Die Suchnummern sind Bauteile für dieses Radio.
D.h, ich möchte nicht die Komplette Stückliste sehen, sondern nur diese, die ich unter Suchergebnisse definiert habe.
In den Suchergebnissen stehen alle Bauteile für alle Geräte für die ich zuständig bin.
Gruss
Stefan

Bild

Betrifft: und wie ordnest Du da bis dato zu ....
von: der neopa C
Geschrieben am: 29.04.2015 07:57:41
Hallo Stefan,
... in Deiner Beispielarbeitsmappe erkenne ich damit momentan keine "automatisiert" Zuordnungsmöglichkeit einer Materialnummer zur Gerätenummer. Da bliebe dann nur noch eine willkürliche/händische.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: ist nicht so kompliziert wie es aussieht
von: stef26
Geschrieben am: 29.04.2015 11:32:54
Hallo Werner,
ich hoffe das nervt dich nicht zu sehr... :-)
Es ist eigentlich ganz einfach.
Ich hab mehrere Aufträge (Produkte).
Zu jedem Produkt gibt es eine Stückliste mit Materialnummern, mit denen das Produkt entsteht.
Ich bin aber nicht für alle Materialnummern der ganzen Produkte die wir haben zuständig.
Ich bin nur für die Materialnummern in der Suchliste zuständig.
Ich möchte lediglich in einer Ergebnisliste meine Materialnummern zu den ganzen Aufträgen aufgelistet haben.
Gruß
Stefan

Bild

Betrifft: wir schreiben da wohl aneinander etwas vorbei ...
von: der neopa C
Geschrieben am: 29.04.2015 12:06:57
Hallo Stefan,
... sieh Dir Deine hier eingestellte Arbeitsmappe noch einmal an.
Wie/woher soll denn Zuordnung der Materialnummer zur der jeweiligen Auftragsnummer oder Gerätenummer (dazu hast Du allerdings schon geschrieben, das es da keine Zuordnung gäbe) in Deine "Ergebnis"-Tabelle kommen? Wenn das für Dich klar ist, dann zeig es mir konkret - in einer neuen Arbeitsmappe - so auf, dass auch ich es verstehe. Dann kann ich Dir möglicherweise auch einen Lösungsweg zeigen.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: wir schreiben da wohl aneinander etwas vorbei ...
von: stef26
Geschrieben am: 29.04.2015 15:56:53
Hallo Werner,
ok nochmal mit einer Vereinfachten Liste.
Ich soll einen Fernseher bauen.
Schaue in die Stückliste Fernseher und trage die Materialnummern in die Ergebnisliste, wenn
diese in meiner Zuständigkeit liegen.
https://www.herber.de/bbs/user/97364.xlsx
Gruß
Stefan

Bild

Betrifft: mit 5 Formeln und einer 0 lösbar ...
von: der neopa C
Geschrieben am: 29.04.2015 18:05:28
Hallo Stefan,
... in meinen Lösungsformeln hab ich momentan folgende Grenzwerte angenommen: 99 Materialnummern für die Du zuständig bist; 333 Materialnummer je "Gerät" und 99 Aufträge. Durch entsprechende Anpassung der Formeln kannst Du die Grenzwerte einfach ändern (aber nur auf das wirklich amx. notwendige)
In einer (ausblendbaren) Hilfsspalte (hier Spalte E gewählt) in der Tabelle "Aufträge" werden die Anzahl der von Dir zu bearbeitenden Materialien durch die in E1 einzugebende 0 und die Formel E2 (99 zeilen nach unten kopieren) ermittelt. Auf dieser Basis füllt sich mittels Formeln (momentan max. 99 Zeilen nach unten kopieren) zunächst die Auftragsnummer und die Gerätebezeichnungen und dann werden für diese beiden ermittelten Daten mit der Formel C2 alle zugeordneten Materialnummern ermittelt. Und dafür dann mit D2 das Abrufdatum.
Ergebisliste

 ABCD
1AuftragGerätMaterialnummerAbruf
2AFernseher322.04.2015
3AFernseher521.04.2015
4BComputer522.04.2015
5BComputer916.04.2015
6    

Formeln der Tabelle
ZelleFormel
A2=WENN(ZEILE(A1)>MAX(Aufträge!E:E); "";INDEX(Aufträge!A:A;VERGLEICH(ZEILE(A1)-1;Aufträge!E:E)+1))
B2=WENN(A2="";"";SVERWEIS(A2;Aufträge!A:B;2;))
C2=WENN(B2="";"";INDEX(INDIREKT(B2&"!A1:A333"); AGGREGAT(15;6;ZEILE(A$1:A$99)/ISTZAHL(VERGLEICH(INDIREKT(B2&"!A1:A333"); MNrn;)); ZÄHLENWENN(B$2:B2;B2))))
D2=WENN(C2="";"";ARBEITSTAG(VERWEIS(9;1/(Aufträge!A$1:A$99=A2)/(Aufträge!B$1:B$99=B2); Aufträge!C:C); -SVERWEIS(C2;'Meine Zuständigkeit'!A:B;2;)))
Namen in Formeln
ZelleNameBezieht sich auf
C2MNrn='Meine Zuständigkeit'!$A$2:INDEX('Meine Zuständigkeit'!$A:$A;ANZAHL2('Meine Zuständigkeit'!$A:$A))
Namen verstehen


Aufträge

 ABCDE
1AuftragGerät Lieferdatum 0
2AFernseher29.04.2015 2
3CTelefon30.04.2015 2
4BComputer30.04.2015 4
5     

Formeln der Tabelle
ZelleFormel
E2=WENN(B2="";"";E1+ANZAHL(INDEX(AGGREGAT(15;6;VERGLEICH(MNrn;INDIREKT(B2&"!A:A"); )*ZEILE(A$1:A$99); ZEILE(A$1:$A$99)); )))
Namen in Formeln
ZelleNameBezieht sich auf
E2MNrn='Meine Zuständigkeit'!$A$2:INDEX('Meine Zuständigkeit'!$A:$A;ANZAHL2('Meine Zuständigkeit'!$A:$A))
Namen verstehen


Meine Zuständigkeit

 AB
1MaterialnummerMinus Abruf Tage
235
356
4910
5  

Gruß Werner
.. , - ...

Bild

Betrifft: WOW !!!
von: stef26
Geschrieben am: 29.04.2015 21:51:46
Hallo Werner,
wow. Super gelöst. Versuche nun die Formeln zu verstehen. (das wird dauern )
Vielen Dank
Und nochmal sorry für die Verwirrung
Liebe Grüße
Stefan

 Bild

Beiträge aus den Excel-Beispielen zum Thema "suchen in verschiedenen Tabellenblättern"