Frage von Allebasi2018, 186

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

Hallo ich brauche eure Hilfe!

Ich habe in einem Tabellenblatt "Tab 1" eine allgemeine List erstellt, auf welche sich die weiteren Tabellenblätter beziehen sollen.

Tab.1: Spalte B: Nummer (1,2,3,usw.), Spalte C Firmenname und Spalte D Ansprechpartner (Werte beginnen bei Zeile 9: B9 = 1 ; B10 = 2; B11=3 usw.)

Im Tabellenblatt 2 hätte ich gerne den Wert von Zelle B9, darunter den Wert der Zelle B10 usw. allerdings möchte ich die Option offen lassen, dass in Tabelle 1 Zeilen gelöscht werden können. Z.B. Löschen Zeile 10 mit der Nr. 2 dann wären die Werte: B9 = 1, B10 = 3; B11 = 4 usw.)

Welche Formel muss ich eingeben, damit ich auch wenn Zeilen gelöscht oder hinzugefügt werden immer den Wert in Zelle B9 erhalte? (ohne die Meldung #Bezug!)

Danke im Voraus für die Beantwortung!!

Antwort
von vitus64, 92

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.

Expertenantwort
von DeeDee07, Community-Experte für Excel, 114

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.

Kommentar von Allebasi2018 ,

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?

Kommentar von DeeDee07 ,

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

(quick'n'dirty nach Ortogonn-Manier)

Kommentar von Allebasi2018 ,

Diese Formel funktioniert leider nicht... 

Kommentar von Iamiam ,

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

Kommentar von Ghanasoccer ,

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

Kommentar von Iamiam ,

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

Kommentar von Ghanasoccer ,

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

Kommentar von DeeDee07 ,

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.

Kommentar von Ghanasoccer ,

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

Kommentar von DeeDee07 ,

oder nicht. Tatsächlich ist es sogar recht verrückt. http://excelformeln.de/formeln.html?welcher=250

Kommentar von Iamiam ,

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!)

Kommentar von 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!

Kommentar von Ghanasoccer ,

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...

Kommentar von Iamiam ,

@ 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.

Expertenantwort
von Iamiam, Community-Experte für Excel, 86

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.

Kommentar von Iamiam ,

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

Kommentar von 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.

Kommentar von 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.

Expertenantwort
von Iamiam, Community-Experte für Excel, 60

Falsch, versehentlich abgeschickt!

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten