Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1384to1388
1384to1388
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Namensbereich ohne Kopfzeile

Forumthread: Namensbereich ohne Kopfzeile

Namensbereich ohne Kopfzeile
06.10.2014 16:12:47
yummi
Hallo Zusammen,
ist es möglich einen Namensbereich so anzulegen dass er für die gesamte Spalte A gilt bis auf die 1. Zeile (Kopfzeile)?
Gruß
yummi
PS: Eine andere Möglichkeit als = A2:A2^64 ;-)

Anzeige

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Namensbereich ohne Kopfzeile
06.10.2014 16:38:50
Martin
Hallo Yummi,
habe hier leider kein Excel2010. Unter Excel 2003 geht es aber so:
    With Range("A2")
ActiveWorkbook.Names.Add Name:="MeinName", RefersTo:=Range(.Address, .End(xlDown))
End With
Viele Grüße
Martin

Kleiner Denkfehler...
06.10.2014 16:45:35
Martin
Hallo Yummi,
ich kann ja nicht davon ausgegen, dass icu mit "End(x1Down)" automatisch die letzte Zeile erreiche. Dann halt so: 
    With Range("A2")
ActiveWorkbook.Names.Add Name:="MeinName", RefersTo:=Range(.Address, Cells(ActiveSheet. _
Rows.Count, .Column))
End With
Viele Grüße
Martin

Anzeige
AW: Kleiner Denkfehler...
06.10.2014 17:00:09
yummi
Hallo Martin,
wollte es ohne vba lösen.
Trotzdem danke
hier mein ansatz :
Datenüberprüfung - Liste -
=BEREICH.VERSCHIEBEN(Tabelle1!$B$2;;;ZÄHLENWENN(Tabelle1!$B$2:$B$65535;"gt""");)
so kann die liste erweitert werden ohne das jemand irgendwelche Einstellungen ändern muss
gt ist das größer Zeichen
Danke
yummi

Anzeige
wozu eigentlich so? ...
06.10.2014 16:45:01
neopa
Hallo yummi,
... einen Bereichsnamen sollte man immer nur auf das max. notwendige definieren.
Und das von Dir möglicherweise gewollte (wie ich es interpretiere,) macht in XL2010 die Tabellenfunktion ("Als Tabelle formatieren").
Gruß Werner
.. , - ...

AW: wozu eigentlich so? ...
06.10.2014 17:01:46
yummi
Hallo Werner,
aber bei als Tabelle formatieren kann ich es auch nicht erweitern ohne einstellungen zu ändern oder?
hier mein ansatz :
Datenüberprüfung - Liste -
=BEREICH.VERSCHIEBEN(Tabelle1!$B$2;;;ZÄHLENWENN(Tabelle1!$B$2:$B$65535;"gt""");)
so kann die liste erweitert werden ohne das jemand irgendwelche Einstellungen ändern muss
gt ist das größer Zeichen
Danke
yummi

Anzeige
ist etwas anderes als ursprünglich gefragt ...
06.10.2014 17:19:54
neopa
Hallo yummi,
... zu 1.) die Tabellenfunktion (richtig genutzt) passt sich automatisch an die jeweilige Datensatzanzahl an.
... zu 2) ich nutze BEREICH.VERSCHIEBEN() aus verschiedenen gründen nur da, wo es erforderlich ist (sind sehr wenige Fälle). In dem von Dir aufgezeigten Fall nutze ich das viel einfacher (und nicht volatile) =B2:INDEX(B:B;ANZAHL2(B:B)), vorausgesetzt, dass die Datenwerte ohne Leerzellen gelistet sind. Anderenfalls (Ausnahmefall, setze ich anstelle ANZAHL2() dies: VERWEIS(9;1/(B1:B99"");ZEILE(A1:A99)) ein. (niemand braucht ein Dropdownzelle die hunderte Einträge hat)
Gruß Werner
.. , - ...

Anzeige
AW: ist etwas anderes als ursprünglich gefragt ...
07.10.2014 10:05:12
yummi
Hallo Werner,
zu 1) mal abgesehen, dass ich sie wohl noch falsch nutze (sie passt sich nicht automatisch an neue Einträge an zeigt mir die Dropdownliste dann auch jeweils die Überschriftszeile mit an, was ich ja vermeiden will.
zu 2)wenn ich die von dir angegebenen Formeln in die Datenüberprüfung schreibe, dann bekomme ich nur die Formel angezeigt als dropdown, nicht aber die hinterlegten Werte. Wo muss ich das denn eintragen?
nochmal zusammengefasst: ich möchte in eine Zelle was eingeben, was durch Datenüberprüfung (drropdownliste) vorgegeben ist. Die dropdownliste die angezeigt wird, soll ohne weitere Vorkenntnisse einfach durch anfügen eines Begriffs in der selben Spalte, sich automatisch erweitern.
Gruß
yummi

