Hilfe bei einer Formel

[Nanu]

Hola mal wieder ins Forum,
heute bräuchte ich mal Hilfe bei einer Formel.
Ich habe eine intelligente Tabelle mit den Spalten A-N. In Spalte A wird eine beliebige Zahl eingegeben. In Spalte F in der Kopfzeile steht ein Wert (3244). Dieser Wert soll immer dann um 1 erhöht werden wenn in Spalte A eine Wert zwischen 6000 und 6100 eingegeben wird. Das ist ja auch kein Problem mit folgender Formel die in der Hilfsspalte F steht:
=WENN(UND([@Referencia]>=6000;[@Referencia]<6100);F1+1;F1)
In Spalte G arbeite ich dann wie folgt:
=WENN(UND([@Referencia]>=6000;[@Referencia]<6100);[@3244];"")

Das Problem entsteht wenn jemand etwas falsches eintippt und ich eine Zeile mittendrin löschen muss. Dann kommt der BEZUG Fehler. Ist mir auch klar warum. Die Zeile die gelöscht wurde findet die Formel ja nicht mehr. Gibt es eine Formel mit der ich das umgehen kann? In der Art:
Wenn Und Referencia zwischen 6000 und 6100 ist dann rechne den Wert in der darüberliegenden Zeile +1.
So soll es aussehen
Und das passiert wenn ich Zeile 320 auf Grund eines Fehlers lösche:
Das Resultat
Kann man das mit einer anderen Formel lösen?
Zur Info: Die gleiche Frage habe ich schon im Herber Forum gestellt.

Vielen Dank an alle die sich meiner Frage annehmen.
Saludos Nanu

[thowe]

Hola Nanu!

Den #BEZUG Fehler wegzubekommen ist "schier/nahezu unmöglich". Die Formeln Indirekt und Bereich.Verschieben - ald denkbare und nutzbare Lösungswege - referenzieren nicht direkt und haben damit kein Problem mit Zeilen löschen. Sie Sind jedoch volatil, das bedeutet, dass bei jeglicher Änderung IRNGENDEINER Zelle auch alle Zellen mit =INDIREKT(...) neu berechnet werden. für Große Datenmengen ungeeignet. Solltest du Office365 verwenden, könntest du es noch mit - in deinem Fall - VSTAPELN() probieren.

Ich arbeite leider nie mit intelligenten Tabellen, aber vielleicht helfen dir die Tipps.

lg

[d'r Bastler]

Moin Nanu,

so hart wie thowe wollte ich es nicht sagen, aber er hat natürlich Recht: Ist ein Bezug erst einmal gebrochen, zieht das einen Rattenschwanz von Bezugsfehlern nach sich.

Was aber nicht bedeutet, dass es nicht doch eine Lösung gibt. Eine Idee habe ich schon, bräuchte dazu aber eine Kopie der am Bezug beteiligten Arbeitsblätter (falls es mehrere sind, sonst genügt natürlich das eine).

Du kannst das hier einfach als Anhang hochladen (wie übrigens auch Bilder, externe Server sind nicht notwendig) und dann schaumermal ...

lg

[d'r Bastler]

Na, dann hoffe ich mal, dass ich schnell bin als Du (gerade online) ...

Folgender Code in Deine Tabelle:

Code: Alles auswählen

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 6 Then                                   'reagiert nur auf Wechsel in Spalte F
    If Target.Value >= 6000 And Target.Value <= 6100 Then   'Deine Bedingungen
        Cells(1, 6) = Cells(1, 6) + 1                       'zählt F1 bei WAHR hoch
    End If
End If
End Sub

und Du solltest sehen, was Du erwartest.

lg

[d'r Bastler]

Und jetzt noch als gemächlicher Nachsatz:

Du hattest zwar ausdrücklich um Hilfe bei einer Formel gebeten, aber thowe hat schon darauf hingewiesen: Formeln sind eine volatile, also verletzliche Angelegenheit, die jeder Nutzer (ob wissentlich oder aus Unkenntnis) zum Fehler bringen kann.

In der Formelwelt kann man das (meist sehr aufwändig) mit Blattschutz-Mechanismen vermeiden. Aber auch dazu braucht man schon etwas VBA.

VBA bietet Dir die Nutzung aller XL-Formeln im Hintergrund, ohne dass der Nutzer auch nur Zugang zu einer davon hat. In Deinem Sheet liegen ausschließlich die Daten und die Berechnungsergebnisse. Die Formeln sind in VBA gekapselt.

Wenn Du Dein Arbeitsblatt entsprechend umgearbeitet haben möchtest, stell' es gerne hier ein und ich kümmere mich darum.

lg

[Nanu]

Hola ins Forum,
vielen Dank erstmal wieder für die Infos. Das Makro vom Bastler werde ich natürlich ausprobieren. Das Problem mit den Makros ist nur das wir mit dieser Tabelle auch wieder in Excel online arbeiten. Über ein Scan Programm auf dem Handy werden die Daten aus den ersten 5 Spalten eingelesen und der Rest dann durch Formeln ergänzt. Bei Produkten mit der Referenz zwischen 6000 und 6100 braucht der Mitarbeiter dann zusätzlich die Nummer der "Boleta". Ich hänge das Blatt als Muster mal an damit man vielleicht besser nachvollziehen kann was ich mache. Die Spalte F ist im Betrieb ausgeblendet.
Saludos Nanu

Mappe1.xlsx

[Nanu]

Und noch ein Update um euch nicht unnötig zu belasten.
Eine Formellösung ist gefunden.
Aber vielleicht habt ihr in anderer Sache noch einen Tipp. Wie beschrieben ist es eine intelligente Tabelle. Leider kann ich den Schutz nicht aktiviern weil sie sich sonst nicht mehr erweitert. Habt ihr da noch Tipps für mich wie ich die Formelfelder auch in Excel Online schützen kann?
Vielen Dank wie immer hier an euch.
Saludos Nanu
Ps. Bastler natürlich probiere ich auch deine VBA Lösung aus

[thowe]

Hallo Nanu,

danke für das Update. Wäre nett wenn du uns und die Anderen an der gefundenen Formellösung teilhaben lässt, lassen würdest.

LG

[Nanu]

Aber sehr gerne doch.
Saludos Nanu

172595.xlsx