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

[anewelle]

Hallo!!!

 Ich brauche eine Formel, die auf 3 verschiedenen Blättern sucht, wo ein Info zu dem Kunden steht. Diese soll er dann ziehen. Er soll immer zuerst auf dem Blatt Hausmann suchen. Dann Großhandel, dann Einzelhandel.

In der Spalte L soll er schreiben, von wo er die Kosten für das Angeobt hat, also von welchem Tabellenblatt.

Ich nutze ganz gerne den Xverweis für Formeln. Aber irgendwie komme ich hier nicht weiter, weil ich mit Sicherheit noch )wenn oder )und benötige.

Für eure Hilfe wäre ich sehr dankbar! 

Liebe Grüße
Anne

[maninweb]

Hallo,

anbei eine mögliche Formel, die ein modernes Excel voraussetzt. Den Bereich F3:L12 zunächst leeren.
Dann in Zelle F3 einfügen und anschließend runterziehen bis F12.

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));
       vnSuche; INDEX(FILTER(vnSuchmatrix; INDEX(vnSuchmatrix; 0; 2) = vnKunde; 0); 1; 0);
       vnErgebniszeile; WENN(INDEX(vnSuche; 1; 1) <> 0; HSTAPELN(INDEX(vnSuche; 1; SEQUENZ(1; 6; 6)); INDEX(vnQuelle; INDEX(vnSuche; 1; 1); 1)); "");
       WENN(LÄNGE(vnErgebniszeile) > 0; vnErgebniszeile; ""))

Die Formel fügt den Bereichen aus jedem Blatt einen Index hinzu, stapelt dann die drei Bereiche untereinander,
und filtert nach Kunde. Dann wird geprüft, ob der Filter was ergeben hat, wenn ja, wird ein Bereich ab Spalte 6
extrahiert (ab AngebotsNr.) und hinten die Quelle drangehängt.

Gruß

[rate mit]

Hallo,

hier ein Vorschlag für ältere Versionen....

[anewelle]

Hi,

vielen lieben Dank für deine Mühe. Allerdings funktioniert das anscheinend nur, wenn immer in den gleichen Zellen auf allen Blättern z.B. die gleiche Kundennummer steht. D.h. ändere ich z.B. in der Übersicht die Nummer 1008 in 1000 funktioniert es leider nicht mehr.

Liebe Grüße
Anne

---

---

Hi!
Also vielen lieben Dank!!! Die Formel funktioniert bei mir nicht. Wenn ich die Formel in F3 reinkopiere, kommt dort die Stadt noch einmal - nicht die Angebotsnummer. Alles ist etwas verschoben. Auch funktioniert nicht der Hinweis, von wo er die Daten gezogen hat (Quelle). Und er sucht auch nur auf der ersten Seite. Die Blätter Großhandel und Einzelhandel durchsucht er nicht. 
Liebe Grüße
Anne

[rate mit]

Allerdings funktioniert das anscheinend nur, wenn immer in den gleichen Zellen auf allen Blättern z.B. die gleiche Kundennummer steht. D.h. ändere ich z.B. in der Übersicht die Nummer 1008 in 1000 funktioniert es leider nicht mehr.

.... das war ja auch nie Bestandteil der Aufgabe.... um das dann ab zu kürzen.... welche Bedingungen müssen denn noch berücksichtigt werden???

[anewelle]

Hallo,

also in jedem Tabellenblatt können alle Kundennummern vorkommen. Durcheinander. Kundennummer kann in der Übersicht in Zelle A3 stehen, in Hausmann auf A5, in Großhandel A12 und in Einzelhandel A13. Es kann auch sein, dass in einem Blatt die Kundennummer gar nicht vorkommt. Die Formel soll immer schauen, ob die Kundennummer vorkommt, ob ein Wert in den Zellen danach steht. Es kann sein, dass nicht immer alle Zellen gefüllt sind. Wenn er die Nummer findet und einen Wert in einer Zelle im Bereich F4:K13, dann soll er den Wert nehmen. Wenn er die Nummer findet aber keinen Wert, dann soll auf dem nächsten Tabellenblatt gesucht werden. Usw. 

Liebe Grüße
Anne

[Klaus-Dieter]

Hallo Anne,

gleichartige daten gehören auf ein Tabellenblatt, dann ist dein Problem keines mehr.

[Egon12]

Hallo Anne,

falls ich es richtig verstanden habe und du O365 nutzt reicht dies:

Code:

=LET(xA;FILTER(Hausmann!F5:K14;(Hausmann!A5:A14=A4)*(MMULT(--(Hausmann!F5:K14<>"");MTRANS(SPALTE(Hausmann!F5:K5)^0))>0);"");xB;FILTER(Großhandel!F5:K14;(Großhandel!A5:A14=A4)*(MMULT(--(Großhandel!F5:K14<>"");MTRANS(SPALTE(Großhandel!F5:K5)^0))>0);"");xC;FILTER(Einzelhandel!F5:K14;(Einzelhandel!A5:A14=A4)*(MMULT(--(Einzelhandel!F5:K14<>"");MTRANS(SPALTE(Einzelhandel!F5:K5)^0))>0);"");VSTAPELN(WENNS(INDEX(xA;1;1)<>"";xA;INDEX(xB;1;1)<>"";xB;INDEX(xC;1;1)<>"";xC)))

  Abfrage auf verschiedenen Tabellenblättern.xlsx (Größe: 16,04 KB / Downloads: 2)

Gruß Uwe

[anewelle]

Das hilft mir leider nicht weiter.
Die Daten werden so von verschiedenen Stellen angeliefert. Und sie sind nicht alle im gleichen Format. Den Bereich kann ich mir dann schon anpassen. Aber ich brauche eine Formel, die auf alle Blätter zugreift und erkennt, steht hinter der Kundennummer eine Information oder nicht. Wenn ja, zieht er sie vom ersten Blatt, wenn nein, sucht er weiter ob die Nummer auf Blatt 2 oder 3 eine  Information enthält.

Viele Grüße
Anne

[Sigi.21]

Hallo,

kommt dann evtl. eine VBA-Fkt. für dich in Frage?

Gruß Sigi