Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
880to884
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
880to884
880to884
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Namendefinition

Namendefinition
26.06.2007 23:55:00
M.Fecker
Hallo
Ich möchte mittels VBA einen definierten Namen (werz) im Arbeitsblatt OP einfügen lassen. Der Namen bezieht sich auf das Arbeitsblatt EK (Feld C45). Das Blatt EK wird manuell ausgetauscht gegen eine neuere Version. Das neue Blatt hat den gleichen Blattnamen (EK). Nun möchte ich wieder im Blatt OP durch einfügen =werz den aktuellen Wert des neuen Blattes haben.
Problem ist, dass sich mit dem austauschen der Blätter der Namensbereich ändert und auf das 1. Blatt fixiert wird. D. h. wenn ich das erste Blatt EK1 nenne damit ich das neue EK einfügen kann, ist der Bezug des Namens (werz) auf das "EK1" bezogen und nicht auf "EK"
Die Anzahl Zeilen im Blatt EK verändern sich, deshalb habe ich Namen definiert
Rahmenbedingungen: Im Blatt OP benötige ich eine Verknüfung zu C45 /
Danke für eure Unterstützung.
Gruss
Martin

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Namendefinition
27.06.2007 00:37:00
fcs
Hallo Martin,
du kannst nach dem Einfügen des neuen Blattes EK den Bereich für den Namen "werz" manuell auf das neue Blatt einstellen.
Via Menü Einfügen-Namen-Festlegen/Definieren bekommst du den Dialog angezeigt. Den Namen "werz" wählen und im Eingabefeld "bezieht sich auf" den Tabellennamen von EK1 in EK ändern.
Dann sollte es wie gewünscht funktionieren.
Makro-Lösung für das Zuweisen des neuen Bereichs:

Sub werz_Bereich_neu_zuweisen()
'Bereich für Namen "werz" neu zuweisen
ActiveWorkbook.Names.Add Name:="werz", RefersToR1C1:="=EK!R45C5"
End Sub


Gruß
Franz

Anzeige
AW: Namendefinition
27.06.2007 22:37:00
Martin
Hallo Franz
Habe mir dein Tipp beherzigt, danke. Nun habe ich das Problem mit dem Einfügen des anderen Tabellenblattes. Ich habe die Datei hochgeladen. In der Praxis würden die einzelnen Blätter "Einkauf" und "Mechanik" von der Datei via "verschieben/kopieren" in eine neue Arbeitsmappe kopiert werden. Die Datei erhält einen anderen Namen. Nachdem sie ausgefüllt wurden sollen die einzelnen Blätter wieder zurückgespielt werden. Und hier erscheint eine Fehlermeldung, dass die Mapppen nicht kopiert werden können. Wie kann ich das umgehen?
Gruss
https://www.herber.de/bbs/user/43642.xls

Anzeige
AW: Namendefinition
28.06.2007 01:27:39
fcs
Hallo Martin,
das wird dann aber leider sehr kompliziert. Das Problem ist dann, dass beim Wiedereinfügen der Tabellenblätter viele Bereichsnamen in beiden Dateien vorhanden sind. Excel will dann wissen ob die bereits vorhandenen Namen verwendet werden sollen, oder ob Namen in dem einzufügenden Blatt umbenannt werden sollen.
Ich hab an dieser Stelle keine Ahnung, ob man einen dieser Wege gehen kann ohne sich Informationen zu zerstören.
Falls du tatsächlich laufend Blätter aus der Datei zum Ausfüllen herauskopierst und später wieder mit Daten hineinverschieben/kopieren willst, dann solltest du zum Teil auf die Verwendung von Namen in den Tabellenblättern Einkauf und Mechanik verzichten und die vorhandenen Namen so festlegen, dass sie nur innerhalb des jeweiligen Tabellenblatts gelten (Dies kann man nur im Dialogfenster "Namen festlegen/definieren"!!). Beim Festlegen der Namen muss dann der Tabellenname zusätzlich vor dem Namen angegeben werden (z.Bsp: Einkauf!Kurs). Wenn die Namen auf diese Art festgelegt sind, dann funktioniert auch das Kopieren und Wieder-Einfügen der Blätter ohne dass es zu Fehlermeldungen kommt.
Leider ist diese Materie etwas komplizert, aber ich kann es irgendwie auch nicht einfacher beschreiben.
Es ist möglich durch eine geschickte Kombination der Funktionen INDEX, VERGLEICH und INDIREKT die Werte aus der Summenzeile der Blätter Einkauf und Mechanik in das Blatt Operationen zu übernehmen. Selbst dann wenn die Nummer der Summenzeile sich ändert. Man muss also nicht unbedingt mit Namen arbeiten.
Ich hab jetzt in der Datei die Namen einmal nach obigem Strickmuster neu festgelegt und auch die Formeln im Blatt Operation eingebaut.
Die Makros zum neu Zuweisen der Namen brauchst du dann auch nicht mehr. Du muss "nur" im Blatt "Operation" die korrekten Namen der Tabellenblätter eintragen, damit die Formeln auf die richtigen Blätter zugreifen.
https://www.herber.de/bbs/user/43644.xls
Ich hoffe du kommst mit der Datei klar. Es sind dort jetzt auch 2 weitere Blätter drin, die sich nach allen Anpassungen problemlos in eine neu Mappe kopieren und anschließend wieder einfügen liessen.
Gruß
Franz

