Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1904to1908
1904to1908
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Hochkommer in Indirekt

Forumthread: Hochkommer in Indirekt

Hochkommer in Indirekt
05.11.2022 23:31:36
Simon
Hallo zusammen,
Ich habe ein Problem, das ich nicht lösen kann. Hier der Hintergrund: Ich habe eine Tabelle (Vll. das falsche Wort), in der auf eine Zelle in verschiedenen Tabellenblättern verwiesen wird.
So soll es aussehen, nur dass "Tabellenblatt 1" variabel gestaltet sein soll: ='Tabellenblatt 1'!$F$2
Da die Beschriftungen der Tabelle lassen sich zu diesem Namen zusammensetzen. Insoweit habe ich folgendes Ergebnis erzielt:
=VERKETTEN(INDIREKT(ADRESSE(ZEILE();SPALTE($CW$67)));" ";INDIREKT(ADRESSE(ZEILE($CY$45);SPALTE()))) --> Diese Formel gibt mir den korrekten Tabellennamen aus.
Jetzt scheitere ich leider daran, die beiden Formel zusammenzuführen. Vor allem das Hochkomma macht mir dabei Probleme, da die Formel nicht mehr als Formel erkannt wird, wenn ich es einfüge. Allerdings brauche ich das Hochkomma doch in der resultierenden Formel, da ich zwangsläufig Leerzeichen drin habe.
Kann mir jemand helfen?
Vielen Dank und Grüße
Simon
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Hochkommata extra verketten!
06.11.2022 04:41:52
Luc:-?
Evtl so, Simon:
=VERKETTEN("'";INDIREKT(ADRESSE(ZEILE();SPALTE($CW$67)));" ";INDIREKT(ADRESSE(ZEILE($CY$45);SPALTE()) );"'!F2")
…falls es nur um die BlattBezeichnung ging und die ZellAdresse fix ist.
Morhn, Luc :-?
AW: Hochkommata extra verketten!
06.11.2022 06:32:35
Chris
Ungetestet, fehlt da nicht noch ein ' vor !F2? Ich sehe nur 4, müssten es nicht 5 sein? Sorry falls ich falsch liege.
Anzeige
Ich mache es mal größer
06.11.2022 07:42:07
RPP63
Moin Chris!
Ich habs mal vergrößert und markiert.
Ich sehe keinen Fehler:

Gruß Ralf
AW: Ich mache es mal größer
06.11.2022 08:50:13
Chris
Hallo Ralf,
sorry,jetzt sehe ich es auch.
Gruß
Chris
AW: Hochkommata extra verketten!
06.11.2022 10:49:22
Simon
Hallo Luc,
die Formel funktioniert fast. Wenn ich noch ein "="; vorne anfüge, bekomme ich das richtige Ergebnis in die Zelle. Leider aber nicht als Formel, sondern als Text. Dort steht jetzt einfach " ='Tabellenblatt 1'!F2 ", ohne dass sie wirklich auf das Tabellenblatt zugreift.
Kann man da irgendwie tricksen und die Zelle mit der Formel im Formelformat abspeichern (gibt es so etwas)? Oder kann die Verkettenfunktion einfach keine Formel ausspucken?
Anzeige
AW: Hochkommata extra verketten!
06.11.2022 11:10:04
Simon
Ich aktiviere mal das Kontrollkästchen. Vll. sieht so noch jemand anderes die Frage und kann weiterhelfen. Würde mich freuen!
Richtig, Simon: ...
06.11.2022 13:25:06
Luc:-?
…1. =-Zeichen,
2. Text (aus Formel [Fml]).
Solange die VERKETTEN-Fml in der Zelle steht, ist das nur ein Text! Du musst sie noch mit ihrem Ergebnis überschreiben (Kopieren - Werte einfügen), wenn das eine fktionierende Fml wdn soll. Alternativ kannst du sie auch mit VBA gleich als Fml eintragen unter Sheets("zielblattname").Range("zieladresse").FormulaLocal (deutsch) oder …Formula (englisch).
Luc :-?
Anzeige
AW: Richtig, Simon: ...
06.11.2022 15:10:18
Simon
Hallo Luc,
Vielen Dank für deine Hilfe bisher. Leider funktioniert es immer noch nicht. Es kann aber gut sein, dass ich deine letzte Nachricht nicht richtig verstanden habe. Ich habe jetzt diese Formel:
=VERKETTEN("=";"'";INDIREKT(ADRESSE(ZEILE();SPALTE($CW$67)));" ";INDIREKT(ADRESSE(ZEILE($CY$45); SPALTE()) );"'!F2")
, die mir das hier ausspuckt: ='Tabellenblatt 1'!F2
Wenn ich die Zelle mit der Formel kopiere und dann als Formel einfüge, erhalte ich wiederum nur einen Zelleintrag in der Form von oben. Was mich sehr wundert, da ich eigentlich dachte, dass ein Zellinhalt nicht mit "=" anfangen darf. Das gleiche passiert, wenn ich versuche, als Wert einzufügen.
In meinem VBA Code habe ich Folgendes eingefügt. Die Variable "letzte2" findet mir die letzte Zeile von unten in meiner Tabelle, in der die Formel eingetragen werden soll.

