Formel für eine Abfrage über mehrere Tabellenblätter

[Egon12]

die Formel erweitert bis zu letzten gefüllten Zelle in A derzu durchsuchenden Datensätze:

Code:

=LET(
  xLa; MAX(FILTER(ZEILE(Hausmann!A4:A1000); Hausmann!A4:A1000<>""));
  xLb; MAX(FILTER(ZEILE(Großhandel!A4:A1000); Großhandel!A4:A1000<>""));
  xLc; MAX(FILTER(ZEILE(Einzelhandel!A4:A1000); Einzelhandel!A4:A1000<>""));
  xBa; BEREICH.VERSCHIEBEN(Hausmann!A4; 0; 0; xLa - 3; 1);
  xAfk; BEREICH.VERSCHIEBEN(Hausmann!F4; 0; 0; xLa - 3; SPALTEN(Hausmann!F4:K4));
  xBb; BEREICH.VERSCHIEBEN(Großhandel!A4; 0; 0; xLb - 3; 1);
  xBfk; BEREICH.VERSCHIEBEN(Großhandel!F4; 0; 0; xLb - 3; SPALTEN(Großhandel!F4:K4));
  xBc; BEREICH.VERSCHIEBEN(Einzelhandel!A4; 0; 0; xLc - 3; 1);
  xCfk; BEREICH.VERSCHIEBEN(Einzelhandel!F4; 0; 0; xLc - 3; SPALTEN(Einzelhandel!F4:K4));
  xA; FILTER(xAfk; (xBa=A3) * (MMULT(--(xAfk<>""); MTRANS(SPALTE(Hausmann!F4:K4)^0))>0); "");
  xB; FILTER(xBfk; (xBb=A3) * (MMULT(--(xBfk<>""); MTRANS(SPALTE(Großhandel!F4:K4)^0))>0); "");
  xC; FILTER(xCfk; (xBc=A3) * (MMULT(--(xCfk<>""); MTRANS(SPALTE(Einzelhandel!F4:K4)^0))>0); "");
  VSTAPELN(WENNS(INDEX(xA;1;1)<>""; xA;INDEX(xB;1;1)<>""; xB;INDEX(xC;1;1)<>""; xC)))

Gruß Uwe

[maninweb]

Hallo,

Du hast recht, ich habe mich leider um eine Position vertan. Es muss nicht SEQUENZ(1; 6; 6) sondern SEQUENZ(1; 6; 7) sein.

Meine Formel hat sich zudem bei der Suche nur nach dem Kunden orientiert. Da habe ich Dich wohl falsch verstanden,
und es soll auch die Angebotsnummer berücksichtigt werden - sprich die zuerst hinterlegte gefunden werden.
Dann sieht die Formel wie folgt aus...

Code:

F3=LET(vnKunde; A3;
       vnQuelle; {"Hausmann";"Großhandel";"Einzelhandel"};
       vnHausmann; Hausmann!$A$4:$K$13;
       vnGrosshandel; Großhandel!$A$4:$K$13;
       vnEinzelhandel; Einzelhandel!$A$4:$K$13;
       vnSuchmatrix; VSTAPELN(HSTAPELN(SEQUENZ(ZEILEN(vnHausmann); 1; 1; 0); vnHausmann);
                              HSTAPELN(SEQUENZ(ZEILEN(vnGrosshandel); 1; 2; 0); vnGrosshandel);
                              HSTAPELN(SEQUENZ(ZEILEN(vnEinzelhandel); 1; 3; 0); vnEinzelhandel));
       vnKundensuche; FILTER(vnSuchmatrix; INDEX(vnSuchmatrix; 0; 2) = vnKunde; 0);
       vnAngebotssuche; WENN(INDEX(vnKundensuche; 1; 1) <> 0; FILTER(vnKundensuche; INDEX(vnKundensuche; 0; 7) <> ""; 0); 0);
       vnErgebniszeile; WENN(INDEX(vnAngebotssuche; 1; 1) <> 0; HSTAPELN(INDEX(vnAngebotssuche; 1; SEQUENZ(1; 6; 7)); INDEX(vnQuelle; INDEX(vnAngebotssuche; 1; 1); 1)); "");
       WENN(LÄNGE(vnErgebniszeile) > 0; vnErgebniszeile; ""))

Gruß

[anewelle]

Hi Uwe!
Die Formel funktioniert perfekt. Aber ich habe keine Ahnung was du da tolles geschrieben hast 

Viele Grüße
Anne

[Egon12]

Hallo Anne,

da dies wohl funktioniert habe ich mal noch die Ausgabe des Tabellenblattes, wo der Datensatz gefunden wurde eingebaut.

Überschaubarer für deine Zwecke ausreichender vereinfachter Lösungsweg (ich hatte das gestern Abend viel zu kompliziert angegangen), welcher nun auch das runterziehen sicherstellt:

Code:

=LET(
  xA;FILTER(Hausmann!$F$4:$K$1000;Hausmann!$A$4:$A$1000=A5;"");
  xB;FILTER(Großhandel!$F$4:$K$1000;Großhandel!$A$4:$A$1000=A5;"");
  xC;FILTER(Einzelhandel!$F$4:$K$1000;Einzelhandel!$A$4:$A$1000=A5;"");
xD;WENNS(INDEX(xA;1;1)<>""; xA;INDEX(xB;1;1)<>""; xB;INDEX(xC;1;1)<>""; xC);
xE;WENNS(INDEX(xA;1;1)<>"";"Hausmann";INDEX(xB;1;1)<>""; "Großhandel";INDEX(xC;1;1)<>""; "Einzelhandel");
HSTAPELN(xD;xE))

Alle mit x benannten Elemente der Formel sind Variablen in der LET() Funktion, welche entweder mit Arrays oder mit Werten gefüllt werden. 

Mit der Funktion FILTER() wird nach der passender Kundennummer gesucht und ein Trefferarray in die betreffende Variable übergeben. 

Die WENNS() Abfrage prüft ob in den jeweiligen Arrayzellen Zellen der Spalte 1 ein Wert enthalten ist. Damit werden Treffer ohne Wert ausgeschlossen. 

HSTAPELN() setzt in der Zeile die beiden Variablen hintereinander (stapelt Horizontal). 

Gruß Uwe