Supportnet / Forum / Tabellenkalkulation

Hallo Leute Ich versuche es noch mal mit einer Frage. Ich habe drei Blätter in eine Exceldatei. Objekt, Obst und Gemüse Auf Blatt Objekt werden die Daten eingegeben. Wenn ich auf Objekt in A1 Apfel und in B1 Obst schreibe, soll in A1 auf Blatt Obst und Gemüse vergleichen werden ob B1 auf Objekt mit dem Blattname übereinstimmt, und ggf. eintagen. Also in diesem Beispiel müsste jetzt auf Obst in A1 Apfel stehen, wenn ich satt Obst Gemüse eingetragen hätte, sollte Apfel auf Blatt Gemüse in A1 stehen. Ist das machbar? Ohne VBA? Vielen Dan im Vorraus.

Hallo PP_MK2,

das ist ohne VBA machbar, kostet Dich aber eine Hilfsspalte auf dem Objekt-Blatt. Nehmen wir dafür gleich C. Schreibe in C1 die Formel:

=B1 & LINKS("00" &ZÄHLENWENN(B$1:B1;B1); 3)

und kopiere sie so weit runter wie nötig. Schreibe auf das Obst-Blatt in A1 die Formel:

=INDIREKT("Objekt!A" & VERGLEICH("Obst" & LINKS("00" & ZEILE(); 3); Objekt!C$1:C$10; 0))

und auf das Gemüse-Blatt in A1 die Formel:

=INDIREKT("Objekt!A" & VERGLEICH("Gemüse" & LINKS("00" & ZEILE(); 3); Objekt!C$1:C$10; 0))

und kopiere sie ebenfalls so weit runter wie nötig. Nun kannst Du die Früchtchen in den Spalten A und B von Objekt eingeben und sie müssten auf dem richtigen Blatt erscheinen.

Voraussetzung: die Bezeichnung in Spalte B von Objekt stimmt mit den Bezeichnungen in den Formeln ("Obst" , "Gemüse") überein. Auf die Tabellenblattnamen wird derzeit kein Bezug genommen. Wenn das wichtig ist, kann man das nachträglich noch einrichten.

Gruß,
CaroS

Hallo

Das geht schon ganz gut.

Wäre super wenn du den Tabellenblattbezug auch noch hinkriegen würdest.

Es kann schon mal vorkommen, das ich mehrere Tabellenblätter habe, dann bräuchte man ja nur die Fromel kopieren.

Vielen Dank schon mal für die Mühe.

Peter

Hallo ,

ersetzen in den Formeln "Obst" bzw. "Gemüse" jeweils durch

RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1))

C1: =B1 & LINKS("00" &ZÄHLENWENN(B$1:B1;B1); 3)

Tabellenblatt "Obst" - A1: =INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & LINKS("00" & ZEILE(); 3); Objekt!C$1:C$10; 0))

Tabellenblatt "Gemüse" - A1: =INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & LINKS("00" & ZEILE(); 3); Objekt!C$1:C$10; 0))

Gruß,
CaroS

Ja fast, haut noch nicht ganz hin

Immer wenn die Daten in Objekt geändert werden, muss man die Formel auf den anderen blättern neu ein geben. Zumindest Doppelklick in A1 und Enter. Dann werden die Felder richtig angezeigt. Wir kann man das denn noch umgehen?

Gruß Peter

Na nu, jetzt seht immer gar nicht da.

Mache ich da was falsch?

Gruß
Peter

Hallo Peter,

ich habe weder AW4 noch AW5 verstanden. Kannst Du mal eine Beispieldatei hochladen, wo der Effekt zu sehen ist, den Du meinst?

Gruß,
CaroS

Das ist auch immer gar nicht so einfach,
es so zu beschreiben das ein anderer es versteht.

Ich habe hier mal ein Download eingerichtet.

Weite Beschreibung habe da rein geschrieben.

Gruß
Peter

www.badminton-sh.de/xls/vergleich.xls

Hallo Peter,

die Ursache des Übels liegt darin, dass der Wechsel von einem Tabellenblatt zu einem anderen für Excel erstmal keinen Grund darstellt, irgendeine Formel in irgendeiner Zelle, geschweige denn alle Formeln in allen Zellen neu zu berechnen.

Die Funktion ZELLE("dateiname") kommt auf den Tabellenblättern ´Obst´, ´Gemüse´ und ´zahl´ zum Einsatz und liefert zu einem bestimmten Zeitpunkt letztlich den Namen des gerade aktiven Tabellenblattes. Nach einem Wechsel auf ein anderes Tabellen-blatt würde ZELLE("dateiname") zwar einen anderen Namen liefern, nämlich den Namen des neuen nun aktiven Tabellenblattes, aber es fehlt der Anstoß, diese Neuberechnung auch auszuführen.