Anzeige
Bereichsnamen definieren ...
07.10.2014 10:24:58
neopa
Hallo yummi,
... weise z.B. die Formel =$A$2:INDEX($A:$A;ANZAHL($A:$A)) einen Bereichsnamen zu. Nennen wir diesen "Auswahl". Nun weise in der Datengültigkeit für Liste =Auswahl zu. Mehr dazu sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=60 und ff.
Allerdings so wie Du es bisher beschrieben hast, gäbe das für mich nur Sinn, wenn für die Auswahllistendefinition eine eigen Tabelle oder zumindest eine sonst nicht genutzte Spalte nutzt. Wenn Du die selbe Spalte nutzt, würdest Du ja auch doppelte Einträge in die Dropdownliste erhalten.
Gruß Werner
.. , - ...

Anzeige
AW: Bereichsnamen definieren ...
07.10.2014 10:55:32
yummi
Hallo Werner,
ich weiß nicht woran es heute morgen liegt, aber ich bekomm es nicht hin.
Wenn ich deine Formel eingebe und der Zelle einen Namen gebe bekomme ich in der Dropdownliste nicht die Werte angezeigt wie ich möchte.
In der angehängten Beispieldatei habe ich es für das Airbagsteuergerät mal mit Bereich umgesetzt. Wenn du jetzt in Tabelle 1 in Spalte A was hinzufügst erweitert sich die Dropdown liste auf dem 1. Sheet automatisch.
Könntest Du mir das bitte mal für externe Sensoren korrespondierend mit Tabelle 1 Spalte B zeigen? So dass sich das dropdown genauso verhält?
Sry aber irgendwie stehe ich heute auf dem Schlauch.
Danke yummi
https://www.herber.de/bbs/user/92994.xls

Anzeige
na, im Prinzip genau so ...
07.10.2014 11:16:23
neopa
Hallo yummi,
... mit der Formel: =Tabelle1!$B$2:INDEX(Tabelle1!$B:$B;ANZAHL2(Tabelle1!$B:$B)) für den Bereichsnamen:
ExterneSensoren.
Gruß Werner
.. , - ...

AW: na, im Prinzip genau so ...
07.10.2014 12:13:40
yummi
Hallo Werner,
wenn ich das so mache, dann sehe ich in mienem dropdown die 0 die die Formel liefert aber nicht die Begriffe, die in der Spalte stehen
Gruß
yummi

Anzeige
ein Leerzeichen zuviel ...
07.10.2014 12:22:05
neopa
Hallo yummi,
... hab mir noch mal Deine Originaldatei geholt und hier festgestellt, dass Du dort in der Datengültigkeitsdefinition vor =ExterneSensoren ein Leerzeichen zu stehen hast, dass Du natürlich löschen musst.
Gruß Werner
.. , - ...

AW: ein Leerzeichen zuviel ...
07.10.2014 12:29:02
yummi
Hallo Werner,
das ändert aber nix daran, dass wenn ich deine Formel anstatt Externe Sensoren in Tabelle1!B1 eintrage und diese Zelle ExterneSensoren nenne, dass ich dann in meinem Dropdown nur dir 0 sehe, die die Formel von dir liefert.
guckst du: https://www.herber.de/bbs/user/93001.xls
Sry aber mit vba hätte ich es besctimmt längst gelöst, aber in dem fall ist eine lösung ohne vba gewünscht.
Gruß
yummi

Anzeige
die Formel gehört den Bereichsnamen zugeordnet ...
07.10.2014 12:36:40
neopa
Hallo yummi,
... und nicht in die Zelle Tabelle1 B1, was noch dazu einen Zirkelbezug auslöst.
Du hast Dich offensichtlich mit dem StandardExcel und auch nicht mit meinen vorherigen Angaben richtig bekannt gemacht.
Gruß Werner
.. , - ...

AW: ein Leerzeichen zuviel ...
07.10.2014 12:39:44
Daniel
Hi
da hast du die Formel die den Namen definieren soll direkt in die Tabelle (Zelle B1) geschrieben und nicht in die Definition des Namens!
in der Definition des Names steht nur der direkte Zellbezug.
Klicke: FORMELN - NAMESMANAGER
Die Formel, die den Namen definiert musst du dann bei "Bezieht sich auf" eintragen.
Gruß Daniel
Ps du stehts heute ziemlich weit neben dir

Anzeige
AW: ein Leerzeichen zuviel ...
07.10.2014 12:50:27
yummi
Hallo Daniel,
hast wohl recht :-)) aber ich hab jetzt einen schritt zur seite gemacht und steh jetzt wieder in mir ;-)))
Danke

AW: Namensbereich ohne Kopfzeile
06.10.2014 17:10:43
Daniel
Hi
ein Name auf die gesamte Spalte A als Formel funktioniert mit folgender Namensdefinition: 
=Index(Tabelle1!$A:$A;2):Index(Tabelle1!$A:$A;Zeilen(Tabelle1!$A:$A))
dieser Name geht aber tatsächlich bis zur letzten Zeile des Gesamten Blattes und ist damit in der Regel nicht sinnvoll.
hier zwei Definitionen für einen Namen, der von der Zeile 2 bis zur letzten genutzten Zeile reicht.
a) Leerzellen zwsichen erlaubt und möglich: 
=INDEX(Tabelle1!$A:$A;2):INDEX(Tabelle1!$A:$A;VERWEIS(2;1/(Tabelle1!$A:$A"");ZEILE(Tabelle1!$A:$A)) )

b) keine Leerzellen zwischen drin: 
=Index(Tabelle1!$A:$A;2):Index(Tabelle1!$A:$A;Anzahl2(Tabelle1!$A:$A))

wenn die Definition nicht per Formel sonden als fester Zellbereich erfolgen soll, würde ich folgenden Code im Change-Event des Tabellenblatts anlegen: 
ThisWorkbook.Names.Add "DeinName", Range("A2:A" & Cells(Rows.Count, 1).end(xlup).Row)

oder folgendes: 
ThisWorkbook.Names.Add "DeinName", Intersect(Me.Usedrange, Me.Usedrange.Offset(1, 0)).Columns(1) _
Gruß Daniel
ps: Quelle für die Verweisformel:
http://www.excelformeln.de/formeln.html?welcher=48

Anzeige
AW: Namensbereich ohne Kopfzeile
07.10.2014 09:55:27
yummi
Hallo Daniel,
da ich es ohne VBA machen will (es soll auch von Leuten erweitert werden können, die kein VBA können) fallen die letzten beiden Vorschläge raus. Wenn ich die 1. beiden nehme und dies bei DAtenüberprüfung eintrage bekomme ich eine Fehlermeldung, dass man Verweisoperatoren im Kriterium Datenüberprüfung nicht verwenden darf.
Gruß
yummi

Anzeige
AW: Namensbereich ohne Kopfzeile
07.10.2014 10:24:30
Daniel
Hi
dann erstelle erst einen Namen mit dieser Formel und gib dann diesen Namen in der Gültigkeitsprüfung als Liste ein.
Die Gültigkeitsprüfung ist bei Formeln zur Berechnung des Zellbereichs etwas eingeschränkt.
Ist der Zellbereich jedoch als Name angelegt, so kannst du diesen Namen dann in der Gültigkeitsprüfung verwenden.
Gruß Daniel

Anzeige
AW: Namensbereich ohne Kopfzeile
07.10.2014 10:57:51
yummi
Hallo Daniel,
wie schon Werner mitgeteilt, steh ich heute auf dem Schlauch, wenn du willst schau mal ich hab eine Datei hochgeladen.
Danke yummi

AW: Namensbereich ohne Kopfzeile
07.10.2014 11:15:45
Daniel
Hi
was hilft das?
du musst doch einfach nur die Formel für die Berechnung des Zellbereichs nicht direkt in die Definition der Gültigkeitsprüfung eintragen, sondern erst mit dieser Formel einen Namen erstellen und dann diesen Namen in die Gültigkeitpsprüfung eintrangen.
Gruß Daniel

Anzeige
AW: Namensbereich ohne Kopfzeile
07.10.2014 12:04:50
yummi
Hallo Daniel,
das hab ich ja gemacht und der Erfolg: Ich seh den namen des Bereichs nicht die liste
Gruß
yummi

AW: Namensbereich ohne Kopfzeile
07.10.2014 12:24:47
Daniel
Hi
dann hast du wahrscheinlich das Gleichheitszeichen vergessen.
Dass muss davor, damit Excel weiss dass du den Namen meinst und nicht den Text.
du musst bei "Quelle" in der Datenüberprüfung
=DeinName eingeben und nicht nur DeinName
Gruß Daniel

Anzeige
AW: Namensbereich ohne Kopfzeile
07.10.2014 12:30:06
yummi
Hallo Daniel,
ist möglich aber mit = sehe ich nur eine 0
Gruß
yummi

AW: Namensbereich ohne Kopfzeile
07.10.2014 12:33:35
Daniel
dann prüf mal deine Formel.
gehe in den Namenmanager, markiere die Formel und setze den Cursor auf das Eingabefeld "Bezieht sich auf".
Der Zellbereich, den die Formel berechnet, sollte jetzt mit dem laufenden Rahmen gekennzeichnet (der gleiche Rahmen, der auch gerade kopierte Zellen markiert)
Gruß Daniel

Anzeige
jetzt hab ich es
07.10.2014 12:48:36
yummi
Danke euch beiden,
jetzt hab ich es verstanden!
Danke
yummi

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige