PDA

Vollständige Version anzeigen : Name von Namensfeld in Zelle daneben


CreatorX
18.04.2009, 10:01
Hallo, also ich möchte in A1 den Namen stehen haben der in B1 als Namensfeld definiert ist. In B1 steht z.B 0.5 als Wert und diese 0.5 sind mit dem Namen "Wert_1" definiert, also möchte ich das in A1 "Wert_1" steht. Wie geht das? Danke.

jinx
18.04.2009, 10:13
Moin, CreatorX,

ohne VBA: in Zelle A1 gehen und folgenden Bezug bei Namen eingeben:

=ZELLE.ZUORDNEN(41;INDIREKT("ZS(1)";FALSCH))
Bei mir verwendeter Name:Formel_Zelle. Bezieht sich immer auf die Zelle rechts von der Zelle, in der der Name verwendet wird, sollte ggf. um das Gleichheitszeichen gekürzt werden...

CreatorX
18.04.2009, 10:29
Tut mir leid, das verstehe ich nicht so ganz bzw. ich weiß nicht wie ich die Formel anpassen muss. Wenn ich sie einfach als Namensfeld in A1 definiere/reinkopiere (so hab ich es jedenfalls verstanden) bekomme ich die Fehlermeldung, dass ich einen gültigen Zellbezug oder einen gültigen Namen für den markierten Bereich angeben soll, mit oder ohne Gleichheitszeichen. Wenn ich sie einfach nur in die Zelle A1 reinkopiere passiert gar nichts, mit oder ohne Gleichheitszeichen.

jinx
18.04.2009, 12:28
Moin, CreatorX,

siehe Mappe im Anhang - HTH.

<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >=Wert_1</td><td style="text-align:right; ">0,5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >A1</td><td >=Formel_Zelle</td></tr><tr><td >B1</td><td >=Wert_1</td></tr></table></td></tr></table> <br />

CreatorX
19.04.2009, 09:22
Hallo jinx und sorry, so lange ich auch suche ich kann einfach keine Mappe als Anhang finden :) und dein, im Text gepostetes Beispiel, hilft mir leider so nicht weiter, da ich daraus nicht erkennen kann wie ich die Formel anwenden kann. Tut mir leid wenn ich dir bis jetzt nicht so ganz folgen kann, obwohl ich echt versuche es zu verstehen.

jinx
19.04.2009, 09:42
Moin, CreatorX,

da war offensichtlich die Absicht vorhanden, aber die Ausführung nachweislich ungenügend - jetzt aber (hoffentlich...).

Möglicherweise habe ich Dein Anliegen auch ganz falsch verstanden (wenn es um das Auslesen eines ggf. auch größeren Bereiches für einen Namen geht), dann wäre mir nur eine Lösung per VBA bekannt.

CreatorX
19.04.2009, 10:57
Ja jetzt hab ich den Anhang gleich gefunden ;)
Aber scheint so, als wäre das nicht ganz das was ich eigentlich wollte, zumal ich die Namen wohl anders deviniere, könnte wohl damit zusammenhängen, dass wir unterschiedliche Excel-Versionen haben (ich hab 2003). Ich versuche mal es etwas genauer zu erklären:

Also als erstes gebe ich in B1 den Wert ein.
http://img24.imageshack.us/img24/7986/scr1v.jpg

Danach definiere ich im Namenfeld
http://img17.imageshack.us/img17/36/scr2r.jpg

den Namen
http://img17.imageshack.us/img17/255/scr3zuf.jpg

der sich dann genau auf diese Zelle bezieht
http://img155.imageshack.us/img155/5457/scr7.jpg

und schon erscheint automatisch in A1 der Name der im Namenfeld von B1 definiert wurde:
http://img4.imageshack.us/img4/1341/scr4f.jpg

Und um das ganze noch weiter zu verdeutlichen, jetzt mal für B2 und A2 mit anderem Namen:
http://img17.imageshack.us/img17/5130/scr5e.jpg
http://img17.imageshack.us/img17/4784/scr6t.jpg

Ich hoffe das ist so möglich, auch ohne VBA, denn es soll schon weitesgehend automatsich passieren und nicht erst wenn ich ein Makro ausführe.

jinx
19.04.2009, 11:41
Moin, CreatorX,

aus Green/Bullen/Bovey/Alexander: Excel 2007 VBA Programmer´s Reference:

Sub TestNameOfRange()
Dim nmName As Name

'See if range has a name

'Ignore errors
On Error Resume Next

'Try to get name
Set nmName = Selection.Name

'Display result
If nmName Is Nothing Then
MsgBox " Selection has no name"
Else
MsgBox nmName.Name
End If

End Sub
<hr>
Sub SelectionEntirelyInNames()
Dim sMessage As String
Dim nmName As Name
Dim rngNameRange As Range
Dim rng As Range

'List all names that entirely contain
'the selected cells

'Ignore errors
On Error Resume Next

'Look at all names in workbook
For Each nmName In Names

'Start with nothing & try to assign range
Set rngNameRange = Nothing
Set rngNameRange = nmName.RefersToRange

'If successful, we have a range reference
If Not rngNameRange Is Nothing Then

'See if range is in active sheet
If rngNameRange.Parent.Name = ActiveSheet.Name Then

'See if selection is in range
Set rng = Intersect(Selection, rngNameRange)
If Not rng Is Nothing Then

'See if range is entirely in selection
If Selection.Address = rng.Address Then
sMessage = sMessage & nmName.Name & vbCr
End If

End If

End If

End If

Next nmName

'Displaymessage
If sMessage = "" Then
MsgBox "The selection is not entirely in any name"
Else
MsgBox sMessage
End If

End Sub
<hr>
Sub NamesOverlappingSelection()
Dim sMessage As String
Dim nmName As Name
Dim rngNameRange As Range
Dim rng As Range

'List all names that overlap
'the selected cells

'Ignore errors
On Error Resume Next

'Look at all names in workbook
For Each nmName In Names

'Start with nothing & try to assign range
Set rngNameRange = Nothing
Set rngNameRange = Range(nmName.Name)

'If successful, we have a range reference
If Not rngNameRange Is Nothing Then

'See if range is in active sheet
If rngNameRange.Parent.Name = ActiveSheet.Name Then

'See if selection overlaps range
Set rng = Intersect(Selection, rngNameRange)

If Not rng Is Nothing Then

sMessage = sMessage & nmName.Name & vbCr

End If

End If

End If

Next nmName

'Displaymessage
If sMessage = "" Then
MsgBox "The selection is not entirely in any name"
Else
MsgBox sMessage
End If

End Sub

Drogist
19.04.2009, 20:09
Ich hoffe das ist so möglich, auch ohne VBA, denn es soll schon weitesgehend automatsich passieren und nicht erst wenn ich ein Makro ausführe.
Dann bietet sich doch eine Funktion an:
Function BereichsName()
Dim sName As Name
Dim rZelle As Range
Dim sBerName As String
Dim Rc As String

Set rZelle = ActiveCell.Offset(0, 1)
Rc = ""

For Each sName In ThisWorkbook.Names 'Schleife über alle Namen
If Not Intersect(sName.RefersToRange, rZelle) Is Nothing Then
Rc = sName.Name
Exit For
End If
Next 'sName

BereichsName = Rc
Set sName = Nothing
Set rZelle = Nothing
End Function

Function BereichsName2(DiffZeile As Integer, DiffSpalte As Integer)
Dim sName As Name
Dim rZelle As Range
Dim sBerName As String
Dim Rc As String

Set rZelle = ActiveCell.Offset(DiffZeile, DiffSpalte)
Rc = ""

For Each sName In ThisWorkbook.Names 'Schleife über alle Namen
If Not Intersect(sName.RefersToRange, rZelle) Is Nothing Then
Rc = sName.Name
End If
Next 'sName

BereichsName2 = Rc
Set sName = Nothing
Set rZelle = Nothing
End Function

Die erste Funktion (Bereichsname) wird immer den Bereichsnamen aus der Zelle rechts verwenden, wie du es wolltest. Die 2. Funktion (Bereichsname2) ist da etwas flexibler. Du brauchst 2 Argumente, nämlich Zeile und Spalte. Beides sind numerische Werte, es kann Plus oder Minus verwendet werden. Aaus Gründen der Übersichtlichkeit habe ich kein Errorhandling eingebaut, du bist also selber dafür verantwortlich, dass du in A1 keine Minus-Werte eingibst.

Grüße
Drogist

CreatorX
20.04.2009, 08:39
Danke jinx, das funktioniert zwar, jedenfalls so dass ich nun weiß wie ich per VBA an die Namen komme aber darauf werde ich als letzte Möglichkeit zurückkommen da es eben VBA ist.

@Drogist: Das ist schon ganz gut, allerdings aktualliesiert sich das ganze nicht selbständig. Wenn ich also in A1 die Funktion "=BereichsName()" eingebe, gibt mir A1 auch den Namen von B1 aus. Wenn ich den Namen in B1 aber wieder ändere/lösche usw. bleibt in A1 allerdings alles unverändert, erst wenn ich in die Bearbeitungsleiste klicke und dort Enter drücke aktualisiert sich A1 wieder. Geht das auch selbsständig? Und zur Funktion BereichsName2: Wie setzte ich Zeile und Spalte in der Klammer richtig ein?

