Excel: Wie kann ich den Zellbezug von einem anderen Tabellenblatt beibehalten auch wenn ich Zeilen lösche oder hinzufüge?

3 Antworten

DeeDee07
Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer
Microsoft Excel
23.03.2016, 20:03

Das kannst du mit dem INDIREKT-Befehl machen. Also etwa
=INDIREKT("Tabelle2!B9")

Da der Bezug ein String ist, verweist er in diesem Beispiel immer auf Tabelle2!B9, egal ob Zeilen eingefügt, gelöscht, verschoben usw. werden.

Der Nachteil ist, dass die Formel nicht kopiert werden kann, d.h. sie passt sich nicht an. Falls du das dennoch haben willst, kannst du z.B.
=INDIREKT("Tabelle2!B"&ZEILE(9:9))
verwenden.
Ggf. kann der häufige Einsatz von INDIREKT eine Arbeitsmappe langsam machen.
Allebasi2018 
Beitragsersteller
 23.03.2016, 20:16

Super! Vielen Dank!

Kann man die Formel =INDIREKT("Tabelle2!B"&ZEILE(9:9)) 
auch noch mit einer Wenn Formel verknüpfen, dass die Zelle leer bleibt wenn in Tab. 1 kein Wert steht?

DeeDee07  23.03.2016, 20:24
@Allebasi2018

Klar.
=WENN(INDIREKT("Tabelle2!B"&ZEILE(9:9));INDIREKT("Tabelle2!B"&ZEILE(9:9));"")

(quick'n'dirty nach Ortogonn-Manier)

Iamiam  23.03.2016, 21:54
@Allebasi2018

WAS funktioniert denn nicht? Falscher Wert, #NV!, #Bezug!, ...?

Ghanasoccer  24.03.2016, 10:25
@DeeDee07

=WENN(ISTLEER(INDIREKT("Tabelle2!B"&ZEILE(9:9)));"";INDIREKT("Tabelle2!B"&ZEILE(9:9)))

Iamiam  24.03.2016, 14:52
@Ghanasoccer

ich meinte eigentlich: Was erscheint? Falscher Wert, #NV!, #Bezug!, ...?

Ghanasoccer  24.03.2016, 17:03
@Iamiam

Die formel von deedee07 ist einfach falsch. Falls das eine Frage beantwortet.

DeeDee07  24.03.2016, 17:18
@Ghanasoccer

Die Frage von IamIam war, WAS daran falsch ist, bzw. welches falsche Ergebnis daraus resultiert. Im Schnelltest hat sie übrigens funktioniert.

Es geht nicht um persönliche Eitelkeitsverletzung (ich mach nen Haufen Fehler), sondern um die Möglichkeit, Hilfe zur Korrektur anbieten zu können. Dabei ist aber weder "funktioniert nicht" noch "einfach falsch" hilfreich.

Ghanasoccer  24.03.2016, 17:28
@DeeDee07

Hier kann schwer ein wahr oder falsch resultieren, oder nicht? INDIREKT("Tabelle2!B"&ZEILE(9:9))

Iamiam  24.03.2016, 17:29
@DeeDee07

für normale Blattnamen stimmt sie m.E. Durch das Leerzeichen im Blattnamen ist lediglich die Einbettung in Hochkommata nötig!

(ich hab mich allerdings auch lange damit rumgeärgert, bis mir das aufgegangen ist!)

Iamiam  24.03.2016, 17:53
@Iamiam

@Ghanasoccer: WAHR ist jede Zahl, die <>0 ist

schreibe mal i'wo die Formel =WENN(A1;WAHR;FALSCH) und trage in A1 ein:

1 => WAHR

0,1=> WAHR

25=> WAHR

-25=> WAHR

leer lassen=> FALSCH

0=> FALSCH

Text=>#WERT

=1/0=> #Div/0!

Fazit: Unterstelle den Beantwortern hier, die eine längere positive Tradition haben, nicht, dass sie Unsinn schrieben.

Irrtümer gibts allerdings haufenweise, und man sollte seine Funktion durchaus darin sehen, diese positiv aufzuklären!

Ghanasoccer  24.03.2016, 18:55
@Iamiam

Ich weiß jetzt nicht was ich sagen soll. Ich stelle euch nocheinmal einen Abschnitt der Fragestellung zur Verfügung:

Tab.1: Spalte B: Nummer (1,2,3,usw.), Spalte C Firmenname und Spalte D Ansprechpartner

Nach diesem Abschnitt geht er dem Beispiel der Zahlen nach, dennoch sind die anderen Spalten Thema.

Ich hätte weniger grob sein sollen. Ich weiß dass auch Zahlen, wenn sie keine leerzeichen haben WAHR sein können.

Entschuldigung...

Iamiam  25.03.2016, 01:50
@Ghanasoccer

@ Ghanasoccer: vermutlich hast Du recht. Wir haben uns beide auf die Zahlenspalte konzentriert und die anderen Spalten vernachlässigt.

Das sollte aber ohne Probleme aus meiner letzten Formel ableitbar sein: Wennfehler(....*1;..) geht bei Texten natürlich nicht, sollte aber in dieser Kombi-Konstellation gar nicht nötig sein. Und Kopierbar wird die Formel, wenn man die 9 der Zeile wieder durch die Formel Zeile(9:9) ersetzt, die mit Zeile(9:9) von DeeDee ist von vornherein nach unten kopierbar.

Für zunächst mal Ungetestet nochmal (mit allen Argumenten):

=WENN(ISTLEER(INDIREKT(ADRESSE(Zeile(9:9);Spalte();4;1;"[Zwi16.xlsm]Tab 1")));"leer!";INDIREKT(ADRESSE(Zeile(9:9);Spalte();4;1;"[Zwi16.xlsm]Tab 1")))

Spalte() ist die Spalte der Formel, so dass man in Spalte B das Ergebnis aus Tab 1!B bekommt, in Spalte C aus Tab 1!C usw.

Werde mir das aber morgen nochmal ansehen.
Iamiam
Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer
Microsoft Excel
24.03.2016, 15:37

Hier viele getestete Formeln:

wobei ich nicht weiß, warum die anderen nicht funktionieren sollen, bei mir tun sie! allerdings kann es sein, dass Du den Blattnamen zwischen 'Hochkommata' setzen musst, wenn er anderes als Standardbuchstaben/Zahlen enthält(Leerzeichen!):

=WENNFEHLER(INDIREKT("'Tab 1'!B"&ZEILE(9:9))*1;"")

=WENNFEHLER(INDIREKT("'Tab 1'!B"&ZEILE(N9))*1;"")

sogar: =WENNFEHLER(INDIREKT("'Tab 1'!B"&SPALTE(i:i))*1;"")     (i=9)

+Datei: =WENNFEHLER(INDIREKT("'[Zwi16.xlsm]Tab 1'!"&"$B$9")*1;"")

mit ADRESSE(Zeile;Spalte;Absolut-Typ[1-4];Bezugsart;Blattname):

=WENNFEHLER(INDIREKT("'Tab 1'!"&ADRESSE(9;2))*1;"")

=WENNFEHLER(INDIREKT(ADRESSE(9;2;;;"Tab 1"))*1;"")

=WENNFEHLER(INDIREKT(ADRESSE(9;2;;;"[Zwi16.xlsm]Tab 1"))*1;"")

in den beiden letzteren keine Hochkommata!

Alle Formeln liefern jedoch bei leeren Zellen 0. Die Null kannst Du aber per Zellformat ausblenden, wobei ich aber ein eher unauffällig machen bevorzuge, so dass man weiß, dass die Zelle nicht leer ist:

Strg+1, Karte Zahlen, Kategorie Benutzerdefiniert, dort eintragen:

#.##0 ;[Rot]-#.##0;[Farbe7]°

Willst Du es doch leer, dann einfach anstatt des ° ein "" [Farbe7] ist dann ü'flüssig

Allerdings erscheinen Dir dann auch aus der abgefragten Zelle "gezogene" Nullen ununterscheidbar genauso. Um das zu vermeiden, müsste man nach dem gleichen Prinzip noch ein =wenn(istleer(indirekt(...));"Leer";"")& davorsetzen (was andernfalls eine Zahl zum Label machen würde?), das führe ich jetzt aber nicht mehr aus.
Iamiam  24.03.2016, 15:47

Die Adresse()-Formeln haben den Vorteil, dass sie sowohl Zeile als auch Spalte über eine Vergleich()-Wertesuche finden können

Iamiam  24.03.2016, 17:23
@Iamiam

Hier noch eine Formel (von vielen möglichen), die auch Leer und Eintragswert 0 unterscheidet:

=WENN(ISTLEER(INDIREKT(ADRESSE(9;2;;;"[Zwi16.xlsm]Tab 1")));"leer!";WENNFEHLER(INDIREKT(ADRESSE(9;2;;;"[Zwi16.xlsm]Tab 1"))*1;"."))

Ob das in allen denkbaren Konstellationen funktioniert, hab ich nicht mehr getestet.

Iamiam  25.03.2016, 10:48
@Iamiam

Habe mich nach dem Einwurf von Ghanasoccer nochmals damit beschäftigt.

Möglicherweise tut es eine viel einfachere Formel, gültig für Abfrageformel an selber Stelle im anderen Blatt, (also lt Deiner Vorgabe) in B9:

=INDIREKT(ADRESSE(ZEILE();SPALTE();4;1;"Tab 1"))

Für andere Formel-Positionen kann man das durch Korrekturglieder anpassen, zB für Position der Formel in C2:

=INDIREKT(ADRESSE(ZEILE()+7;SPALTE()-1;4;1;"Tab 1"))

Die Formel ist in jede Richtung kopierbar(solange Zeile/Spalte >=1 bleiben), die kopierten Zellen geben -versetzt- ein genaues Abbild des Ausgangsblattes wieder.

;4;1; sind optionale Argumente, die man weglassen kann (aber nicht die ; !). "Tab 1" wieder ohne Hochkammate.
vitus64
23.03.2016, 20:32

Das kann man mit Matrixformeln lösen. Hierbei werden nicht einzelne Zellen, sondern ganze Zeilen, Spalten oder Matrizen rechnerisch verknüpft. Das ganze muss man sich zwar erstmal aneignen; eine Erklärung, wie das genau geht, würde hier den Rahmen sprengen, es bietet aber eine Reihe von Vorteilen.