Deshalb wird auch auf dem neuen Tabellenblatt der alte, bei der letzten Berechnung ermittelte Name verwendet, und die Formeln zeigen dementsprechend die Früchtchen an, die eigentlich auf das alte Tabellenblatt gehören. (Das ist eine völlig korrekte Arbeitsweise von Excel!)

Du kannst zwei Dinge tun. Entweder nach einem Wechsel auf ein neues Tabellenblatt die Taste F9 drücken (- alles andere, Doppelklick in A1, Enter usw. ist unnötig), um manuell eine Neuberechnung auszulösen, oder die Zeile ActiveSheet.Calculate in das Workbook_SheetActivate-Ereignis einfügen. Dann wird die Neuberechnung des gerade ausgewählten Tabellenblattes nach jedem Wechsel automatisch durchgeführt. Dafür müsstest Du aber die Ausführung von Makros zulassen.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveSheet.Calculate
End Sub

Außerdem könntest Du noch die Formeln auf den Obst- und Gemüse-Blättern so erweitern, dass keine #NV-Fehler mehr angezeigt werden, indem Du anstelle von

=INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); Objekt!C$1:C$10; 0))

die Formel

=WENN(ISTFEHLER(INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); Objekt!C$1:C$10; 0))); ""; INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); Objekt!C$1:C$10; 0)))

verwendest. Mit WENN(ISTFEHLER(); ...) wird der Fehler abgefangen. Hier das Beispiel zum Download:
http://www.netupload.de/detail.php?img=94040ae9b692a2ef1d91134e9281...

Gruß,
CaroS

Hallo Peter,

es gibt noch ein paar Fehler zu beseitigen. Ich habe eben völlig vergessen zu erwähnen, dass ich die Formel in den Tabellen ´Obst´, ´Gemüse´ und ´zahl´ gerade an einer Stelle geändert habe, weil mir ein Fehler aufgefallen war. Ich habe LINKS() gegen RECHTS() ersetzt, sonst wird die Nummerierung ab 10 nicht korrekt erstellt. (Merkt man aber erst, wenn man es mit mehr als 10 Dingen von einer Sorte probiert.) Diese Änderung ist in der hochgeladenen Datei schon drin.

Allerdings habe ich vergessen, dieselbe Änderung auch in Spalte C in ´Objekt´ durch-zuführen.

Außerdem müsste man auch den Bereich C$1:C$10 etwas erweitern, wenn man mit ein paar Objekten mehr arbeiten will. Ich habe C$1:C$999 genommen, das erkennt man gut wieder. Die Formeln würden dann insgesamt so aussehen:

Tabellenblatt "Objekt" - C1: =B1 & RECHTS("00" &ZÄHLENWENN(B$1:B1;B1); 3)

Tabellenblatt "Obst" - A1: =WENN(ISTFEHLER(INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); Objekt!C$1:C$999; 0))); ""; INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); Objekt!C$1:C$999; 0)))

Tabellenblatt "Gemüse" - A1: =WENN(ISTFEHLER(INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); Objekt!C$1:C$999; 0))); ""; INDIREKT("Objekt!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); Objekt!C$1:C$999; 0)))

http://www.netupload.de/detail.php?img=0a18bd9059a976e54d0d4a558f12...

Gruß,
CaroS

Super CaroS !!!!

Das scheint es zu sein. Vielen Dank.
Das hätte ich ja nie hinbekommen.

Gruß
Peter

Ich noch mal

Ich habe mal versucht das Blatt Objekt umzubenennen und in den Formeln jeweils den Blattnamen gegen Objekt getausch.
Ging aber nicht. Was muss ich den da noch beachten?

Gruß
Peter

umgedeht Objekt habe ich gegen den neuen Blattname getauscht

Gruß
Peter

Hallo Peter,

ist ja wieder eine ganz großartige Fehlerbeschreibung. Da könnte es ein Weilchen dauern, bis sich jemand findet, der damit etwas anfangen kann.

Du brauchst eigentlich nichts weiter beachten als an jeder Stelle, wo "Objekt!A" oder "Objekt!C" steht, den alten Tabellenblattnamen Objekt gegen Deinen neuen Tabellenblattnamen XXXXXXXX auszutauschen, so dass dann "XXXXXXXX!A" bzw. "XXXXXXXX!C" da steht.
Wenn ich von der letzten Formel ausgehe, musst Du den Namen 4-mal ersetzen:

=WENN(ISTFEHLER(INDIREKT("XXXXXXXX!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); XXXXXXXX!C$1:C$999; 0))); ""; INDIREKT("XXXXXXXX!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); XXXXXXXX!C$1:C$999; 0)))

Gruß,
CaroS

Hallo Peter,

was spricht denn dagegen, in den einzelnen Tabellenblättern irgendwo den Dateinamen hinzuschreiben, oder per Formel ermitteln zu lassen. In meinem Formelbeispiel wäre das die Zelle $A$1

Die Hilfsspalte würde nicht mehr benötigt.

Formel:
{=WENN(ZÄHLENWENN(Objekt!B:B;$A$1)<ZEILE(A1);"";INDEX(Objekt!$A$1:$A$1000;KKLEINSTE(WENN(Objekt!$B$1:$B$1000=$A$1;ZEILE($1:$1000));ZEILE(A1))))}

Achtung!! Arrayformel, geschweifte Klammern nicht mit eingeben, sondern Formel normal erfassen und mit Strg+Shift+Enter abschliessen.

Das Problem mit dem Umbennen der Tabellenblätter wäre dann auch erledigt.

CU Aliba

Hallo CaroS

Ist schon komisch, ich hatte genau das versucht. Hatte aber irgendwie nicht hingehauen. Und heute gleich bei dem ersten versuch.

Nun hatte ich noch das Problem, das ich mich dazu entschlossen habe, Kopfzeilen einzugeben. Doch wenn ich auf Objekt zwei Zeilen (1 und 2) einfüge, passiert folgendes. Wenn z.B. in A1 Apfel, in A2 Birne und in A3 Banane steht, wird Apfel auf dem Blatt Obst erst nach dem Eintag Banane bzw. nach dem Eintag Obst in B3 angezeigt. Und dann steht Apfel auch nicht in A1 sondern in A3.
Vielen Dank noch mal.

Hallo Aliba

Ich habe es mal ausprobiert, was mir nicht so gefällt ist:

Die Einträge aus den Zellen werden 1:1 übergeben, ich meine damit auch in der gleichen Zeile. So kann es vor kommen, das in Zelle A2 und in A3 etwas steht und in A4, A5 und A6 nicht und dann aber wieder in A7. Das ist nicht so gut.

Ich wünsche einen schönen Sonntag
Peter

Hallo Peter,

von Deiner Beschreibung habe ich nicht allzu viel verstanden. Ich konnte folgen bis

Du hast also auf dem Tabellenblatt ´Objekt´ oben zwei Zeilen eingefügt. (Wieso Kopf- zeilen? Kopfzeilen sind was ganz anderes. Sag einfach nur Zeilen, dann entstehen auch keine Missverständnisse.) Dadurch hast Du nun ein neues Problem, weil irgendetwas mit den Äpfeln und Birnen passiert ist.

Es ist klar, was passiert ist, die Formeln auf den anderen Tabellenblättern wurden automatisch angepasst. Wo vorher Objekt!C$1:C$999 bzw. XXXXXXXX!C$1:C$999 stand, müsste jetzt Objekt!C$3:C$1001 bzw. XXXXXXXX!C$3:C$1001 stehen. Durch die zwei eingefügten Zeilen wurde der Bereich C$1:C$999 ebenfalls um zwei Zeilen verschoben (C$3:C$1001).

Es gibt nun zwei Möglichkeiten: Entweder die untere Grenze des Bereichs von 3 auf 1 herunterzusetzen (C$3:C$1001 zu ändern in C$1:C$1001)
oder zu dem von der VERGLEICH-Funktion ermittelten Wert 2 dazu zu addieren:

=WENN(ISTFEHLER(INDIREKT("XXXXXXXX!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); XXXXXXXX!C$1:C$999; 0) + 2)); ""; INDIREKT("XXXXXXXX!A" & VERGLEICH(RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname")) - FINDEN("]"; ZELLE("dateiname"); 1)) & RECHTS("00" & ZEILE(); 3); XXXXXXXX!C$1:C$999; 0) + 2))

Gruß,
CaroS

Hallo Peter,

kann nicht nachvollziehen, was Du meinst.

Die Einträge sollten direkt untereinander stehen, es sei denn, Du hast in SPalte B etwas stehen und in Spalte A steht nichts.

CU Aliba

• vba vergleich des zelleninhalts
• Zellen
• Vergleich ob Datum in Zelle oder nicht
• Excel VBA
• VBA - Prüfung Blattname