.Range(.Cells(letzte2 - 1, "CY"), .Cells(letzte2, "CY")).FormulaLocal = "=VERKETTEN(" = ";""'"";INDIREKT(ADRESSE(ZEILE();SPALTE($CW$67)));"" "";INDIREKT(ADRESSE(ZEILE($CY$45);SPALTE()) );""'!F2"")"
Wenn ich den Code laufen lasse, steht in der betreffenden Zelle nur "FALSCH".
Ich habe mal eine Beispieldatei erstellt. https://www.herber.de/bbs/user/156046.xlsm Selbst wenn ich die Formel in einer Stringvariablen speichere, spuckt mir VBA "FALSCH" aus, was ich überhaupt nicht verstehe. Sollte das nicht einfach das ausspucken, was ich innerhalb der Anführungszeichen geschrieben habe?
Ich hoffe du (ihr) versteht mehr.
Vielen Dank und Grüße
Simon
Anzeige
AW: Richtig, Simon: ...
06.11.2022 15:58:03
Daniel
Hi
Beim Kopieren und Einfügen als Wert eines Formelergebnisses macht Excel keine Typprüfung.
Dh. es übernimmt das Formelergebnis so wie es ist und prüft nicht, ob der eingefügte Text eine Zahl oder eine Formel sein könnte.
Damit das passiert, musst du erst einmal in die Zelle klicken und Enter drücken (also den Wert neu eingeben) oder mit den Zellen ein ERSETZEN ausführen (z.b. "=" durch "=" ersetzen), denn nach dem Ersetzten führt Excel diese Typprüfung und Umwandlung durch.
Wenn du diesen Zellbezug sofort berechnet haben willst, wäre die Formel:

=Indirekt(Verketten("'";Tabelleblattname;"'!";Zelladresse))
Gruß Daniel
Anzeige
AW: Richtig, Simon: ...
06.11.2022 16:17:48
Simon
Hallo Daniel,
das macht Sinn, danke. Leider funktioniert es immer noch nicht ganz. Ich möchte den Tabellennamen ja abhängig von den Beschriftungen der Tabelle "zusammensetzen" lassen. Diese Formel gibt mir den gewünschten Tabellennamen aus:

=VERKETTEN(INDIREKT(ADRESSE(ZEILE();SPALTE($CW$67)));" ";INDIREKT(ADRESSE(ZEILE($CY$45);SPALTE()))) 


=Indirekt(Verketten("'";Tabelleblattname;"'!";Zelladresse))
, also 


=Indirekt(Verketten("'";Tabellenblatt 1;"'!";F2))
Wenn ich versuche, die beiden Formeln zu kombinieren, kommt nur der Fehler #BEZUG!

=INDIREKT(VERKETTEN("'";(VERKETTEN(INDIREKT(ADRESSE(ZEILE();SPALTE($CW$67)));""; INDIREKT(ADRESSE(ZEILE($CY$45);SPALTE()))));"'!";"F2"))
Mache ich noch etwas falsch? Hast du vll. noch eine Idee? Ich glaube, ich bin dicht dran...
Gruß Simon
Anzeige
AW: Richtig, Simon: ...
06.11.2022 16:22:39
Simon
Hallo nochmal,
Vielen, vielen Dank für den Hinweis. Es hat jetzt doch funktioniert. Ich habe nur ein Leerzeichen vergessen, weshalb er das Tabellenblatt nicht gefunden hat.
nochmal vielen Dank an alle und einen schönen Abend noch!
Simon
;
Anzeige
Anzeige

Infobox / Tutorial

