Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Wert einer Zelle errechnen, in der Zielzelle soll keine Formel sein





Frage

Hallo, ich habe folgendes Problem: In der Zelle A1 steht ein Wert (100), in der Zelle B1 ein weiterer (5%). In der Zelle C1 soll das multiplizierte Ergebnis stehen => 5. Allerdings soll in der Zelle C1 keine Formel hinterlegt sein, das Feld soll leer sein für manuelle Einträge. Es soll also eine vorhandene aber nicht sichtbare Formel sein (analog einer bedingten Formatierung, die ja auch nicht sichtbar ist). Gerne möchte ich ohne Makros, Hilfsspalten etc. auskommen. Ist höchstwahrscheinlich die gleiche Lösung: Wenn in Zelle A2 "Storno" steht, soll dieser Wert in Zelle B2 erscheinen- auch hier soll in Zelle B2 keine sichtbare Formel hinterlegt sein. Vielen Dank für Eure Lösungsvorschläge! Stefan

Antwort 1 von Hajo_Zi

Hallo Stefan,

keine Change ohne VBA.

Gruß Hajo

Antwort 2 von AceRider

Schade :-(( Aber eine Lösung mit VBA wäre möglich? Wie sieht die aus?

Antwort 3 von DukeNT

Hi Stefan,
in Zelle C1 schreibst du:
=WENN(A1<>"";WENN(B1<>"";(A1*B1);"");"")
Wenn in A1 und B1 werte stehen wird C1 berechnet.
Zelle C1 ist leer wenn A1 oder B1 leer sind.
Spalte B Zellen Formatieren auf "Prozent" einstellen.
Dann noch unter Ansicht "Bearbeitungsleiste" den Haken weg.
Damit wird die Formelzeile ausgeblendet.
Zwar nicht das gelbe vom Ei aber wenn es so geht?

Gruß Niels

Antwort 4 von Hajo_Zi

Hallo Stefan,

unter der Tabelle

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$B$1" Then Range("C1") = Range("A1") * Range("B1")
End Sub

Gruß Hajo

Antwort 5 von AceRider

Funktioniert, vielen Dank für die Lösungsvorschläge!

In der ersten Anfrage habe ich leider nicht geschrieben, dass auch die Folgezeilen berücksichtigt werden sollen. Wie muss dann die Formel aussehen? Mit dem Ersetzen des Wertes "$a$1" durch "A1:A1000" war ich wenig erfolgreich....

Antwort 6 von Hajo_Zi

Hallo Stefan,

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Column = 2 Then Cells(Target.Row, 3) = Cells(Target.Row, 1) * Cells(Target.Row, 2)
End Sub

Gruß Hajo

Antwort 7 von AceRider

Klasse, bin begeistert, Hajo.

Eine Anschlussfrage: Wenn ich den Wert aus Spalte 2 wieder lösche, soll auch in Spalte 3 nichts mehr erscheinen (jetzt erscheint beim Löschen immer eine "0") => d.h. ich will den Ursprungszustand wieder haben.

Letzte Frage (hoffentlich): Wie sieht die Formel bei diesem Problem aus: Wenn in Zelle A2 "Storno" steht (+fortlaufende Zeilen), soll dieser Wert "Storno" in Zelle B2 erscheinen.

Antwort 8 von Hajo_Zi

HAllo Stefan,

ich hoffe mal ich habe es richtig verstanden.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Column = 2 Then
If Target <> "" Then
If IsNumeric(Cells(Target.Row, 1)) And IsNumeric(Cells(Target.Row, 1)) Then
Cells(Target.Row, 3) = Cells(Target.Row, 1) * Cells(Target.Row, 2)
End If
Else
Cells(Target.Row, 3) = ""
End If
End If
If Target.Column = 1 And Target = "Storno" Then Target.Offset(0, 1) = Target
End Sub

Ich habe noch paar verbesserungen eingebaut

Gruß Hajo

Antwort 9 von AceRider

Fast perfekt mit einer kleinen Änderung: Wenn nur in Spalte A oder B etwas steht (also entweder 100 oder 5%), dann soll in Spalte C nichts stehen.

Antwort 10 von Hajo_Zi

Hallo Stefan,

sieht ein Beitrag mit Anrede und Gruß nicht persönlicher aus?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Column = 2 Then
If Target <> "" Then
If IsNumeric(Cells(Target.Row, 1)) And IsNumeric(Cells(Target.Row, 2)) _
And Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" Then
Cells(Target.Row, 3) = Cells(Target.Row, 1) * Cells(Target.Row, 2)
End If
Else
Cells(Target.Row, 3) = ""
End If
End If
If Target.Column = 1 And Target = "Storno" Then Target.Offset(0, 1) = Target
End Sub

Gruß Hajo

Antwort 11 von AceRider

Hallo Hajo,

natürlich, Du hast Recht. Du wirst lachen, aber genau das habe ich bei meiner vorherigen Nachricht auch gedacht- nachdem ich sie abgeschickt habe :-)) Vor allem, da Du mir wirklich sehr weitergeholfen hast und ich jetzt Dank Deiner Hilfe wunschlos glücklich bin!

Vielen, vielen Dank für Deine kompetente Hilfe!

Viele Grüße
Stefan

Antwort 12 von AceRider

Guten Abend Hajo & alle anderen lieben Helfer/Innen,

gerade ist mir aufgefallen, dass ich bei der vorherigen Anfrage einen Fehler eingebaut habe:

Wenn in Spalte B eine % Zahl steht, soll normal gerechnet werden (also z.B. 100 x 5%), nehme ich aber aus der Spalte B die % Zahl heraus, soll in C nichts stehen. Trage ich aber in die Spalte C eine manuelle Zahl ein, soll in Spalte B die % Zahl herausgenommen werden.

Ergänzt wird mein jetziges Problem durch drei neue Spalten. Steht in Spalte D das Wort Storno soll in Spalte A ebenfalls Storno übernommen werden (vorher sagte ich, dass das Wort Storno aus Spalte A in Spalte B übernommen werden solle). Dies habe ich selber hinbekommen:

If Target.Column = 4 And Target = "Storno" Then Target.Offset(0, -3) = Target

Wenn Spalte E leer ist, kann obige Formel verwendet werden. Wenn aber in Spalte D "Storno" steht und in Spalte E "Erl.", dann soll in Spalte A "Storno" stehen und es soll zusätzlich in Spalte E der Wert aus Zelle F stehen mit dem Zusatz "Storno verechnen". Beispiel: "Storno verechnen 500". Spalte G ist eine Spalte mit Gebühren, die verrechnet werden müssen.

Letztes Problem: Spalte A bezieht sich auf Spalte G (hier steht ein nicht währungsbereinigte Wert). Wenn ich in Spalte F den Wert ändere, ändert sich richtigerweise auch Spalte A. Aber es verändert sich nicht der Wert in Spalte B und C (C ist relevant)!

Gibt es auch dafür eine Lösung?? Der besseren Übersicht wollte ich die Excel Datei beigefügen, wußte aber nicht wie das geht....

Viele Grüße und vielen Dank!

Stefan

Antwort 13 von Hajo_Zi

Hallo Stefan,

hochladen kannst Du z.B. hier
http://www.uploadagent.de/index.php

Ich habe erstmal bis Mittwoch abend keine Zeit für Dein Problem.

Gruß Hajo

Antwort 14 von AceRider

Ich habe sie hochgeladen, weiß aber nicht, wo sie gelandet ist... :-)))

Antwort 15 von Hajo_Zi

Hallo Stefan,

nach dem hochladen wird Dir im oberen Teil ein Link angezeigt und den mußt Du in das Forum kopieren.

Gruß Hajo

Antwort 16 von AceRider

Hallo Hajo,

die Tabelle befindet sich hier:

http://www.uploadagent.de/files/1172676165/Tabelle.xls

Gruß
Stefan

Antwort 17 von Hajo_Zi

Hallo Stefan,

auf meinem Rechner ist jetzt leider der Testzeitraum für mein Office abgelaufen. Meine gekaufte Version installiere ich erst beim neuen Betriebssystem. Was am Wochende passieren wird. Also bin ich zur Zeit nur eingeschränkt Handlungsfähig. Ich kann Dein Problem wahrscheinlich mir erst am Sonntag ansehen. Ich hoffe mal das bis dahin schon jemand das Problem gelöst hat.

Gruß Hajo

Antwort 18 von AceRider

Hallo Excelprofis,

ich hoffe mir kann jemand weiterhelfen, wollte den Thread wieder ein bißchen nach vorne bringen :-)

Viele Grüße

Stefan

Antwort 19 von Hajo_Zi

Hallo Stefan,

ich hoffe mal ich habe viele Probleme erledigt.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 1 Or Target.Column = 2 Then
Application.EnableEvents = False
If Target <> "" Then
If IsNumeric(Cells(Target.Row, 1)) And IsNumeric(Cells(Target.Row, 2)) _
And Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" Then
Cells(Target.Row, 3) = Cells(Target.Row, 1) * Cells(Target.Row, 2)
End If
Else
Cells(Target.Row, 3) = ""
End If
Else
If Target.Column = 3 Then
Cells(Target.Row, 2) = ""
End If
End If
If Target.Column = 4 And Target = "Storno" Then
Target.Offset(0, -3) = Target
If Target.Offset(0, 1) = "Erl." Then
Target.Offset(0, 1) = "Storno verrechnen " & Target.Offset(0, 2)
End If
End If
Application.EnableEvents = True
End Sub

Das letzte Problem konnte ich nicht mehr testen, da zu wenig Werte in der Datei waren und bei mir in Spalte A nur noch Storno Stand und keine Verknüpfung.

Gruß Hajo

Antwort 20 von Hajo_Zi

Hallo Stefan,

ich habe die Datei nochmal runtergeladen. Bei Änderung in Spalte F ändert sich nicht Spalte A, da diese sich auf G bezieht und G sich nicht auf F bezieht.

Was ist wenn in A schon Storno steht? oder der Wert in Spalte A überschrieben wurde, was im Code ja auch abgefangen wurde.

Gruß Hajo

Antwort 21 von AceRider

Hallo Hajo,

erstmal vielen Dank für Deine Antwort, da sieht schon mal sehr gut aus!

Ich habe Dir die Datei mit einigen Musterbeispielen hochgeladen:

http://www.uploadagent.de/files/1173702342/Tabelle.xls

In A werde ich nicht manuell das Wort "Storno" eintragen, hier trage ich nur Zahlen ein.

Die Werte 50 und 1.000 in Spalte F bzw. G sind von mir vorgegebene Zahlen, die sich ändern können.

Lieben Gruß
Stefan

Antwort 22 von Hajo_Zi

Hallo Stefan,

es ist ja Klasse das Du eine neue Datei hochgeladen hast mit paar mehr Daten.

Der Dateiname sollte was mit dem Problem zu tun haben. Ich habe mir z.B. einen Ordner angelegt in dem ich alle Dateien aus dem Internet speichere. Bei Dateinamen wie Test., Mappe.., Beispiel wird eine vorhandene überschrieben. Ich lehne es ab solche Dateien runterzuladen.

Meine Fragen hast Du aber nicht beantwortet.

Ob nun Storno oder eine Zahl in Spalte A eingteragen wird bleibt sich gleich. Eine Eingabe in der Zeile egal welche Spalte ändert nicht den Wert in Spalte A.

Wenn Du in Spalte D Storno einträgst steht in Spalte F kein Wert mehr sondern auch Storno. Diese Formeln waren im ersten Beispiel nicht vorhanden.

Man sollte schon ein Beispiel hochladen das in entscheidenen Punkten wie das Original aussieht.

Da mein Homerechner immer noch nicht ok ist war dies wahrscheinlich die letzte Antwort für Heute.

Gruß Hajo

Antwort 23 von AceRider

Hallo Hajo,

mir ist leider/zum Glück bei dem letzten Update noch eine Idee (wg. der Formeleingabe in Spalte F) eingefallen, daher sieht die letzte hochgeladene Datei etwas anders aus, sorry, ich dachte, das sei nicht relevant.

Doch, eine Änderung in der Spalte G ändert den Wert in Spalte A (Formel in Spalte A =G3*1,3).

Gruß
Stefan

Antwort 24 von Hajo_Zi

Hallo Stefan,

ich interpretiere das mal jetzt so das nicht bei Änderung in Spalte F der Wert in A geändert wird sondern bei Eingabe in Spalte G. Das ist ein Teil der Antwort. Bis morgen hast Du dann ja Zeit den anderen Teil auch zu beantworten.

Gruß Hajo

Antwort 25 von AceRider

Hallo Hajo,

ja, richtig, Änderung in Spalte G ändert Wert in Spalte A (und nicht bedingt durch Spalte F). In meiner Anfrage vom 26.02.2007, 20:05 habe ich die Spalte F und G vertauscht.

Zitat:
Wenn Du in Spalte D Storno einträgst steht in Spalte F kein Wert mehr sondern auch Storno. Diese Formeln waren im ersten Beispiel nicht vorhanden.


Mein Wunsch wäre es, dass ich in Spalte D "Storno" eintrage und er (bildlich gesprochen) in Spalte E erst den Wert aus Spalte F übernimmt (mit dem Zusatz Storno verrechnen) und erst dann in Spalte F das Wort "Storno" einträgt.

Gibt es eigentlich, die Möglichkeit, eine Sicherheitabfrage einzubauen? Wenn in D Storno von mir eingetragen wird, dass dann ein Kästchen auftaucht mit der Frage "Wollen Sie einen Storno eingeben" Ja/Nein?

Gruß
Stefan

Antwort 26 von Hajo_Zi

Hallo Stefan,

der Code ist jetzt ein wenig komplizierter, das kann ich nicht mehr im Beitrag darstellen. Ich habe mich nur um das Stornoproblem gekümmert, da die anderen Fragen nicht beantwortet wurden.

Link zur Datei

Gruß Hajo

Antwort 27 von AceRider

Hallo Hajo,

hilf mir bitte weiter, ich weiß nicht genau, welche Frage ich unbeantwortet gelassen habe...

Viele Grüße

Stefan

Antwort 28 von Hajo_Zi

Hallo Stefan

Ob nun Storno oder eine Zahl in Spalte A eingetragen wird bleibt sich gleich. Eine Eingabe in der Zeile egal in welcher Spalte ändert danach nicht mehr den Wert in Spalte A..

Was soll nun passieren falls in Spalte G was eingetragen wird, soll die Formel wieder eingetragen werden?

Wann soll die Formel in Spalte F wieder eingetragen werden?

Gruß Hajo

Antwort 29 von AceRider

Hallo Hajo!

Wenn sich in Spalte G der Wert ändert, ändert sich durch die Formel in Spalte A ebenfalls der Wert. Doch die Spalte C wird nicht korrekt errechnet. Die Formel in Spalte A kann überschrieben werden.

In Spalte F braucht nichts verändert werden.

Änderungen in Fett:

http://www.uploadagent.de/files/1173798386/storno.xls

Gruß

Stefan

Antwort 30 von Hajo_Zi

Hallo Stefan,

Heute ist Feierabend, wir drehen uns im Kreis, falls die Formel in Spalte a überschriebenwurde durch eine Eingabe, ändert sich der Wert in Spalte a nicht bei eingabe in G

Gruß Hajo

Antwort 31 von AceRider

Hallo Hajo,

die Zelle wird ja nur überschrieben, wenn in D Storno steht. Ein Storno ändert ja eh alle möglichen Angaben in der Zeile.

Eine Änderung in C soll nur erfolgen, solange die Formel in A noch vorhanden ist und der Wert in G von mir manuell angepasst wird.

http://www.uploadagent.de/files/1173799290/storno.xls

Gruß

Stefan

Antwort 32 von Hajo_Zi

Hallo Stefan,

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 1 Or Target.Column = 2 Then
If Target <> "" Then
If IsNumeric(Cells(Target.Row, 1)) And IsNumeric(Cells(Target.Row, 2)) _
And Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" Then
Cells(Target.Row, 3) = Cells(Target.Row, 1) * Cells(Target.Row, 2)
End If
Else
Cells(Target.Row, 3) = ""
End If
Else
If Target.Column = 3 Then
Cells(Target.Row, 2) = ""
End If
End If
If Target.Column = 4 And Target = "Storno" Then
If MsgBox("Wollen Sie einen Storno eingeben", vbYesNo + vbQuestion, "Löschabfrage ?") = vbYes Then
Target.Offset(0, -3) = Target
If Target.Offset(0, 1) = "Erl." Then
Target.Offset(0, 1) = "Storno verrechnen " & StWert
End If
Else
Application.Undo
End If
ElseIf Target.Column = 7 Then
If IsNumeric(Cells(Target.Row, 1)) And IsNumeric(Cells(Target.Row, 2)) _
And Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" Then
Cells(Target.Row, 3) = Cells(Target.Row, 1) * Cells(Target.Row, 2)
End If
End If
Application.EnableEvents = True
End Sub

Gruß Hajo

Antwort 33 von AceRider

Hallo Hajo,

ich habe die Tabelle jetzt einige Zeit getestet. Eine Sache funktioniert nicht richtig:

Wenn in Spalte D das Wort "Storno" steht, soll Excel in Spalte F das Wort "Storno" und in Spalte E den ursprünglichen Wert aus F übernehmen (50, 100 etc.) mit dem Zusatz "Storno verrechnen".

In der aktuellen Version steht in Spalte E immer die Formulierung "Storno verechnen Storno". Bzw. manchmal übernimmt er einen Wert, ich kan mir aber nicht erklären, warum.

Dateilink


Ich habe versucht, das Problem selber zu lösen, bin aber nicht weiter gekommen....

Gruß
Stefan

Antwort 34 von AceRider

Neuer Anhang (beim vorherigen war eine Kleinigkeit falsch):

Dateilink


Antwort 35 von Hajo_Zi

Hallo Stefan,

Es wird nur eine Zelle mit einmal ausgefüllt?
Ich habe es jetzt 25x getestet und es lief immer korrekt.

Gruß Hajo

Antwort 36 von AceRider

Hallo Hajo,

ich habe es mir gerade nochmal angesehen, funktioniert aber immer noch nicht.

Gruß
Stefan

Antwort 37 von Hajo_Zi

Hallo Stefan,

dann mußt Du mal schritt aufschreiben was Du machst, nach offnen de Datei. Ich muß den Fehler simulieren können um ihn zu finden.

Gruß Hajo

Antwort 38 von AceRider

Hallo Hajo,

ich öffne die Datei und gebe in Zelle D11 Storno ein- in Zelle E11 erscheint nur "Storno verrechnen". Genauso in den Folgezellen (D12, D13 etc.).

Viele Grüße
Stefan

Antwort 39 von Hajo_Zi

Hallo Stefan,

ich kann den fehler nicht simulieren.

Gruß Hajo

Antwort 40 von AceRider

Hallo Hajo,

liegt es evtl. daran, dass Du Du eine andere (veraltete/neuere) Datei hast als ich? Ich habe Dir ja nochmal meine neueste Datei als Link zur Verfügung gestellt.

Gruß
Stefan

Antwort 41 von Hajo_Zi

Hallo Stefan,

ändere mal diesen Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 4 Then
StWert = Target.Offset(0, 1)
End If
End Sub

die Eingabe ist ja in Spalte D (4) und nicht 5 (E)

Gruß Hajo

Antwort 42 von AceRider

Hallo Hajo,

vielen, vielen Dank! Daran hat es gelegen. VBA war für mich völliges Neuland, gerade mit Deinem letzten Beitrag hast Du mir eine große Hilfestellung gegeben, Einblicke in VBA zu erhalten und selber herumzutüffteln!

Alles funktioniert so, wie es sein soll!

Nochmals vielen Dank!

Stefan