Anzeige
AW: Namendefinition
28.06.2007 19:36:54
Martin
Hallo Franz
Danke für den Lösungsansatz. Es scheint eine gute Formel zu sein. Leider verstehe ich nicht was darin in den einzelnen Schritten genau passiert. Kannst du mir bitte dazu eine Erklärung geben?
WENN(ISTFEHLER(INDIREKT("'"&$A48&"'!A1"));"";INDEX(INDIREKT("'"&$A48&"'!A9:Z200");VERGLEICH("Angebot Whr";INDIREKT("'"&$A48&"'!A9:A200");0)+1;3))
Danke und Gruss
Martin

AW: Namendefinition
28.06.2007 22:24:13
fcs
Hallo martin,
in Zelle A48 steht der Name der Tabelle aus der Werte ausgelesen werden sollen. Hier jetzt die Erläuterungen zur Formel:

=WENN(ISTFEHLER(INDIREKT("'"&$A48&"'!A1"));"";INDEX(INDIREKT("'"&$A48&"'!A9:Z200");
VERGLEICH("Angebot Whr";INDIREKT("'"&$A48&"'!A9:A200");0)+1;3))


ISTFEHLER(INDIREKT("'"&$A48&"'!A1")):
Fehlerprüfung, ob die mit INDIREKT("'"&$A48&"'!A1") angegebene Adresse existiert. In diesem Fall, ob das in A48 eingegeben Blatt vorhanden ist. Bei Fehler wird ein Leerstring ausgegeben. Die Hochkommata (') vor und nach dem Namen sind erforderlich, falls im Blattnamen Leerzeichen oder sonstige Sonderzeichen verwendet werden.
INDEX(INDIREKT("'"&$A48&"'!A9:Z200");VERGLEICH("Angebot Whr";INDIREKT("'"&$A48&"'!A9:A200");0)+1;3)
Mit INDEX kann man aus einer Matix/Zellenbereich durch Angabe von Zeile- und Spaltennummer einen Wert auslesen.
Der Zellenbereich ist hier INDIREKT("'"&$A48&"'!A9:Z200"), also der Bereich A9:Z200 in dem in Zelle A48 angegebenen Tabellenblatt. Innerhalb des Bereichs beginnt die Zählung der Zeilen und Spalten in der linken oberen Ecke jeweils mit 1. A9 ist also die Zelle 1|1, Z9 Zelle 1|26 und Z200 Zelle 192|26 des Bereichs.
VERGLEICH("Angebot Whr";INDIREKT("'"&$A48&"'!A9:A200");0)+1
Mit dieser Teilformel wird im Bereich A9:A200 der Tabelle die Zeilennummer ermittelt, in der der Begriff "Angebot Whr" steht. Die Zeilenzählung beginnt dabei in der Zelle A9 mit 1. Da die Summen in der Zeile darunter stehen muss noch 1 addiert werden.
3
Diese Zahl gibt an aus welcher Spalte des angegebenen Zellbereichs der Wert entnommen werden soll.
Ich hoffe meine Erläuterungen helfen weiter, ansonsten: EXCEL hat auch eine F1 Taste um sich Hilfsinformation zu einzelnen Funktionen zu holen. Wobei ich natürlich zugebe, dass die gleichzeitige Verwendung von 3 Funktionen die Sache schon etwas unübersichtlich macht.
Gruß
Franz

Anzeige
AW: Namendefinition
28.06.2007 23:00:00
Martin
Hallo Franz
Vielen Dank für die detaillierten Ausführungen. Deine vorgeschlagene Lösungsvariante finde ich gut. Ich werde dies im Praxiseinsatz testen. Ich melde mich wieder.
Gruss
Martin

Erledigt oT
29.06.2007 13:27:37
.
ot

AW: Namendefinition
27.06.2007 23:09:43
Martin
Hallo Franz
Ich habe den Fehler soeben selbst gefunden. Habe Excel 2007 und vergessen in die Version 2003 abzuspeichern (wie die Originaldatei).
Somit möchte ich mich nochmals für deine Hilfe bedanken.
Gruss

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige