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!!
3 Antworten
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.
Klar.
=WENN(INDIREKT("Tabelle2!B"&ZEILE(9:9));INDIREKT("Tabelle2!B"&ZEILE(9:9));"")
(quick'n'dirty nach Ortogonn-Manier)
WAS funktioniert denn nicht? Falscher Wert, #NV!, #Bezug!, ...?
=WENN(ISTLEER(INDIREKT("Tabelle2!B"&ZEILE(9:9)));"";INDIREKT("Tabelle2!B"&ZEILE(9:9)))
ich meinte eigentlich: Was erscheint? Falscher Wert, #NV!, #Bezug!, ...?
Die formel von deedee07 ist einfach falsch. Falls das eine Frage beantwortet.
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.
Hier kann schwer ein wahr oder falsch resultieren, oder nicht? INDIREKT("Tabelle2!B"&ZEILE(9:9))
@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!
oder nicht. Tatsächlich ist es sogar recht verrückt. http://excelformeln.de/formeln.html?welcher=250
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...
@ 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.
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.
Die Adresse()-Formeln haben den Vorteil, dass sie sowohl Zeile als auch Spalte über eine Vergleich()-Wertesuche finden können
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.
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.
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.
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?