Wie ändert man den Excel SVerweis Bezug?
Ich habe eine Excel-Tabelle die mit mehreren Sverweisen gefüllt ist. Fast jede Zelle enthält ein Bezug zu einem anderen Arbeitsblatt. Ich würde gerne den Bezug auf ein anderes Arbeitsblatt ändern, möchte aber ungern jede einzelne Zelle bearbeiten um den Bezug zu ändern. Gibt es eine Möglichkeit den Bezug automatisch für alle Zellen zu ändern?
3 Antworten
wenn es immer der gleiche Bereich ist, auf den die Formeln zugreifen, und andere Formeln nicht auf diesen Gesamtbereich zugreifen (sondern nur auf einzelne Zellen oder Teilbereiche), dann per Suchen und Ersetzen (Strg+H)
Wähle nur eine Zelle aus (irgendeine, aber einzelne) Dort in Optionen Durchsuchen:"Arbeitsmappe" und "..in" "Formeln" auswählen.
Die Adresse des jetzigen Bereichs -zB Tabelle3!$B$2:Z$1234 in suchen dann in ersetzen durch neuen Bereich eintragen, zB Blatt4!$D$5:$AF$1256
Das Beispiel hier ändert nicht nur das BezugsBlatt, auch die Leitspalte von B nach D und erweitert den Verweisbereich sowohl in Spalten wie in Zeilen (Du kannst natürlich auch nur eins oder zwei von allem ändern, solltest aber immer den gesamten Bezugsstring suchen&ersetzen, sonst könnte das auch andere Formeln verändern!)
Vergiss aber nicht die $-Zeichen, damit Du die Formeln problemlos kopieren kannst!
"in Formeln" ersetzt übrigens ebenfalls in Texten, sofern in den Zellen keine Formel vorhanden, aber zB als Text fürs backup hinterlegt ist.
Du kannst sogar auch nur einzelne Holspalten ersetzen, wenn Du das nächste Argument miteinbeziehst: Tabelle3!$B$2:Z$1234;5; durch Blatt7!$D$5:$AF$567;6; , das ändert die Bezugsspalte des einen Verweisbereichs auf eine andere in einem Anderen (zB partielles update, aber noch unvollständige Liste: gibt aber #NV aus für die noch nicht vorhandenen)
Ich empfehle Dir übrigens, noch eine Dummy-Zeile mit zB 999999(=größte Zahl als Suchargument) anzulegen, diese miteinzubeziehen und dann jede neue Zeile durch Zeile einfügen oberhalb anzulegen: die $-Zeilenbezüge erweitern sich dann automatisch in allen Formeln (natürlich auch relative, es ist aber meist eher kontraproduktiv, solche anzulegen)
Kopiere Dir Diese Antwort in ein Textfeld (Menü einfügen, Textfeld), dann hast du's in xl sicht- und greifbar.
Hi oomee,
falls sich in der Datei die Tabellenblätter/Verweise oft ändern, solltest du prüfen, ob die Umstellung auf Bereichsnamen sinnvoll ist:
1. Gehe zum Tabellenblatt der Quelle (Matrix), in dem der Sverweis sucht.
2. Markiere den Gesamten Matrixbereich auf den der Sverweis zugreifen soll (z.B. A2:D500).
3. Klicke auf den Button Formeln -> Definierte Namen -> Namen definieren
4. Im unteren Bereich ist die Matrix korrekt angegeben, z.B. Tabelle2!$A$2:$D$500, im oberen Bereich legst du einen Namen fest (z.B. sverweisquelle) - bestätigen mit ok.
5. Wechsele auf das Tabellenblatt mit den Sverweisen, markiere in der Formel die Matrix (z.B. Tabelle2!$A$2:$D$500), lösche den Wert und drücke F3 - jetzt kannst du den Namen sverweisquelle auswählen.
Der Vorteil dadurch: Bei der nächsten Änderung brauchst du nur noch den Bereich des Bereichsnamens zu ändern, die einzelnen Formeln werden dann automatisch "umgehangen". Dies ist mit mehreren unterschiedlichen Bereichsnamen möglich. Ein weiterer Vorteil: wenn du mit den Formeln anzahl2 und index (nicht jedoch bereich.verschieben oder indirekt, da dies volatile Funktionen sind, die du vermeiden solltest) rumspielst, kannst du einen dynamischen Bereich bauen, der automatisch länger wird, wenn die "sverweisquelle" neue Datensätze hinzubekommt.
Grüße
Weasel
Also wenn auf einem Blatt die ganzen Verweise sind, die zum nächsten Blatt führen und du dann einfach willst, dass sich diese beispielsweise auf das übernächste Blatt ändern, dann könnte es eventuell mit "Suchen und Ersetzen" funktionieren.
Wenn die bisherige Formel beispielsweise so aussieht:
=SVERWEIS(Tabelle2!A2;E:F;2;FALSCH)
nimmst Du das übliche Suchen/Ersetzen. Du musst überlegen, welcher Teil der Formel immer ersetzt werden kann. Bei dieser hier Suchen nach
=SVERWEIS(Tabelle2!A und ersetzen durch
=SVERWEIS(Tabelle3!A
Die sich von Zelle zu Zelle ändernden Teile der Formel muss man weglassen, sonst werden ja nicht alle Formeln gefunden, die zu ändern sind.
Ich brauche bitte eine Lösung mit einzelnen Schritten!