Das grosse Archiv mit 640
VBA-freien Formellösungen und Tips
 
   
 
 ÜBER UNS   NEWS   FORMELN   TIPPS   KONTAKT   FORUM   LINKS 
   
Übersicht   Zauberbuch   Rätselbuch   Sparbuch   Bücher-Downloads / Errata  
 
Verweisfunktionen (3) ->

INDIREKT mit relativer Referenzierung (516)
 
 
Aufgabe
Du möchtest Werte aus anderen Tabellenblättern unter variabler Vorgabe des Tabellenblattnamens referenzieren. Dafür wurde die Funktion INDIREKT in der Reinform entwickelt.

Steht der Blattname in A1, und man möchte den Wert aus E1 dieses Blattes auslesen, dann formuliert man:

=INDIREKT($A$1&"!E1")
bzw.
=INDIREKT("'"&$A$1&"'!E1")

Jetzt soll die Formel aber kopierbar sein, dass sich also auch der Zellbezug (hier: E1) relativ verhält (also beim Runterkopieren: E2, E3 etc., bzw. nach rechts F1, G1 etc.)



Lösung
Benötigt man nur die Zeilen relativ, dann kann man mit ZEILE() die Relation erreichen:

=INDIREKT("'"&$A$1&"'!E"&ZEILE(X1))

Alternativ mit der Z1S1-Bezugsart, die man der INDIREKT-Funktion mit dem 2. Parameter 0 bzw. FALSCH entlocken kann:

=INDIREKT("'"&$A$1&"'!Z(0)S5";0)

Nachteil: Man muss den Zeilenversatz (hier: (0) ) explizit aus Sicht der Zelle vorgeben, in der die Formel selbst steht.
Für das genannte Beispiel steht die 1. Formel in Zeile 1 (da das Auslesen auch im anderen Blatt in Zeile 1 - E1 - beginnt).
Zudem sind in dem Beispiel die Spalten (E bzw. S5) fest verdrahtet.

Nachteil 2: Die Formel funktioniert nicht international, da es bei uns ZS (Zeile/Spalte) heißt - im englischen aber RC (Row/Column). Und da es sich innerhalb INDIREKT um einen String handelt, kann dieser nicht automatisch übersetzt werden.

Da es jetzt zudem bei relativen Spaltenbezügen nochmals etwas umständlicher wird, nimmt man am Besten für ALLES die Funktion ZELLE("address";E1) - und zwar bewusst in der englischen Schreibweise ("address") - diese funktioniert auch in den deutschen Versionen.

Also:

=INDIREKT("'"&$A$1&"'!"&ZELLE("address";E1))

verhält sich komplett relativ - und man braucht sonst auf nichts zu achten!




Erläuterung
Für INDIREKT ist es wichtig, einen String zu erzeugen, der EXAKT dem Aufbau eines Bezugs gleicht.
Dabei kann ein Bezug auf ein anderes Blatt so aussehen:

=Tabelle2!E1
oder so
='Tabelle 2'!E1

Kommen im Blattnamen also Leerzeichen oder Sonderzeichen vor, werden noch die Hochkommata ' hinzugefügt.

Da auch Blattnamen ohne Leerzeichen / Sonderzeichen jedoch mit Hochkommata angesprochen werden können, raten wir dazu, diese ' immer hinzuzufügen. Dann braucht man sich später keine Gedanken mehr zu machen.

Um die ' zwischen den Gänsefüßchen " besser identifizieren zu können, haben wir sie farblich hervorgehoben.

=INDIREKT("'"&$A$1&"'!"&ZELLE("address";E1))

 
    zurück


 
powered by telltarget