jinx
20.04.2009, 08:54
Moin, CreatorX,

vielleicht erklärst Du mir bitte mal den Unterschiede zwischen VBA und einer benutzerdefinierten Funktion? Auch die ist VBA - meiner bescheidenen Kenntnis nach jedenfalls.

Was genau willst Du eigentlich erreichen? Mir ist der Sinn der Übung, eine Zelle zu benennen und dann diese Benennung zu verändern nicht klar. Kennzeichnet eine benutzerdefinierte Funktion als flüchtig. Solche Funktionen werden immer neu berechnet, wenn in einer beliebigen Zelle des Tabellenblattes eine Berechnung durchgeführt wird. Nicht flüchtige Funktionen werden nur dann neu berechnet, wenn sich die Eingabevariablen ändern. Diese Methode wird in der Regel in einer benutzerdefinierten Funktion zur Berechnung einer Tabellenzelle verwendet, andernfalls hat sie keine Auswirkung.

Syntax

Ausdruck.Volatile(Volatile)

Ausdruck Erforderlich. Ein Ausdruck, der ein Application-Objekt zurückgibt.

Volatile Variant optional. True, um die Funktion als flüchtig zu kennzeichnen. False, um die Funktion als nicht flüchtig zu kennzeichnen. Die Voreinstellung ist True.
In diesem Fall also Application.Volatile - mit allen möglichen Nachteilen...

CreatorX
20.04.2009, 09:16
Nunja, über die genaue Definitien weißt du sicher mehr bescheid als ich und ich will mich da auch gar nicht rumstreiten und das eine Funktion auch in VBA geschrieben ist, mag alles richtig sein. Was ich erreichen möchte ist das sich die A-Spalte automatisch aktualisiert sobald ich den Namen eine B-Zelle definiere/ändere/lösche ohne das ich dabei irgend ein Makro auführen muss (darunter verstehe ich, einen weiteren Button oder ähnliches zu betätigen). Es kann ja möglich sein, dass dies mit VBA genauso möglich ist, dann bin ich mir dessen aber nicht bewußt und daher möchte ich es auf diese weise lösen. Und mir war auch bisher nicht bewußt, dass sich der Name eine Zelle im Verlauf eines Projektes nicht wieder ändern dürfe, denn aus speziellen Gründen ändern sich diese Namen bei mir nun mal immer wieder.

Edit: Mit "allen möglichen Nachteilen" meinst du sicher das das Blatt nun ständig neuberechnet wird. Hab grad ein wenig gesucht und bin auf Calculate gestoßen, eventuell sollte ich das mal ausprobieren.

Drogist
21.04.2009, 20:54
@Drogist:[..] Wenn ich den Namen in B1 aber wieder ändere/lösche usw. bleibt in A1 allerdings alles unverändert, erst wenn ich in die Bearbeitungsleiste klicke und dort Enter drücke aktualisiert sich A1 wieder. Geht das auch selbsständig?
Ja, zum Thema "VOLATILE" ist ja schon dieses oder jenes gesagt worden. Ich mag das nicht besonders. Aber du hast ja auch schon die Taste [F9] erwähnt, die geht natürlich auch. (Oder meintest du die Funktion CALCULATE?) Und selbstverständlich werden die aktuellen, korrekten werte eingesetzt, wenn du die (geschlossene) Mappe wieder öffnest.

Und zur Funktion BereichsName2: Wie setzte ich Zeile und Spalte in der Klammer richtig ein?
OK, die Funktionalität ist ja gleich wie vorher, nur dass du hier Werte eingeben musst. Angenommen, du bist in B2 dann bieten sich folgende Argumente/Parameter an:
BereichsName2(-1,0) -> Zielzelle Zeile - 1, Spalte -0 also -> B1 wird ausgewertet.
BereichsName2(1,1) -> Zielzelle Zeile + 1, Spalte +1 also -> C3 wird ausgewertet.
BereichsName2(3,-1) -> Zielzelle Zeile + 3, Spalte -1 also -> A5 wird ausgewertet.
BereichsName2(-3,-1) -> Zielzelle Zeile - 3, Spalte -1 also -> #FEHLER wird ausgegeben, denn Zeile2 - 3 -> -1 und die Zeile gibt es nicht. Wie gesagt, die Fehlerprüfung hatte ich raus gelassen.

mfg
Drogist