Verweisen auf Dynamische Benannten Bereich in Excel-Formel

Ich habe eine Tabelle in Excel mit den Spalten-überschriften, die entsprechen, um Teil eines dynamischen benannten Bereich an anderer Stelle in meiner Arbeitsmappe. Zum Beispiel habe ich diese Spalte überschriften: "10", "20", etc., und diese dynamische benannte Bereiche: "ExampleRange10", "ExampleRange2", etc. Ich würde gerne geben Sie eine SVERWEIS-Formel, die Verweise ExampleRange10 durch die Verkettung der Zeichenfolge "ExampleRange" und die überschrift der Spalte "10". Dies würde mir erlauben, einfach erweitern Sie die Formel über alle Spalten in der Tabelle, statt der manuellen Eingabe von "ExampleRange10", "ExampleRange20", etc. in jeder Spalte die Formel.

Ich bin mir bewusst, dass die INDIREKTE Funktion, und habe es erfolgreich in der Vergangenheit mit benannten Bereichen, aber es scheint nicht zu funktionieren mit der dynamische benannten Bereich in diesem Fall. Ich vermute, dies ist eine nuance, die etwas damit zu tun hat, wie dynamische benannte Bereiche definiert werden, die von Excel (Sie nicht zeigen, bis in den Bereich mit dem Namen dropdown auf der linken Seite der Formel bar, und Sie haben einige interessante Eigenschaften, die in VBA zum Beispiel). Gibt es eine Möglichkeit, die ich nutzen kann die INDIREKT-Formel in Verbindung mit einer dynamischen benannten Bereich, oder gibt es einen anderen Weg, den ich gehen kann, um die Lösung für dieses problem?

Bearbeiten: Hier sind die genauen Formeln verwendet.

Dies ist die wichtigste Formel: =VLOOKUP(B2,INDIRECT("ExampleRange"&C1),2,FALSE) wobei C1 enthält "10" und die Formel für meine dynamische benannten Bereich namens "ExampleRange10" ist: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$25),2). Die wichtigsten Formel liefert "#REF!", aber es funktioniert richtig, wenn ich entfernen Sie die dynamische benannte Bereich Formel und definieren einfach "ExampleRange10" als eine statische Reichweite.

Können Sie bitte die GENAUE Formel, die nicht funktioniert?
Könnte man vielleicht verwenden Sie eine Kombination von offset() und Spalte(), abhängig von Ihrer genauen layout.
Sehen Sie auf Bearbeiten.
=VLOOKUP(B5,INDIRECT("range"&C1),1,0) funktioniert gut für mich in Excel 2007. Sind Sie sicher, dass der rest der Formel ist kein Fehler, z.B. 3. parameter - Spalte Zahl im Bereich zurückzukehren?
Huh. Und Ihre "range10" dynamisch ist? Ich habe doppelt überprüft meine Formeln und Bereichen, und ich konnte nicht einen Fehler finden. Wenn ich nach dem Namen-Manager und klicken Sie in der "Bezieht sich auf" Feld, sowohl den dynamischen Bereich und die statische Reichweite highlight der gleichen region, also denke ich nicht das Problem mit dem OFFSET-Formel für den dynamischen Bereich...und wie ich bereits erwähnt habe, die genau die gleiche SVERWEIS-Formel funktioniert, wenn ich Ersatz für die Anfang der statische Reichweite text für "ExampleRange", das ist der Anfang-text für den dynamischen Bereich, so dass die SVERWEIS-Formel scheint zu funktionieren auch...
du hast Recht - die doppelte Dynamik, die nicht arbeiten, in der Tat... vielleicht intermediate Zelle, wo Sie erhalten "ExampleRange"&C1 für weitere indirekte ist eine option?
Das habe ich versucht, aber ohne Glück. Es sieht aus wie das zugrunde liegende Problem ist, dass INDIRECT funktioniert einfach nicht mit dynamische benannte Bereiche... ich bin derzeit auf der Suche in das schreiben einer benutzerdefinierten Funktion in VBA dieses Problem zu umgehen. Siehe link

InformationsquelleAutor Kyle Wurtz | 2013-02-13

6

Als besten würde ich sagen, nachdem ich die weitere Forschung, Excel INDIRECT Funktion einfach nicht funktioniert mit dynamischen Bereichen. Es könnte ein cleverer Weg, um zu bekommen, um mit INDIRECT und das festhalten an der nicht-VBA-Excel-Welt, aber ich bin mir nicht bewusst so. Stattdessen landete ich erstellen eine benutzerdefinierte Funktion, die sehr ähnlich zu der beschriebenen hier. Ich veränderte meine wichtigsten Formel zu Lesen =VLOOKUP(B2,DINDIRECT("ExampleRange"&C1),2,FALSE), wo DINDIRECT ist der name des VBA-Funktion, die ich erstellt.

