Sehr geehrte Damen und Herren,
ich habe eine etwas komplexe Frage zu Excel 2007.
Ich habe eine Excel-Datei für unseren Weinverkauf erstellt. Die Arbeitsblätter habe ich mit Vorlage (1), Vorlage (2), … immer wieder kopiert. In einem gesonderten Arbeitsblatt lese ich mit einem Makro die Blattnamen aus. Sie stehen dann alle untereinander in einer Spalte. In der Spalte daneben liste ich mit einer Formel den Gesamtpreis der gekauften Weine auf, daneben das Kaufdatum, usw.
Vorlage (1) =´Vorlage (1)´!$H$36
Vorlage (2) =´Vorlage (2)´!$H$36
Vorlage (3) =´Vorlage (3)´!$H$36
Vorlage (4) =´Vorlage (4)´!$H$36
Vorlage (5) =´Vorlage (5)´!$H$36
Die Formel in der zweiten Spalte kann ich zwar kopieren, sie enthalten dann aber immer ´Vorlage (1)´ und ich muss von Hand die Nummer (1) in (2), (3) … bis (32) ändern. Eine sehr zeitaufwändige Angelegenheit und das jeden Monat. Die Blattnamen bekommen später den Namen des Käufers und mein Makro ändert dann auch in allen Formeln die „Vorlage“ in den betreffenden Namen um.
Otto =Otto!$H$36 =Otto!$H$37 =Otto!$I$1
Vorlage (2) =´Vorlage (2)´!$H$36 =´Vorlage (2)´!$H$37 =´Vorlage (2)´!$I$1
Vorlage (3) =´Vorlage (3)´!$H$36 =´Vorlage (3)´!$H$37 =´Vorlage (3)´!$I$1
Vorlage (4) =´Vorlage (4)´!$H$36 =´Vorlage (4)´!$H$37 =´Vorlage (4)´!$I$1
oder so in der normalen Ansicht
Otto 35,40 € 01.01.15
Vorlage (2)
Vorlage (3)
Vorlage (4)
Es muss doch eine Möglichkeit geben, beim Kopieren der Formel die (1) in die nächsthöhere Zahl umzuwandeln.
Vielen Dank im Voraus!
Mit freundlichen Grüßen,
Eva Eller
Hallo Frau Eller,
Zunächst „Danke“ für Ihre ergänzenden Hinweise zur Entstehung der Leerzeichen in Ihren Arbeitsblättern. Daraufhin habe auch ich erst einmal recherchiert, wie man diese Problematik am einfachsten ohne zusätzlichen Aufwand lösen kann. Die notwendigen Hinweise habe ich dann auf https://forum.cad.de/foren/ubb/Forum226/HTML/001709.shtml gefunden.
Nachfolgend nunmehr die aktualisierten Angaben, die Sie bitte in den folgenden Zellen eintragen müssten, damit auch die Zellinhalte aus Arbeitsblattnamen mit vorkommenden Leerzeichen problemlos in der Gesamtübersicht
angezeigt werden:
in Zelle A4: ´=WENN(ZEILE(A3)>ANZAHL2(x);““;HYPERLINK(„#´“&INDEX(x;ZEILE(A3))&“´!A1“;TEIL(INDEX(x;ZEILE(A3));FINDEN(„]“;INDEX(x;ZEILE(A3)))+1;31)))´
in Zelle B4: ´=INDIREKT(„´“&TEIL(INDEX(x;ZEILE(A3));FINDEN(„]“;INDEX(x;ZEILE(A3)))+1;31)&“´!H36″)´
in Zelle C4: ´=INDIREKT(„´“&TEIL(INDEX(x;ZEILE(A3));FINDEN(„]“;INDEX(x;ZEILE(B3)))+1;31)&“´!H37″)´
Die Formeln weichen durch Verwendung anderer Excel-Funktionen gegenüber der vorherigen Fassung etwas voneinander ab. Wichtig ist am Ende allerdings die Funktionsfähigkeit. Sollte in den Spalten B und C die Zuordnung der Werte aus den Arbeitsblättern zum jeweiligen Kunden nicht sofort passen,müssten Sie evtl. durch Ausprobieren der Zeilenangabe die korrekte Zuordnung herauszufinden, aber vielleicht funktioniert es auch gleich auf Anhieb …
Anbei auch ein Datei-Anhang mit der überarbeiteten Fassung.
Ich wünsche Gutes Gelingen
dieter53
Nachtrag: Nachdem ich diese Antwort verfasst hatte, stellte ich Ihre Ergänzungsmitteilung fest, welche ich versuche, bis morgen zu beantworten. Evtl. versuchen Sie zwischenzeitlich einmal meine aktualisierten Hinweise umzusetzen (u.a. wegen der Leerzeichen-Probematik) MfG dieter53
Schön, dass Ihre Frage beantwortet wurde!
Um weiterhin auf dem Laufenden zum Thema Technik und PC zu bleiben, nehmen Sie doch kostenlos teil an unseren monatlichen LIVE-Webinaren: hier klicken für Registrierungsseite. Bei diesen Online-Shows können Sie uns all Ihre Fragen rund um das Thema Computer stellen und lernen jeden Monat etwas Neues.
Sehr geehrte Frau Eller,
bin selbst nicht der Excel-Experte vom Experten-Team – habe aber nach Kenntnis Ihrer Frage am Wochenende mal etwas gegoogelt und folgende Lösung entdeckt:
In einem Beitrag auf „https://www.excel-center.de/foren/read.php?2,31305,31307#msg-31307“ ist eine Beispieldatei „Tabellennamen.xls“ mit ähnlicher Fragestellung downloadbar und sollte hinsichtlich Ihrer Frage abwandelbar sein.
Falls das noch nicht die gewünschte Lösung sein sollte, dann dies hier im Forum mitteilen und dann doch mal auf die Antwort eines Experten vom Experten-Team warten.
Toi, toi, toi
dieter53
Sehr geehrte Frau Eller,
in Anlehnung an die bereits erwähnte Quelle (einem Zeitpunkt, als noch Excel 2003 vorherrschte) habe ich – bezogen auf die aktuelle Fragestellung – die Erläuterungen etwas aktualisiert (unter Excel 2010, welches Ihrem verwendeten Excel 2007 ähnelt):
Demnach muss zunächst die betreffende „Excel-Tabelle“ wie folgt dafür „vorbereitet werden“:
1. Zur Registerkarte „Formeln“ gehen
2. und dann in der Gruppe „Namen definieren“
3. die Option „Namen definieren …“ wählen
4. dort eine Namenszuweisung für: x vornehmen und
5. bei ´bezieht sich auf´ das folgende eintippen:
=ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())
(- siehe oberer Teil der Grafik im Datei-Anhang)
Danach kann die nachfolgende Formel auf dem Blatt „vorhandene Tabellen“ in die Zelle A4 kopiert werden
Zelle A4 ´=WENN(ZEILEN($1:1)>VERGLEICH(„“;x;-1);““;TEIL(INDEX(x;ZEILEN($1:1));FINDEN(„]“;INDEX(x;ZEILEN($1:1)))+1;31))´
In den Spalten B und C wäre noch einzutragen:
Zelle B4 ´=WENN(ISTFEHLER(INDIREKT(A4&“!H36″));““;INDIREKT(A4&“!H36″))´
Zelle C4 ´=WENN(ISTFEHLER(INDIREKT(A4&“!H37″));““;INDIREKT(A4&“!H37″))´
Man kann diese 3 Spalten-Formeln nun zeilenweise kopieren und hat, wenn das entsprechende Arbeitsblatt existiert, eine gute Übersicht der gewünschten Einträge aus den Einzelblättern. Eine beabsichtigte Bereitstellung einer meinerseits getesteten „Excel-Datei nach Ihren Wünschen“ als Datei-Anhang scheiterte leider an den Sicherheitseinstellungen des Computer-Clubs (Stichwort: Makro-Virengefahr, obwohl ich keine Makros verwendet habe) – aber evtl. kommen Sie auch mit vorgenannten Erläuterungen und dem Foto klar.
Nette Grüße
dieter53
Hinweise zum am 06.01.2015 ergänzten Datei-Anhang :
1. Vergrößerte Darstellung der angehängten Grafik per Klick mit linker Maustaste
2. Noch größere Darstellung möglich durch Abspeichern der unter 1. bereits vergrößerten Grafik -> wie folgt:
– „Mausklick rechts “ und
– „Grafik speichern unter …“ ,
– z.B. „Test_Vorlage“ und
– als ´PNG Image´ speichern.
= diese so auf dem PC abgespeicherte Datei unter Paint öffnen -> damit sollten die Formel-Details jetzt besser erkennbar sein.
Sehr geehrte Frau Eller,
ich hoffe, Sie konnten die empfohlene Datei schon mal downloaden und meine dazu gegebenen ergänzenden Hinweise durchlesen und versuchen jetzt (sofern zeitlich möglich gewesen), dieses umzusetzen.
Mir ist jetzt noch aufgefallen, dass Ihre Datenblätter vor der erst später erfolgenden Umbenennung Leerzeichen beinhalten ´Vorlage (1)´ ´Vorlage (2)´ ´Vorlage (3)´ usw. – das ist für die Nutzung der übermittelten Formeln nachteilig, weil damit die Inhalte der Spalten B (Kaufpreis) und C (Kaufdatum) nicht angezeigt werden.
Ich empfehle Ihnen daher statt ´Vorlage (1)´, ´Vorlage (2)´, … besser ´Vorlage001´, ´Vorlage002´ usw. zu verwenden – damit ist die Funktionsfähigkeit der Formeln in den Spalten B und C gewährleistet.
Vielleicht ist auch die Umbenennung des Blattnamen in den Kundennamen durch Ihr Makro vorzeitig möglich – das kann ich aber nicht einschätzen, da ich die organisatorischen Abläufe beim Weinverkauf in Verbindung mit Ihrem
Excelprogramm und das Makro nicht kenne.
Bei Bedarf evtl. nachfragen, wenn etwas unklar ist .
Ich wünsche „Gutes Gelingen !“
Nette Grüße
dieter53
Hallo, dieter53!
Vielen Dank für die vielen Hinweise.
Leider bin ich noch nicht dazugekommen die umfangreichen Informationen auszuprobieren.
Ich möchte nur zu der Benennung der Blattnamen eine Anmerkung machen. Ich erstelle ein Arbeitsblatt mit unseren Weinangeboten und nenne es „Vorlage“. Dieses Blatt vervielfältige ich nun so ca. 30 mal für den aktuellen Monat mit der Funktion „Arbeitsblatt verschieben/kopieren“. Excel erstellt dann automatisch die anderen Arbeitblätter mit Vorlage (1), Vorlage (2), …. Vorlage (3). Es wäre sehr mühsam diese Arbeitsblätter dann einzeln in „Vorlage001“ usw. umzubenennen. Eine Kopie von „Vorlage001“ benennt Excel dann in „Vorlage001 (1). Die Umbenennung in Kundennamen – bei denen ich durch einen Unterstrich (_) Leerstellen vermeide – kann ich erst einfügen, wenn Kunden eine Weinbestellung abgeben. Es handelt sich ja nicht um feste Kunden. Eine andere Lösung 30 Arbeitsblätter mit immer der gleichen Preisliste zu erstellen habe ich nicht gefunden.
Noch einen schönen Feiertag,
Eva Eller
Hallo, dieter53!
Dank dieser ganz ausführlichen Anleitung hat alles hervorragend geklappt. Mit der Excel 2003-Anleitung bin ich nicht zurechtgekommen. Die darauffolgenden Mails mit Screenshots waren die Lösung.
Der einzige Haken sind nur noch die Tabellenblattnamen mit den Leerzeichen. In Spalte F auf dem Arbeitsblatt „Vorhandene Tabellen“ habe ich eine Matrix, die mir die Beträge aus den Spalten B und C summiert. Wenn jetzt einem Tabellenblatt z.B. Vorlage (27) kein Kunde zugeordnet wird und das Ergebnis 0,00 € sein müsste liefert mir die Zelle „#WERT!“. Das ist an sich nicht so schlimm, aber auch die Summenfunktion der Werte in der Matrix liefert mir „#WERT!“ Diese Summe ist mir aber als Kontrolle wichtig um eventuelle Berechnungsfehler herauszufinden.
Vielen Dank, ich habe eine Menge dazugelernt.
Eva Eller
Hallo Frau Eller,
melde mich gleich nochmal – sorry, ich habe ein Verständnis-Problem zu der von Ihnen erwähnten Matrix in Spalte F (mit Summierungen aus den Spalten B und C) in Ihrem Arbeitsblatt „Vorhandene Tabellen“ :
Was summieren Sie da genau ? (Kaufpreis und Kaufdatum (wie im von mir angenommenen Beispiel) sind es vermutlich nicht.) -> Dazu bitte ich Sie um ein paar mehr Details zu Ihrer Matrix in Spalte F (mit den von Ihnen verwendeten Formeln).
Dass ein Fehler „#WERT!“ bei einer Summierung auftritt, wenn in den zu summierenden Zeilen „#WERT!“-Fehler vorhanden sind, leuchtet mir ein.
Hier würde ich allerdings erst einmal Ihre Antwort zur Matrix abwarten, evtl. wird mir es dann verständlicher. Oder treten die vorgenannten Fehler in der neuen Version gar nicht mehr auf ?
Danke für Ihre Rückinfo !
Mit freundlichen Grüßen
dieter53
Hallo Dieter53,
es funktioniert alles prima, auch wenn ich die wenigsten Zusammenhänge verstehe.
Der Link zur Leerzeichenproblematik hat bei mir „NOT FOUND“ ergeben. Die drei nachfolgenden Formeln haben dann sofort das richtige Ergebnis gebracht. Auch die Matrix zeigt jetzt die richtigen Beträge an.
Eigentlich hätte ich nur die Formeln in den Spalten B und C ändern müssen. Die Änderung der Formel in Spalte A hat aber diese zusätzliche Funktion (ich nehme an, das ist der Hyperlink), dass sich der Mauszeiger in eine zeigende Hand verwandelt und ich mit einem Klick auf eine Vorlage das betreffende Arbeitsblatt aufrufen kann. Bisher habe ich mühsam mit den kleinen Dreiecken links unten von Arbeitsblatt zu Arbeitsblatt bewegt.
Das entstandene Verständnisproblem mit der Matrix ist meine Schuld. Ich wollte meine Frage etwas einfacher stellen da ich nicht mit so fundierten, ausführlichen Antworten gerechnet hatte. In Spalte C ist ebenfalls ein Eurobetrag für zusätzliche Kosten wie Geschenkkartons oder Vereinsrabatte. Das Datum erscheint dann erst in Spalte D. In der Matrix werden die Weinkosten plus/minus der weiteren Kosten berechnet. Die Summen von A und B müssen dann mit der Summe von Spalte D übereinstimmen.
Es hat unheimlich Spaß gemacht all diese Vorschläge auszuprobieren.
Nochmals vielen, vielen Dank!
Eva Eller
Hallo Frau Eller,
Danke für Ihre erfreuliche Rückmeldung! Damit hat unsere Korrespondenz wohl letztendlich das gewünschte Ergebnis gebracht … ?
Den Link zur Leerzeichenproblematik habe ich nochmals überprüft und gleich in der Antwort vom 06.01.2015, 18:18 Uhr korrigiert (Danke für Ihren Hinweis !)
Die vorgeschlagenen Änderungen der Formeln in den Spalten B, C und auch A basieren auf vorgenanntem Link und daher habe ich alles komplett geändert, statt eine Lösung aus zweierlei Quellen anzubieten. Aber entscheiden Sie selbst, was Ihnen besser gefällt.
Übrigens: Weiterführende Hinweise z.B. zum Definieren und Verwenden von Namen in Formeln gibt es auf diversen Internetseiten u.a.auf
https://support.office.com/de-ch/article/Definieren-und-Verwenden-von-Namen-in-Formeln-b2bacf14-945d-41d4-b3aa-267b18a23f6e#__toc312073993
oder zum Zuordnen einer Arbeitsmappe auf
https://www.herber.de/forum/archiv/1080to1084/1082747_ARBEITSMAPPEZUORDNEN10JETZT.html
-> bei Interesse einfach danach googeln und ausprobieren …
Wenn Ihnen meine Antwort vom 06.01.2015, 18.18 Uhr „als Lösung“ zusagen sollte, können Sie unterhalb des genannten Beitrages auf „Als Lösung markieren“ klicken und damit diesen Thread als „GELÖST“ abschließen. 😀
Ich wünsche Ihnen für das Jahr 2015 alles Gute
und verbleibe mit freundlichen Grüßen
dieter53
Hallo Frau Eller,
auf meine letzte Mail vom 08.01.2015 konnte ich keinerlei Reaktion Ihrerseits erkennen. Ich hoffe, dass Sie mit der Lösung zufrieden waren, bisher nur noch nicht den Mut hatten, das Thema „durch Markieren von „Als Lösung markieren“ abzuschließen (?) .
Da ich aus Ihren Worten vom 07.01.2015 prinzipiell ein positives Fazit erkennen konnte, wäre meine Bitte den Thread – wie oben beschrieben – als „GELÖST“ abzuschließen.
Zwischenzeitlich habe ich mit dem den „Club.Computerwissen.de“ betreuenden Mitarbeiter des Verlages das Thema „Bereitstellung von Excel-Dateien“ nochmals erörtert und würde versuchen, die seinerzeit nicht funktionierende Übermittlung der Excel-Datei zu wiederholen, quasi ein Test, der mir und dem Verlag für künftige Hilfestellungen an andere User weiterhilft. Ich würde daher nochmals eine Excel-Datei übermitteln und hoffe, dass diese als Datei-Anhang diesmal akzeptiert wird. Im schlimmsten Fall sehen Sie doch keine Datei – aber das wäre dann auch eine Erkenntnis.
Nette Grüße sendet Ihnen
dieter53
Erkenntnis: Für das zur Lösung der Aufgabenstellung notwendige xlsm-Format ist aus Sicherheitsgründen ein Upload nicht möglich. (daher kein Datei-Anhang !)
Tut mir leid:
„Als Lösung markieren“ klicken und damit diesen Thread als „GELÖST“ abschließen.
Ich war der Meinung, dass ich das bereits getan hätte. Ich finde nur ein
„x Als ungelöst markieren“.
Suche ich an der falschen Stelle?
Auf jeden Fall ist mein Problem zu vollster Zufriedenheit gelöst worden. Ich arbeite bereits in mehreren Excel-Dateien mit diesen Formel.
Nochmals vielen Dank,
Eva Eller
Hallo Frau Eller,
da bis gestern die von mir am 08.01.2015 und am 16.01.2015 erbetene Markierung leider immer noch nicht vorhanden war, habe ich die Verantwortlichen im Club.Computerwissen um Hilfe gebeten, da Sie in Ihren Kommentaren bereits positive Rückmeldung gegeben hatten.
Ihre Meinung, dass Sie das bereits getan hätten, steht im Widerspruch zu den bis gestern vorhandenen Gegebenheiten. Zwischenzeitlich wurde die „Gelöst“-Markierung vom Club.Computerwissen-Team vorgenommen, so dass Sie jetzt mit „x Als ungelöst markieren“ im Falle, dass „Ihnen“ diese Lösung doch nicht zugesagt hätte, diese wieder rückgängig hätten machen können. Das trifft erfreulicherweise jedoch nicht zu, so dass das Thema damit wirklich abgeschlossen ist.
Ich bedanke mich, dass Sie sich noch einmal gemeldet haben und verbleibe mit freundlichen Gruessen
dieter53