Office: Verwenden von Namen in Verweisformel durch Berechnen / Excel 2010 englisch

Hallo Forum,
ich habe folgende funktionsfähige Formel konstruiert:
=HLOOKUP(H_Key;Daten_200;VLOOKUP(Key;Daten_200;2;FALSE)+1;FALSE)
(in deutsch=WVERWEIS(H_Key;Daten_200;SVERWEIS(Key;Daten_200;2;FALSE)+1;FALSE)

H_Key ist ein Überschriftenbereich in der Zieltabelle der als Schlüssel verwendet wird und in dem mit Namen "Daten_200" bezeichneten Tabellenbereich dort die Überschrift liest und mit dem Key aus der Quelltabelle den Satz mit der gleichen Überschrift aus der Spalte ließt. Der Name Daten_200 ist in der Quelltabelle manuell angelegt und markiert Daten der Gruppe 200. Nun haben ich noch weitere Gruppen gebildet welche die Namen Daten_100, Daten_300, Daten_400 usw. tragen. Der nummerische Wert hinter dem Text Daten_ bildet einen Hauptschlüssel welcher den jeweiligen Datenblock als Hauptgruppe kennzeichnet. Dieser steht in dieser Haupttabelle in Spalte A als Zahl. Nun wollte ich mittels Formel den Namen Daten_xxx durch eine Formel in der Formel bilden. Ich habe sämtliche mit bekannten Funktionen; Text(A1), Verketten("=Daten";A1), Indirekt, etc. ausprobiert. Insgesamt hole ich etwa 8000 Datensätze in 317000 Zellen in die Zieltabelle.
Ich habe nun 10 verschiedene Formelm zum abholen der Daten und würde diese gern auf eine reduzieren.

(in deutsch=WVERWEIS(H_Key;Daten_100;SVERWEIS(Key;Daten_200;2;FALSE)+1;FALSE)
(in deutsch=WVERWEIS(H_Key;Daten_200;SVERWEIS(Key;Daten_200;2;FALSE)+1;FALSE) usw.

(in deutsch=WVERWEIS(H_Key;[Funktion];SVERWEIS(Key;[Funktion];2;FALSE)+1;FALSE) [Funktion] bildet den vergebenen Namen durch Berechnen/Zusammensetzten aus dem Text "=Daten"&($a1) nach.

Vielen Dank schon mal für eure Hilfe.
Gruß
Bernd

 

Hallo! Eigentlich sollte dies anstelle von [Funktion] reichen: INDIREKT("Daten_"&A1) wenn in A1 ein entsprechender Wert (100, 200, 300, ...) steht.

 

Hallo Schatzi,

dies habe ich natürlich schon ausprobiert. Excel meldet hierzu #Ref!

Bernd

 

Da es sich bei dem Konstrukt um einen =Namen handelt dachte ich ein "=" davor würde es bringen. Aber auch dies trifft nicht zu.
Bernd

 

Hallo! #REF! bedeutet "Reference", also einen Bezugsfehler. Excel findet also den zusammengesetzten Namen nicht. Oft liegt das an einem simplen Tippfehler. Hast du an den Unterstrich gedacht? Oder verbergen sich irgendwo noch (unsichtbare) Leerzeichen?

 

Hallo Schatzi,

solche Tippfehler vermeide ich von vornherein in dem ich mir die Formel in getrennter Zelle aufbaue und das Ergebnis teste:
="Daten_"&(TEXT(Index;0)) = Daten_800 und dann die Formel per Kopie einsetzte.
Die Formel:
=HLOOKUP(H_Key;indircet("Daten_"&(TEXT(Index;0)));VLOOKUP(Key;Daten_800;2;FALSE)+1;FALSE)
meckert "#NAME?", findet also den zusammengebauen Namen nicht. Ersetze ich indircet wieder durch Daten_800 funktioniert die Referenz wieder.
das Projekt wir übrigens im Kompatibilitätsmodus geführt. Aber auch das temporäre umstellen auf 2010 bringt kein Ergebnis. Index ist mein Name auf die Spalte A in der die Zahlen 100-900 stehen.

Vielleicht hast Du ja noch eine Idee.

PS. Habe die indirekt Funktion nochmal untersucht. So wie beschreiben funktioniert sie auch. Auf eine namenlich benannte Zelle wird zugegriffen. Wenn ich allerdings versuche in der SVerweis Formel den Namen der benannten Matrix zusammenzubauen funktioniert der Befehl indirekt(..) definitiv nicht. Vielleicht gibt es ja doch noch einen Trick um innerhalb der Verweisformeln eine indirekte Adressierung zu bauen.

Gruß
Bernd

 

Hallo!

Nein! Diese Formel gibt #NAME! zurück, weil du einen Buchstabendreher bei "indircet" hast! Bei einem nicht gefundenen Namen würde Excel #REF! zurückgeben.
Soviel also zum Thema

Bei meinen Tests funktionierte mein Vorschlag einwandfrei, und ich sehe eigentlich auch keinen Grund, weshalb er NICHT funktionieren sollte.

Wenn du kannst, dann hänge mal eine Beispielmappe an; gerne mit anonymisierten Daten, aber mit den original Bereichsnamen.

 

Hallo Schatzi,

du hast Recht, den Tippfehler habe ich tatsächlich handisch eingebaut (schäm). Macht vielleicht die geringe Übung mit der Englischen Version. Nach dem idiotischen Fehler in der zusammengebaute Textformel funktioniert nun auch bei mir nun die indirekte Adressierung innerhalb der Verweisformel. Vielen Dank für Deine Aufmerksamkeit beim lesen. Ich werde meine Tabelle gleich umbauen. Das hilft mir ungemein.

Beste Grüße und vielen, vielen Dank
Bernd