Nur Nachteile (die möglicherweise oder möglicherweise nicht werden Nachteile je nachdem wie man es betrachtet) zu dieser alternative ist, dass die Arbeitsmappe muss gespeichert werden, der als makro-Arbeitsmappe und die Verwendung einer benutzerdefinierten Funktion ist nicht sehr selbsterklärend und erfordert ein wenig Erklärung, um andere Benutzer. Alle Sachen betrachtet, obwohl, war dies eine akzeptable Lösung für mich.

Für den link abgeneigt, hier der code:
```
Public Function DINDIRECT(sName As String) As Range
     Dim nName As Name

     On Error Resume Next
          Set nName = ActiveWorkbook.Names(sName)
          Set nName = ActiveSheet.Names(sName)
     On Error GoTo 0

     If Not nName Is Nothing Then
          Set DINDIRECT = nName.RefersToRange
     Else
          DINDIRECT = CVErr(xlErrName)
End Function
```
Hinweis: Obwohl diese Lösung funktionierte, werde ich nicht akzeptieren meine Antwort, weil ich nicht wollen, zu entmutigen, andere von der Buchung bessere Lösungen. Auch ich bin neu auf der Seite, also sorry, wenn ich bin gegen alle Etikette-codes durch die Beantwortung meiner eigenen Frage...ich dachte, ich würde teilen, die genau die Lösung, die ich verwendet, wenn andere es nützlich finden.

InformationsquelleAutor Kyle Wurtz
5

Traf ich exakt diese Mauer erst vor kurzem und die Antwort wie Sie schon erraten haben, ist einfach, dass man nicht Bezug auf dynamische benannte Bereiche mit INDIREKT.

Sie können jedoch den Dynamikbereich der Formel sich als INDIREKTE argument, aber das ist keine Verwendung für das, was Sie tun möchten. Etwas wie ein PITA, da es die Art von Funktionalität, die sehr nützlich sein würde.
- Danke für die Bestätigung, dass INDIRECT funktioniert nicht mit dynamisch benannten Bereichen. Ich von ganzem Herzen Zustimmen, dass diese Funktion nützlich wäre, und es ist besonders frustrierend, wenn man berücksichtigt, wie einfach die Problemumgehung code ist (so sollte es sehr einfach sein, für Sie zu übernehmen), und wie oft das problem kommt (zumindest für mich).
- Genau. Es ist doppelt frustrierend, wenn Sie haben zu kommen mit einer Lösung, die nicht den Einsatz von UDFs (oder VBA in irgendeiner Weise), wie ich bin oft erforderlich, um zu tun.
InformationsquelleAutor blackworx
1

Wenn Ihr Daten haben überschriften wie 10, 20 usw., dann brauchen Sie nicht zu verwenden Indirekt. Warum nicht einfach eine Index/Match wählen Sie die Daten, die Sie brauchen?

Name, den Sie die ganze Tabelle ExampleRanges zum Beispiel, und verwenden Sie diese Formel:
```
Index(ExampleRanges, match(B2, index(ExampleRanges, , 1), 0), match(C1, index(ExampleRanges, 1,), 0))
```
InformationsquelleAutor Cool Blue
0

Ungetestet, aber ich denke, dass dies funktionieren würde:

benutzerdefinierte Funktion geben Sie die Adresse Ihres dynamisch benannte Bereich:
```
Function Named_Range_Address(Range_Name As Range, _ 
    Optional SheetName As Boolean) As String 

    Dim strName As String 
    Application.Volatile 

    If SheetName = True Then 
        strName = "'" & Range_Name.Parent.Name & "'!" & Range_Name.Address 
    Else 
        strName = Range_Name.Address 
    End If 

    Named_Range_Address = strName 
End Function 
```
dann sollten Sie in der Lage sein zu verwenden Ihre SVERWEIS-Formel:
```
=VLOOKUP(B2,INDIRECT(named_range_address("ExampleRange"&C1,TRUE)),2,FALSE)
```
- Danke für die Anregung. Sah ich eine Funktion wie diese irgendwo, wenn ich war googlen, aber diese Funktion setzt das erste argument vom Typ Range, aber da bin ich verkettet die strings "ExampleRange" und der text in C1, habe ich ein argument vom Typ String. Jedoch, Ihren Vorschlag zu verwenden Sie eine benutzerdefinierte Funktion, die richtig ist. Ich finde hat eine andere Funktion, die RangeName As String hier, und das funktionierte gut für mich.
InformationsquelleAutor scott
0

Ich weiß, das ist ziemlich alt, aber ich kam gerade über diese und dachte, ich würde hinzufügen, eine Lösung, die vermeidet, dass alle VBA-coding, falls es hilft niemandem, stolpert Sie über diese:
```
=VLOOKUP(B2,CHOOSE(C1/10,example10,example20,example30,example40),2,0)
```
Dies ist unter der Annahme der Namenskonvention wird 10,20,30,usw und nicht ideal für Hunderte von Bereichen.

InformationsquelleAutor Dave
0

Ich war heute basteln mit Excel benannte Bereiche, und ich entdeckte, dass, während es ist wahr, dass Sie nicht berechnen kann der name des Bereichs in der INDIRECT() Anruf selbst, können Sie es in einer reinen "Excel-Weg", indem ein Zwischenschritt: erstellen Sie einfach einige versteckte Zelle, in der Sie berechnen den Bereich mit dem Namen.

Zum Beispiel, sagen, dass A1 enthält den "dynamischen Teil" der Reihe Namen, dann in A2 verwenden Sie die Formel = "ExampleRange" & A1, und jetzt haben Sie die vollständige Palette Namen, die Sie verwenden können, wie = INDIRECT(A2).
- Es ist nicht so sehr der name an sich dynamisch, das ist das problem, es ist, wenn der Bereich auf den er sich bezieht, ist dynamisch.
InformationsquelleAutor Matteo Tassinari
0

Hinzufügen eine neue Wendung, es ist möglich, verwenden Sie einen benannten Bereich, mit der Adresse und Indirekten Funktionen. Ich habe einen Fall, wo ich bin Einstellung namens reicht für eine Reihe von Tabellen, und bin mit dem folgenden:
```
Named Range: WWDH-FF-PI which points to Linear!$A$19 (first cell in table)
```
um die Adresse zu bekommen:
$T$56: =ADRESSE(VERGLEICH(S56,Linear!A:Ein,0),1,1,1,"Linear")

Dann mit der offset-Funktion kopiert mehrere Male, um erstellen Sie eine pivot-Tabelle:
```
=OFFSET(INDIRECT($T$56),C5,$T$57-1)
```
Also die Adresse-Funktion eingebettet werden können (oder verpackt) in die Indirekte Funktion zu erstellen, die eine dynamische Zelle Adresse.
- Das funktioniert, aber leider tritt das problem mit dynamische benannte Bereiche, dh einen benannten Bereich, der sich auf eine variable Anzahl von Zellen, in der Regel abhängig von Ihrem Inhalt.
InformationsquelleAutor rgo

Ich weiß, das ist ein wirklich Alter thread, aber ich hatte das gleiche Problem, so vielleicht meine Lösung kann den Menschen helfen, in der Zukunft.

Grundsätzlich habe ich ein Makro erstellt, dass würde das löschen und neu-definieren Sie den Bereich auf speichern, und geben Sie ihm einen Namen. Daher die INDIREKTE-Funktion funktionieren würde, wie die Auswahl war nicht dynamisch. Alles, was Sie tun müssen, ist, speichern Sie die Arbeitsmappe nach jedem hinzufügen von Werten zu den benannten Bereichen,

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim a, b, c, d, e, f As Integer
Dim data As Worksheet

Set data = ThisWorkbook.Worksheets("Data")

a = data.Range("A" & Rows.count).End(xlUp).row
b = data.Range("B" & Rows.count).End(xlUp).row
c = data.Range("C" & Rows.count).End(xlUp).row
d = data.Range("D" & Rows.count).End(xlUp).row
e = data.Range("E" & Rows.count).End(xlUp).row
f = data.Range("F" & Rows.count).End(xlUp).row



ActiveWorkbook.Names("KP").Delete
ActiveWorkbook.Names("KPT").Delete
ActiveWorkbook.Names("AP").Delete
ActiveWorkbook.Names("APT").Delete
ActiveWorkbook.Names("DISC").Delete
ActiveWorkbook.Names("SEATS").Delete

ActiveWorkbook.Names.Add Name:="KP", RefersTo:="=Data!$A$2:$A$" & a
ActiveWorkbook.Names.Add Name:="KPT", RefersTo:="=Data!$B$2:$B$" & b
ActiveWorkbook.Names.Add Name:="AP", RefersTo:="=Data!$C$2:$C$" & c
ActiveWorkbook.Names.Add Name:="APT", RefersTo:="=Data!$D$2:$D$" & d
ActiveWorkbook.Names.Add Name:="DISC", RefersTo:="=Data!$E$2:$E$" & e
ActiveWorkbook.Names.Add Name:="SEATS", RefersTo:="=Data!$F$2:$F$" & f

End Sub

InformationsquelleAutor DavePenn

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.