Hallo,
ich möchte in einer Exceldatei für meine Kunden Rabatt-Guthabenkonten einrichten und aktualisieren lassen. Ich habe eine Exceldatei mit den Kundendaten, die wie folgt aufgebaut ist:
Kd-Nr. | Name | Vorname | Straße | PLZ | Ort | G-Konto
In einer anderen Exceldatei möchte ich eine Abfrage erstellen, die mir den Namen, den ich dort z.B. in A5 eingebe in meiner Kunden-Datei sucht und in z.B. A3-G3 komplett anzeigt. In z.B. C6 möchte ich den Betrag eingeben, der in der Kundendatei unter G-Konto abgezogen bzw. hinzugerechnet werden soll. Mit einem Klick auf einen erstellten Button soll der Betrag dann unter G-Konto beim jeweiligen Kunden geändert werden.
Soweit die Theorie… 
Nur habe ich schon bei der Abfrage der Kundendaten Probleme, da ich schon lange nicht mehr mit Excelformeln gearbeite habe. Sollte wohl mit einem Sverweis gehen. Leider kriege ich nur eine Fehlermeldung „#NV“.
Daher möchte ich euch fragen, ob meine Theorie überhaupt in die Tat umzusetzen ist und wenn, wie ich das am besten realisieren kann.
Für Tips und Rat schon einmal vielen Dank!
Gruß
Achim
Hallo,
erst mal eine Bedenkenswarnung:
Excel ist für sowas ungeeignet.
Ein doppelklick wo ein singleklick reicht und schon wird doppelt berechnet.
Nach Deiner Methode würdest Du sowas nie bemerken!
ansonsten denke ich ist eine Art Serienbrief-Funktion für Dich am geeignetsten. Damit kannst Du komfortabler suchen und afaik auch einfach updaten…
Hallo Achim,
Ich
habe eine Exceldatei mit den Kundendaten, die wie folgt
aufgebaut ist:
Kd-Nr. | Name | Vorname | Straße | PLZ | Ort | G-Konto
In einer anderen Exceldatei möchte ich eine Abfrage erstellen,
die mir den Namen, den ich dort z.B. in A5 eingebe in meiner
Kunden-Datei sucht und in z.B. A3-G3 komplett anzeigt.
Sverweis funktioniert hier nicht, weil diese Funktion immer in der ersten Spalte des Bereichs nach dem angegebenen Kriterium sucht. Du müßtest also in deiner Datenbank den Namen als erste Spalte setzen oder halt die Kundennummer eingeben.
In z.B.
C6 möchte ich den Betrag eingeben, der in der Kundendatei
unter G-Konto abgezogen bzw. hinzugerechnet werden soll. Mit
einem Klick auf einen erstellten Button soll der Betrag dann
unter G-Konto beim jeweiligen Kunden geändert werden.
Dann kannst Du Dir ein Makro aufzeichnen, das das macht, und dieses dann hinterher mit der Schaltfläche verknüpfen.
Daher möchte ich euch fragen, ob meine Theorie überhaupt in
die Tat umzusetzen ist und wenn, wie ich das am besten
realisieren kann.
Geht schon, aber man muß sich halt ein paar mehr Gedanken zur Umsetzung machen, es ist nicht ganz trivial.
Gruß Kubi
Hallo Kubi;
Sverweis funktioniert hier nicht, weil diese Funktion immer in
der ersten Spalte des Bereichs nach dem angegebenen Kriterium
sucht. Du müßtest also in deiner Datenbank den Namen als erste
Spalte setzen oder halt die Kundennummer eingeben.
Ich habe den Namen nun in die erste Spalte gepackt. Nun funktioniert schon mal die Abfrage nach dem Namen. Besten Dank!
Da es in der Datenbank auch gleiche Nachnamen gibt, zeigt er mir immer den letzten Datensatz an. Gibt es eine Möglichkeit, dass ich eine Auswahl bekomme oder dass er nach der Kombination aus der Zelle von Vor- und Nachname den Datensatz anzeigt?
Achim
Hallo,
vielen Dank für die Bedenken. Dem Macro müsste man doch mitteilen können, das er nach dem aktualisieren des Kundenkontos die Zelle mit dem neuen Rabatt (C6)löscht. Somit würde ein doppeltes Bestätigen doch nichts mehr bringen, da alter Betrag + leere Zelle (C6) wieder den alten Betrag ergibt.
Schönen Gruß
Achim
nicht mit Sverweis
dazu müsstest Du schon ein wenig komplexer rangehen.
Kleiner Tipp dazu:
Führ mal die Fuktion „Makro aufzeichnen“ auf und geh dann in das Tabellenblat mit der Datenbank.
Drück dann STRG+F und such nach dem Namen.
Markiere die komplette Zeile und kopiere sie in Dein Tabellenblatt für die Suche.
danach nochmal F3 damit nochmal gesucht wird (weitersuchen)
dann beendest Du den Aufzeichnungsmodus wieder.
Vergib unbedingt eindeutige ID’s für die einzelnen Leute - damit kannst Du dann die „Datensätze“ wieder an die richtige stelle zurückkopieren…
anschliessend musst Du das Makro mit Variablen befüllen…
Etwa dass es den Suchbegriff aus einer bestimmten Zelle holt
inhalt = Range(„A1“).Value
msgbox inhalt
oder dass eine input box geöffnet wird.
u.U. musst Du auch noch einbauen, dass das Makro in Zelle A1 beginnt und auch nur was kopiert, wenn es in der richtigen Zelle gefunden hat…
Ein Problem gibts dabei noch - wenn das Makro nichts findet produziert es sicher einen Fehler.
Ich persönlich weiss nicht, wie Du den abfangen kannst…
Achja - irgendwie musst Du auch noch abfangen, dass immer wieder von vorne begonnen wird mit suchen, aber das kriegst Du sicher hin 
Deine Idee im anderen Posting ist übrigens auch nicht schlecht - einfach den Inhalt wieder resetten und nachprüfen bevor er mit leeren Werten überschreibt…
Hallo munichfreak,
die Makrofunktion hatte ich ursprünglich auch schon probiert, aber wegen Unkenntniss von VBA wieder verworfen.
Ich habe Deinen Ansatz nochmal aufgegriffen, bin dann aber an der Komplexität gescheitert. 
Vielen Dank trotzdem!
Auch alle anderen Ansätze sind meist daran gescheitert, dass Kundennamen auch doppelt vorkommen können und daher noch nach weiteren Kriterien selektiert werden müsste. Auch die Filterfunktion oder der Spezialfilter haben nicht zum gewünschten Effekt geführt.
Ich habe jetzt in der Kundendatei einen Button erstellt, der bei Klick den Befehl Maske (unter Daten->Maske) aufruft. Dort habe ich die Felder laut Datenbank:
Kd-Nr.|Anrede|Nachname|Name|Straße|Postleitzahl|Or|Guthaben.
Auf das angezeigte Guthaben muss ich dann halt den Rabatt zurechnen und dort eintragen.
Auch kann ich hier neue Datensätze eingeben oder Vorhandene löschen.
Das ist zwar nicht ganz das, was ich mir vorgestellt habe, aber immerhin ein Kompromiss
Also nochmal vielen Dank!
Schöne Grüße
Achim
Vba-Lösung
Kd-Nr. | Name | Vorname | Straße | PLZ | Ort | G-Konto
In einer anderen Exceldatei möchte ich eine Abfrage erstellen,
die mir den Namen, den ich dort z.B. in A5 eingebe in meiner
Kunden-Datei sucht und in z.B. A3-G3 komplett anzeigt. In z.B.
C6 möchte ich den Betrag eingeben, der in der Kundendatei
unter G-Konto abgezogen bzw. hinzugerechnet werden soll. Mit
einem Klick auf einen erstellten Button soll der Betrag dann
unter G-Konto beim jeweiligen Kunden geändert werden.
Soweit die Theorie…
Hi Achim,
jetzt die Praxis…
Die Tabellenblattnamen und die zellen musst du dir anpassen.
Du brauchst 3 Tabellenblätter, in Blatt1 über Formular 2 Schaltknöpfe die du mt plus und Minus bezeichnest
und denen du die jeweiligen Makros zuweist.
Blatt1 sieht so aus:
A B C D E F G
1 cz
2 3 c z s3 p3 o3 180
3 10
In A3 gibst du den Betrag ein den du dazu addieren oder abziehen willst.
A1 verweist über Daten--Gültigkeit---Liste auf "Bereich"
Bereich ist ein Name (Eifügen---Namen---Definieren) der auf =Tabelle2!$H$2:blush:H$5 verweist.
Blatt2 sieht so aus:
A B C D E F G H
1 nr Nam vorn str plz ort g-konto nam&vorn
2 1 a x s1 p1 o1 100 ax
3 2 b y s2 p2 o2 200 by
4 3 c z s3 p3 o3 180 cz
5 4 d w s4 p4 o4 340 dw
wobei in H2 (und folgend) folgende Formel steht:
=B2&C2
Blatt3 sieht so aus:
A B C D E F G H I
nr Nam vorn str plz ort g-konto alt g-konto neu diff
Ablauf: In Blatt1 wählst du in A1 den Kunden aus, daraufhin erscheinen dessen Daten in Zeile 2.
In A3 gibst du den Betrag ein und betätigst den Plus oder Minus Knopf.
Daraufhin wird in zeile 2 der neue Wert angezeigt.
In Tabelle 3 wird jeder Vorgang protokolliert.
Gruß
Reinhard
Alt+F11, Einfügen Modul, dorthinein folgender Code:
Public zei As Long
Sub Plus()
Worksheets("Tabelle2").Range("G" & zei) = Worksheets("Tabelle2").Range("G" & zei) + Range("A3")
Worksheets("Tabelle2").Range("A" & zei & ":G" & zei).Copy Range("A2")
Worksheets("Tabelle3").Range("H" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row) = Worksheets("Tabelle2").Range("G" & zei)
Worksheets("Tabelle3").Range("I" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row) = Worksheets("Tabelle3").Range("H" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row) - Worksheets("Tabelle3").Range("G" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row)
Range("A3") = ""
End Sub
Sub Minus()
Worksheets("Tabelle2").Range("G" & zei) = Worksheets("Tabelle2").Range("G" & zei) - Range("A3")
Worksheets("Tabelle2").Range("A" & zei & ":G" & zei).Copy Range("A2")
Worksheets("Tabelle3").Range("H" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row) = Worksheets("Tabelle2").Range("G" & zei)
Worksheets("Tabelle3").Range("I" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row) = Worksheets("Tabelle3").Range("H" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row) - Worksheets("Tabelle3").Range("G" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row)
Range("A3") = ""
End Sub
Dann im VB-Editor Doppelklick auf den Blattnamen, dorthinein den Code:
Private Sub Worksheet\_Change(ByVal Target As Range)
If Target.Address(0, 0) "A1" Then Exit Sub
zei = Application.WorksheetFunction.Match(Range("A1"), Worksheets("Tabelle2").Range("H1:H5"), 0)
Worksheets("Tabelle2").Range("A" & zei & ":G" & zei).Copy Worksheets("Tabelle3").Range("A" & Worksheets("Tabelle3").Range("A65536").End(xlUp).Row + 1)
End Sub
Wow!
Besten Dank!
Ich denke, dass werde ich hinkriegen!
Ich werds heute Abend direkt einmal ausprobieren.
Ich geb dannn Bescheid, wie es gelaufen ist.
Nochmal herzlichen Dank!
Gruß
Achim
Hallo Reinhard,
dir erst einmal besten Dank für diese Lösung!
jetzt die Praxis…
In der Praxis läuft das Ganze schon ziemlich gut!
Nur leider kann ich in der Liste (A1) nur scrollen und nach dem Namen suchen. Bei mehreren hundert Kunden-Datensätzen kann die Suche nach dem richtigen Kunden dauern. Gibt es hier noch eine Lösung?
Auch wird der Kunde und seine Daten nicht in A2-G2 nach Auswahl aus der Liste angezeigt, sondern erst, wenn ich unter A3 einen Betrag eingegeben habe und auf eine Schaltfläche (+/-) klicke.
Die Liste „Bereich“ ist klar definiert (z.B. I2:I1500). Wenn ich nun weitere Kunden anlege (zur Zeit über einen Button, welcher mir die Maske unter Daten–> Maske öffnet),wird die Liste größer und der unterste Kunde fällt aus der Liste „Bereich“ heraus. Kann die Liste „Bereich“ automatisch angepasst werden?
In der Kundentabelle „Adressen“ (Blatt2) werden in der Spalte H (bei mir mittlerweile I) Name und Vorname zusammengefasst. Wie kann ich die Fromel „A1&B1“ anpassen, damit er zwischen den ermittelten Werten ein Leerzeichen lässt. Ist zwar nur ein Schönheitsfehler, aber wenn er schnell korrigiert werden kann, wärs nicht schlecht.
An sich ist diese Lösung schon ziemlich gut, besonders gefällt mir die Protokollierung. Hier kann ich genau nachvollziehen, was eingegeben worden ist. Klasse!
Wäre schön, wenn mir jemand weiterhelfen könnte.
Schönen Gruß
Achim
Hallo Achim,
Nur leider kann ich in der Liste (A1) nur scrollen und nach
dem Namen suchen. Bei mehreren hundert Kunden-Datensätzen kann
die Suche nach dem richtigen Kunden dauern. Gibt es hier noch
eine Lösung?
Kannst du mal eine kleine Beispieldatei basteln (bei Namen Vornamen sw reicht ein Buchstabe, bei zahlen irgendwelche, Hauptsache die dateistruktur ist gleich) und irgendwo hochladen. Siehe dazu auch meinen Beitrag hier im Brett „Homepage“.
Auch wird der Kunde und seine Daten nicht in A2-G2 nach
Auswahl aus der Liste angezeigt, sondern erst, wenn ich unter
A3 einen Betrag eingegeben habe und auf eine Schaltfläche
(+/-) klicke.
Ich hatte das nachgebastelt, sofort nach Auswahl aus der Liste wurde Zeile 2 aktualisiert.
Die Liste „Bereich“ ist klar definiert (z.B. I2:I1500). Wenn
ich nun weitere Kunden anlege (zur Zeit über einen Button,
welcher mir die Maske unter Daten–> Maske öffnet),wird die
Liste größer und der unterste Kunde fällt aus der Liste
„Bereich“ heraus. Kann die Liste „Bereich“ automatisch
angepasst werden?
Bestimmt, dazu wäre die Beispieldatei hilfreich.
In der Kundentabelle „Adressen“ (Blatt2) werden in der Spalte
H (bei mir mittlerweile I) Name und Vorname zusammengefasst.
Wie kann ich die Fromel „A1&B1“ anpassen, damit er zwischen
den ermittelten Werten ein Leerzeichen lässt. Ist zwar nur ein
Schönheitsfehler, aber wenn er schnell korrigiert werden kann,
wärs nicht schlecht.
=A1&" "&B1
An sich ist diese Lösung schon ziemlich gut, besonders gefällt
mir die Protokollierung. Hier kann ich genau nachvollziehen,
was eingegeben worden ist. Klasse!
Jepp, man könnte das leicht erweitern dass auch Datum/Zeit/ User miterfasst werden, damit man nachvolziehen kann wer wann die Million gutschrieb
Gruß
Reinhard
Hallo Reinhard,
Kannst du mal eine kleine Beispieldatei basteln (bei Namen
Vornamen sw reicht ein Buchstabe, bei zahlen irgendwelche,
Hauptsache die dateistruktur ist gleich) und irgendwo
hochladen. Siehe dazu auch meinen Beitrag hier im Brett
„Homepage“.
Aber klar doch! Hier der Link:
http://rapidshare.de/files/10934957/Testdatei.xls.html
Ich hatte das nachgebastelt, sofort nach Auswahl aus der Liste
wurde Zeile 2 aktualisiert.
Vieleicht liegts an der Excelversion. Ich bastle hier mit Excel 2003.
Es soll aber später auf Excel 97 (Notebook) laufen.
=A1&" "&B1
Jawohl, das geht! Bei meinem Versuch fehlte das zweite &.
Jepp, man könnte das leicht erweitern dass auch Datum/Zeit/
User miterfasst werden, damit man nachvolziehen kann wer wann
die Million gutschrieb
Noch arbeite ich alleine.
Also geht die Millionen dann auf meine Kappe.
Aber das Datum wär schon echt Klasse!
…ein Stern ist Dir jetz schon sicher. Also bis hier schon mal vielen Dank!
Gruß
Achim
Hallo Achim,
beim damalien Test bei mir erzeugte eine Auswahl in A1 die Einträge in Zeile 2.
Dies geht jetzt nicht mehr, mal schauen warum.
Mit dem Scrollen, wie wärs mit einer 2ten Listbox wo man erst den Anfangsbuchstaben des Nachnamens auswählt. Dann, in der jetzigen Listbox werden nur die mit diesem Anfangsbuchstaben aufgelistet!?
Oder insgesamt 3 Listbox, zwei für die Anfangsbuchstaben von name und Vorname, dann hast du in der driten nicht mehr soviel Auswahl.
Und wie wäre es, weil hier kaum noch jmd liest, die Frage neu einzustellen, mit Verweis auf hier, Verweis auf den Download und postens der Codes und Angabe was zu verbessern ist.
Gruß
Reinhard
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Jetzt scheint es zu funktionieren
Hallo Achim,
http://www.badongo.com/file.php?file=mit+datum+in+pr…
mit datum im Protokoll.
Die beiden ZusatzMakros (MakroEin und MakroAus) dienen dazu dass du was in der Tabelle korregieren/ändern kannst ohne dass die automatischen Makros dies auswerten.
Oder bei einem programmfehler/Absturz kann es dazu kommen das die automatischen Makros abgeschaltet sind, dann tut sich nix mehr, dann makroEin betätigen.
Gruß
Reinhard
Hallo Achim,
beim damalien Test bei mir erzeugte eine Auswahl in A1 die
Einträge in Zeile 2.
Dies geht jetzt nicht mehr, mal schauen warum.
Nun gehts ja!
Mit dem Scrollen, wie wärs mit einer 2ten Listbox wo man erst
den Anfangsbuchstaben des Nachnamens auswählt. Dann, in der
jetzigen Listbox werden nur die mit diesem Anfangsbuchstaben
aufgelistet!?
Das wäre eine Möglichkeit. Am besten würde mir gefallen, wenn ich in eine Zelle den Nachnamen eingebe und in einer Liste die entsprechenden
Datensätze auswählen könnte.
Oder insgesamt 3 Listbox, zwei für die Anfangsbuchstaben von
name und Vorname, dann hast du in der driten nicht mehr soviel
Auswahl.
Nein ich denke, der Aufwand dafür wäre zu groß. Der Nachname alleine würde schon reichen( Soviele Kunden sinds nun auch nicht! ).
Und wie wäre es, weil hier kaum noch jmd liest, die Frage neu
einzustellen, mit Verweis auf hier, Verweis auf den Download
und postens der Codes und Angabe was zu verbessern ist.
Werde ich machen. Nur nicht mehr heute. Mein Sohn hat morgen Geburtstag und wir müssen noch eine Menge Vorbereitungen treffen.
Gruß
Reinhard
Auch Dir schöne Grüße und recht herzlichen Dank!
Achim