Office: Formelausdrücke nach Teiltext durchsuchen für Spaltenkontrolle

Hallo liebe Community,

für Anwender habe ich ein Formular. Auf dem Blatt „Eingaben“ wird ein Verteilschlüssel für ein bestimmtes Jahr ausgewählt.
Ich stelle auf dem Tabellenblatt „Schlüssel“ die Schlüssel für verschiedene Jahre zur Verfügung, die der Anwender nun konsequent auf den weiteren Blättern nutzen soll.
Auf dem Tabellenblatt „Daten“ erfolgt nun die Anwendung des ausgewählten Schlüssels.
Meine Frage: Wie kann ich mit einer Excel-Formel prüfen, dass der Anwender immer den korrekten Schlüssel verwendet hat? Da er sich für den Schlüssel des Jahres 2020 entschieden hat (Blatt „Eingaben“) können nur die Schlüssel aus Spalte C auf dem Blatt „Schlüssel“ zur Anwendung kommen. Im Anwendungsfall auf dem Blatt „Daten“ hat er einmal den Schlüssel aus Spalte E benutzt. Dies möchte ich im Rahmen der Qualitätssicherung abfangen.
Anders ausgedrückt: Bei allen verwendeten Formelausdrücken darf nur „Schlüssel!C“ vorkommen – und nicht Schlüssel!B, nicht Schlüssel!D und auch nicht Schlüssel!E.
Siehe dazu meine Beispieldatei.
Wie kann ich das in eine Formel verpacken?

Danke für Eure Hilfe!



yorcky

 

Hallo yrckyn

wenn ich es richtig verstanden habe so:

Code:

=B$5*INDEX(Schlüssel!$B$1:$H$7;ZEILE(Schlüssel!$A5);VERGLEICH(Eingaben!$A$18;Schlüssel!$B$4:$H$4;0))

B6 runter und rüberziehen

Gruß Uwe

 

Hi

berechne den Prozentwert aus den Ländernamen und dem eingegebenen Schlüssel
Dann kann man da keinen Fehler machen und muss nichts kontrollieren.

Die Formel dazu hat ja Uwe gezeigt.

Für das, was du vorhast , müsste man den Formeltext analysieren, aber das ist schwierig und aufwendig.
Es gibt zwar die gleichnamige Funktion und du kannst dir auch due Formeln in den Zellen. anzeigen lassen, um es zu kontrollieren, aber einfacher und narrensicher ist die Berechnung der Werte aus den eingegebenen Angaben

Gruß Daniel

 

Moin

Ich bin - wie immer - für die komplizierte Lösung.

 

Guten Morgen!
Danke für Eure hilfreichen Ratschläge. Aber ich habe mich wohl etwas mißverständlich ausgedrückt.
Meine Datei geht an externe Personen, die dann das Datenblatt "Daten" ausfüllen. Ich muss davon ausgehen, dass die ausfüllenden Personen wenig Ahnung von Excel haben und eine Formel wie =C$5*Schlüssel!C5 nutzen.
Ich bekomme die Datei dann zurück und muss kontrollieren, ob alles korrekt ausgefüllt wurde. Daher muss ich wohl doch den Formeltext analysieren.
Verbal ausgedrückt möchte ich folgendes analysieren:
Wenn der Verteilschlüssel für das Jahr 2020 genutzt wird und das auf dem Blatt "Eingaben" so angegeben wird, kann auf dem Blatt "Daten" im Formeltext der Ausdruck "Schlüssel!" nur in Kombination mit dem Buchstaben "C" vorkommen - alle anderen Buchstabenvorkommen wären falsch. Kann ich dies in eine Formel packen?
Nochmals und vorab vielen Dank!

 

Hallo,

in Zellen kann man alles (un)mögliche reinschreiben. Um zu überprüfen, ob alle Formeln den Ausdruck Schlüssel enthält kann man dies am besten per VBA. Ansonsten im Einzelfall mit =(FORMELTEXT() gibt die Formel der Zelle aus. Mit WENN() und FINDEN() prüfen auf >0 kannst du feststellen, ob der Ausdruck Schlüssel! vorhanden ist oder nicht.

Gruß Uwe

 

Hallo!

Damit hat sich für mich der Inhalt etwas verschoben.

Über die Funktion =FORMELTEXT() lese ich verschiedene Formeln aus. Nun sollen zwei Bedingungen geprüft werden:

1. Gibt es im Formeltext eine Referenz auf ein anderes Tabellenblatt mit einem bestimmten Namen?
2. Wenn ja, kommt aus dem Tabellenblatt nur eine bestimmte Spalte vor?

Zur Illustration siehe mein neues Beispiel.
Vielen Dank.

yorcky2

 

Keine Ahnung ob E10 jetzt falsch oder korrekt ist.
=UND(ISTZAHL(SUCHEN("!"&$B$5;B9));ISTZAHL(SUCHEN($B$3;B9)))
B9 kannst du natürlich durch deine Formeltext() Formel ersetzen.