Office: (Office 2013) Zellbezug auf benannten Bereich

Hallo zusammen,
Ich würde gerne auf einen Bereich mit Namen verweisen, und zwar gezielt auf eine bestimmte Zeile, die beim Runterziehen dann auch hochzählt.
A1:A15 kriegt den Namen "source" und ich will in "D7*biggrin.gif*9" auf "A4:A6" verweisen, dabei aber den Bereichsnamen verwenden

Tiefere Erklärung:
ich habe sehr großes Workbook mit um die 50 Tabellenblätter in der u.a. Kopierbereiche für ein Import-Tool generiert werden.
So gibt es z.B. auf einem Blatt einen Bereich, in dem 20 Zeilen Geräte aufgelistet werden. Die zweite Spalte enthält damit den ersten Teil des String für den Kopierbereich.
Standort.Extraktionspumpe_TypA_Nr1 ...TypA_Nr20

Auf Blatt zwei gibt es dann eine Liste mit Parametern dieses Gerätetyps (Druck, Leistung, Fördemenge, Status,...)
Auf Blatt drei sollen die jetzt zuammengesetzt werden zu sowas wie:

Standort.Extraktionspumpe_TypA_Nr1.Druck
Standort.Extraktionspumpe_TypA_Nr1.Leistung
...
Standort.Extraktionspumpe_TypA_Nr20.Status

Das funktioniert alles, ist so gewachsen, habe ich von meinem Vorgänger übernommen und steht nicht zur Disposition.
Ich muss dieses Workbook aber hin und wieder ausbauen und erweitern, und ich finde es sehr mühsam, die ganzen Verweise auf "$A$1:$D$125" nachzuvollziehen. Lieber würde ich auf Blatt 1 den Namen "Gerät" für alle Zeilen in Spalte B vergeben, und "Parameter" für den entsprechenden Bereich auf Blatt 3.
Und dann mit =Gerät(Zeile1desBereiches) & Parameter(Zeile5desBereiches)
benutzen, dann wäre viel klarer, was hier genau gemacht wird.
Jetzt kann ich ja problemlos A1:A10 den Namen "source" geben, und in D5 reinschreiben "=source" und das dann bis D14 runterziehen. Allerdings verweise D5 dann nicht auf die erste Zeile des Bereichs Target, sondern eben auf den 5.

:)

 

Hi,

benutze die INDEX-Funktion: =INDEX(source;5;1) - hier ist Zeile 5 angesprochen.

 

Danke, aber da Zählt die Zeile nicht hoch. Wie kann ich das bauen? Sowas wie index(source; Zeile - "offsetzeile";1)

Habe mir derweil mal was in VBA gebastelt. Oder geht das auch mit Hausmitteln?

Function ThisColumn() As Integer
ThisColumn = Application.Caller.Column
End Function

Function ThisRow() As Integer
ThisRow = Application.Caller.row
End Function

Function FromRange(rng As Range, Optional row As Integer = 1, Optional col As Integer = 1) As Variant
If rng(row, col).Value = "" Then FromRange = "" Else FromRange = rng(row, col).Value
End Function

in der Zellen dann entsprechend:

=FromRange(source; ThisRow() - 2)

 

Die Zeile kannst du doch dynamisch machen indem du die Funktion ZEILE() verwendest:

=INDEX(source;ZEILE(A1);1) - 1. Zeile
=INDEX(source;ZEILE(A2);1) - 2. Zeile
=INDEX(source;ZEILE(A3);1) - 3. Zeile
....

 

Nochmal etwas überarbeitet

Function FromRange(rngsrc As Range, rngstart As Range, Optional ofrow As Integer = 0, Optional ofcol As Integer = 0) As Variant
Dim iRow As Integer
Dim iCol As Integer
iRow = 1 + ThisRow - rngstart.row + ofrow
iCol = 1 + ThisColumn - rngstart.Column + ofcol
If rngsrc(iRow, iCol).Value = "" Then FromRange = "" Else FromRange = rngsrc(iRow, iCol).Value
End Function

Ich gebe ein =FromRange(Bereichsname; Startzelle)
Startzelle wäre "B3" wenn ich ich von B3:B12 den Inhalt der ersten 10 Zeilen von "Bereichsname" ausgeben will. Optional kann ich ein Offset angeben, wenn ich nicht ganz oben oder ganz links in "Bereichsname" anfangen will.

 

Hallo,

wieso fragst Du nach "Hausmitteln" und ignorierst dann Karins Hinweis?

Wenn es VBA sein muss:
Code:

 

Sorry, wollte nichts Ignorieren. Bei der Variante mit Index stört mich in meinem Anwendungsfall, dass ich dann doch wieder das Tabellenblatt wechseln muss um die erste Zelle des Quellbereich rauszusuchen. Denn es sind so viele Blätter dass man sich da leicht vertut. Die Bereichsnamen hingegen vergebe ich jetzt nach einem Schema und die sind schön eindeutig und gut zu handeln.
Vielen Dank auch für den Verbesserungsvorschlag mit Offset 0, habe meine Funktion angepasst.
Noch eine Frage zu VBA: Bei den Excel-Funktionen wie VERKETTEN kommt ein "Tooltip" welche Parameter erwartet werden. Kann ich diese bei VBA-Funktionen auch bekommen?

 

Hallo,

musst Du nicht. Die erste Zelle ist immer die Index(...;1;1), so, wie es Karin auch geschrieben hat.

Grüße
EarlFred

 

Du liest die Beiträge öffensichtlich nicht richtig: ich hatte doch ganz eindeutig geschrieben, wie man auf den per Namen definierten Bereich mittels INDEX-Funktion zugreifen kann - sowohl mit einer fixen Zeilenangabe als auch dynamisch, sodass sich die Formel nach unten ziehen lässt...

 

Ich lese sie sehr wohl, ich hatte aber etwas falsch verstanden. Ich dachte, "A1" müsste jeweils an die jeweilige Zelle angepasst werden. Dass A1 dann immer die erste Zelle des Bereiches anspricht, egal wo dieser auf dem Blatt liegt hatte ich nicht gesehen. Vielen Dank für die Antworten, jetzt habe ich tatsächlich zwei Varianten *Smilie