Microsoft Excel

Herbers Excel/VBA-Archiv

Namensbereich ohne Kopfzeile

Betrifft: Namensbereich ohne Kopfzeile von: yummi
Geschrieben am: 06.10.2014 16:12:47

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 ;-)

  

Betrifft: AW: Namensbereich ohne Kopfzeile von: Martin
Geschrieben am: 06.10.2014 16:38:50

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


  

Betrifft: Kleiner Denkfehler... von: Martin
Geschrieben am: 06.10.2014 16:45:35

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


  

Betrifft: AW: Kleiner Denkfehler... von: yummi
Geschrieben am: 06.10.2014 17:00:09

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


  

Betrifft: wozu eigentlich so? ... von: neopa C (paneo)
Geschrieben am: 06.10.2014 16:45:01

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
.. , - ...


  

Betrifft: AW: wozu eigentlich so? ... von: yummi
Geschrieben am: 06.10.2014 17:01:46

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


  

Betrifft: ist etwas anderes als ursprünglich gefragt ... von: neopa C (paneo)
Geschrieben am: 06.10.2014 17:19:54

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
.. , - ...





  

Betrifft: AW: ist etwas anderes als ursprünglich gefragt ... von: yummi
Geschrieben am: 07.10.2014 10:05:12

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


  

Betrifft: Bereichsnamen definieren ... von: neopa C (paneo)
Geschrieben am: 07.10.2014 10:24:58

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
.. , - ...




  

Betrifft: AW: Bereichsnamen definieren ... von: yummi
Geschrieben am: 07.10.2014 10:55:32

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


  

Betrifft: na, im Prinzip genau so ... von: neopa C (paneo)
Geschrieben am: 07.10.2014 11:16:23

Hallo yummi,

... mit der Formel: =Tabelle1!$B$2:INDEX(Tabelle1!$B:$B;ANZAHL2(Tabelle1!$B:$B)) für den Bereichsnamen:
ExterneSensoren.


Gruß Werner
.. , - ...


  

Betrifft: AW: na, im Prinzip genau so ... von: yummi
Geschrieben am: 07.10.2014 12:13:40

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


  

Betrifft: ein Leerzeichen zuviel ... von: neopa C (paneo)
Geschrieben am: 07.10.2014 12:22:05

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
.. , - ...


  

Betrifft: AW: ein Leerzeichen zuviel ... von: yummi
Geschrieben am: 07.10.2014 12:29:02

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


  

Betrifft: die Formel gehört den Bereichsnamen zugeordnet ... von: neopa C (paneo)
Geschrieben am: 07.10.2014 12:36:40

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
.. , - ...


  

Betrifft: AW: ein Leerzeichen zuviel ... von: Daniel
Geschrieben am: 07.10.2014 12:39:44

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


  

Betrifft: AW: ein Leerzeichen zuviel ... von: yummi
Geschrieben am: 07.10.2014 12:50:27

Hallo Daniel,

hast wohl recht :-)) aber ich hab jetzt einen schritt zur seite gemacht und steh jetzt wieder in mir ;-)))

Danke


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: Daniel
Geschrieben am: 06.10.2014 17:10:43

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


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: yummi
Geschrieben am: 07.10.2014 09:55:27

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


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: Daniel
Geschrieben am: 07.10.2014 10:24:30

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


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: yummi
Geschrieben am: 07.10.2014 10:57:51

Hallo Daniel,

wie schon Werner mitgeteilt, steh ich heute auf dem Schlauch, wenn du willst schau mal ich hab eine Datei hochgeladen.


Danke yummi


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: Daniel
Geschrieben am: 07.10.2014 11:15:45

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


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: yummi
Geschrieben am: 07.10.2014 12:04:50

Hallo Daniel,

das hab ich ja gemacht und der Erfolg: Ich seh den namen des Bereichs nicht die liste


Gruß
yummi


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: Daniel
Geschrieben am: 07.10.2014 12:24:47

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


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: yummi
Geschrieben am: 07.10.2014 12:30:06

Hallo Daniel,

ist möglich aber mit = sehe ich nur eine 0

Gruß
yummi


  

Betrifft: AW: Namensbereich ohne Kopfzeile von: Daniel
Geschrieben am: 07.10.2014 12:33:35

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


  

Betrifft: jetzt hab ich es von: yummi
Geschrieben am: 07.10.2014 12:48:36

Danke euch beiden,

jetzt hab ich es verstanden!


Danke
yummi


 

Beiträge aus den Excel-Beispielen zum Thema "Namensbereich ohne Kopfzeile"