Hochkomma in Indirekt verwalten

Schritt-für-Schritt-Anleitung

  1. Formel zum Zusammensetzen: Beginne mit der Formel, die den Tabellennamen dynamisch erstellt. Hier ist ein Beispiel, wie Du das machen kannst:

    =VERKETTEN(INDIREKT(ADRESSE(ZEILE();SPALTE($CW$67)));" ";INDIREKT(ADRESSE(ZEILE($CY$45);SPALTE())))

    Diese Formel gibt Dir den gewünschten Tabellenname mit Leerzeichen.

  2. Hochkomma hinzufügen: Um Hochkommas zu integrieren, kannst Du die Formel wie folgt erweitern:

    =VERKETTEN("'";INDIREKT(ADRESSE(ZEILE();SPALTE($CW$67)));" ";INDIREKT(ADRESSE(ZEILE($CY$45);SPALTE())));"!F2")

    Hierbei wird das Hochkomma um den Tabellenblattnamen eingefügt.

  3. Formel als tatsächliche Formel nutzen: Wenn Du die Formel in eine Zelle eingibst, wird sie möglicherweise als Text angezeigt. Um dies zu umgehen, kannst Du das Gleichheitszeichen = an den Anfang der Formel setzen und sicherstellen, dass die Zelle als Formel formatiert wird.

Häufige Fehler und Lösungen

  • Formel wird als Text angezeigt: Wenn Du die Formel in die Zelle einfügst und sie als Text erscheint, überprüfe, ob Du das Gleichheitszeichen am Anfang hast. Außerdem kann es helfen, die Zelle zu aktivieren und Enter zu drücken, um die Typprüfung auszulösen.

  • Fehler #BEZUG!: Dieser Fehler tritt auf, wenn ein Zellbezug ungültig ist. Stelle sicher, dass alle Teile der Formel korrekt zusammengesetzt sind. Ein typisches Problem ist das Fehlen eines Leerzeichens oder eines Hochkommas.

Alternative Methoden

Es gibt mehrere Wege, um Hochkomma in eine Formel zu integrieren:

  1. Verwendung von VBA: Mit VBA kannst Du die Formel direkt in eine Zelle einfügen, was die Problematik mit der Typprüfung umgeht. Beispiel:

    Sheets("zielblattname").Range("zieladresse").FormulaLocal = "=VERKETTEN("'" & Tabellenblattname & "'!F2"

  2. Zellinhalt ersetzen: Eine andere Möglichkeit ist, die Formel als Text in eine Zelle einzufügen und dann die Typprüfung durch die Excel-Funktion ERSETZEN auszulösen.

Praktische Beispiele

Hier sind einige praktische Beispiele, um Hochkomma in Indirekt zu verketten:

  1. Zelladresse und Tabellenname kombinieren:

    =INDIREKT(VERKETTEN("'" & 'Tabellenblatt 1' & "'!F2"))

    Dies setzt den Tabellenblattnamen und die Zelladresse korrekt zusammen.

  2. Dynamische Zellreferenz:

    =INDIREKT(VERKETTEN("'" & 'Tabellenblatt " & A1 & "'!" & B1))

    Hier wird der Tabellenname und die Zelladresse dynamisch aus den Zellen A1 und B1 generiert.

Tipps für Profis

  • Verwende Namensbereiche: Anstatt direkt auf Zelladressen zuzugreifen, kannst Du auch Namensbereiche verwenden. Das macht die Formeln übersichtlicher und weniger fehleranfällig.

  • Debugging: Nutze die Formelüberprüfung in Excel, um Fehler schnell zu identifizieren. Dies kann hilfreich sein, wenn Du komplexe Formeln mit INDIREKT und VERKETTEN verwendest.

  • Makros nutzen: Wenn Du häufig ähnliche Formeln benötigst, erstelle ein Makro, das diese Formeln automatisch generiert und einfügt.

FAQ: Häufige Fragen

1. Wie kann ich verhindern, dass die Formel als Text angezeigt wird?
Stelle sicher, dass die Formel mit einem Gleichheitszeichen beginnt und die Zelle als Standard oder Zahl formatiert ist.

2. Was tun, wenn ich einen Fehler #NAME? bekomme?
Überprüfe, ob alle Funktionen korrekt geschrieben sind und dass Du die richtigen Argumente übergibst. Manchmal kann ein Leerzeichen in Funktionsnamen zu diesem Fehler führen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige