Office: Funktionen im Datenblatt

Helfe beim Thema Funktionen im Datenblatt in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Guten Morgen zusammen, bei der Erstellung eines (neuen) Dienstplanes bin ich auf einige Frage gestossen und hoffe das mir vllt der ein oder andere... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von sengir_cdv, 14. Mai 2021.

  1. sengir_cdv Neuer User

    Funktionen im Datenblatt


    Guten Morgen zusammen,
    bei der Erstellung eines (neuen) Dienstplanes bin ich auf einige Frage gestossen und hoffe das mir vllt der ein oder andere weiterhelfen kann :)
    Unsere Version: Office 2013 (2015.2 (2019?) serverseitig; active directory)

    Frage 1:
    Ich würde gerne bei den Kommentaren die entsprechenden Namen stehen haben.
    Allerdings ist es derzeit so das jedesmal wenn ich ein Kommentar schreibe, erst mein Name steht und sobald ich alles beende bzw erneut den Kommentar aufrufe, steht nun immer Autor als Nutzer.
    Ich habe schon versucht mich selber als Autor im Startbereich der Tabelle einzutragen.
    Ich kann mich auswählen und einfügen, aber sobald ich Speicher - bin ich wieder raus und es ist kein Autor eingetragen.

    Frage 2: Kann ich auch einzelne Felder für bestimmte Nutzer freigeben?
    Die Idee dahinter ist das 2 Leute ihre Markierung setzen (evtl der Name oder ein X) - das ganze soll aber natürlich nur von der entsprechenden Person bzw vom entsprechendem Account (active directory) möglich sein (ohne das der Schutz aufgehoben werden muss).

    Frage 3: Ist es möglich einzelne Spalten / Zeilen zu sperren und gleichzeitig "am Ende der Kontrolle" das ganze Datenblatt? ohne das dann der Schutz per Passwort aufgehoben und wieder erneut gesetzt werden muss?

    Frage 4: Wenn ich ein Datenblatt aufrufe, kann man sich dort evtl automatisch anzeigen lassen wer wann was als letztes geändert hat? zB ab einem bestimmten Datum?
     
    sengir_cdv, 14. Mai 2021
    #1
  2. Hajo_Zi
    Hajo_Zi Erfahrener User
    zu 2
    bearbeiten, Schützen, Berabeiten von Bereichen zulassen.
    zu 4
    Ja
    Die Informationen dazu sind ja in Deiner Spalt IV und IB

    Funktionen im Datenblatt GrußformelFunktionen im Datenblatt Homepage
     
    Hajo_Zi, 14. Mai 2021
    #2
  3. sengir_cdv Neuer User
    Grüsse zusammen,
    erst einmal danke für die Antwort (auch in dem anderen Thread).
    Ich bin schon etwas weiter gekommen und habe einige offene Punkte abarbeiten können.

    Was für mich noch offen ist:
    Mitarbeiter scheidet aus und ein neuer Mitarbeiter kommt. Ich habe eine Zelle in der der Name eines Mitarbeiters steht (zu dem Mitarbeiter gehören insgesamt 4 Zeilen und ca 15 Spalten).
    Nun soll der Mitarbeitername (eine Zelle) geändert werden - Ich kann die Zellen zwar ausblenden, was meine erste Idee war - bekomme nur dann keinen neuen Mitarbeiter rein (das Excel Dokument besteht aus 52 Datenblättern (die Kalenderwochen).
    Ich muss nun also den Bezug von jedem Datenblatt auf das vorherige setzen und muss dieses nun ändern (weil es beim kopieren 1:1 übernommen wird).
    Gibt es dafür evtl ein Makro (das aus dem Bezug / der Formel kw3!b5 ein kw4!b5 macht und beim nächsten datenblatt aus kw4!b5 ein kw5!b5)?

    Zum Verständnis für mich (ob sowas per Makro automatisch machbar ist):
    - Kann ein "Button" für den Druckbereich per Makro erstellt werden, wo festgelegt ist das er von A-X und 2-5 den Bereich drucken soll? Das es nicht mehr manuell ausgewählt werden muss?
    - Ich möchte für Überstunden ein Feld festlegen was, je nach Zahlenwert, grün gelb oder rot ist. Diese Automation geht leider nicht, korrekt? ich müsste wenn dann ein Makro starten und jedes mal ausführen. Richtig?
     
    sengir_cdv, 25. Mai 2021
    #3
  4. sengir_cdv Neuer User

    Funktionen im Datenblatt

    Anbei noch eine Beispiel Datei bzgl der "noch offenen" Frage.
     
    sengir_cdv, 25. Mai 2021
    #4
  5. Exl121150 Erfahrener User
    Hallo,

    in der beiliegenden Excel-Datei habe ich dir im allgemeinen Codemodul "Modul1" 2 Makros (+1 Funktion) eingefügt, die mit den beiden Buttons im Arbeitsblatt "kw3" verknüpft sind.

    Klickt man auf Button "Neues "kw"-Blatt anfügen" im Arbeitsblatt "kw3" wird dieses in das Arbeitsblatt "kw4" kopiert und der Formelbezug in Zelle A2 umgestellt von "='kw2'!A2" auf "='kw3'!A2". Das wird mit allen Formeln gemacht, die einen Blattbezug auf das Vorblatt haben.
    Dieser Mechanismus kann aber frühestens im Blatt "kw2" funktionieren, wenn dieses nach "kw3" kopiert wird. Denn im Blatt "kw1" sind keine Formeln enthalten, die Bezug nehmen können auf ein Vorblatt.

    Der 2. Button "Druckbereich A2:X5 festlegen" besagt bereits alles, was er tut.

    Nachfolgend habe ich die Makros nochmals gelistet:
    Code:
    Option Explicit
    
    Sub NeuesBlatt_Anfuegen()
      Dim WsAlt As Worksheet, WsNeu As Worksheet
      Dim strNameAlt As String, strNameNeu As String
      Dim rgFormelZelle As Range, strFormel As String, Bl$(), BlI As Integer
     
      Set WsAlt = ActiveSheet
      strNameAlt = WsAlt.Name
      strNameNeu = "kw" & (Mid$(strNameAlt, 3) + 1)
     
      WsAlt.Copy After:=WsAlt
      Set WsNeu = Worksheets(Worksheets.Count)
      With WsNeu
         strNameAlt = WsAlt.Name
         .Name = strNameNeu
        
         'Ersetze in den Formeln alle Bezüge auf den Vorvorblattnamen auf den Vorblattnamen(=strNameAlt):
    '--> 'Das kann nur dann funktionieren, wenn bereits im WsAlt-Blatt Bezüge auf das VorBlatt enthalten waren!!
        
         With .UsedRange.SpecialCells(xlCellTypeFormulas)
        
            For Each rgFormelZelle In .Areas
              strFormel = rgFormelZelle.Formula
              'Testen, ob es sich tatsächlich bei allen entdeckten Stellen um den strNameAlt-Blattnamen handelt:
              rgFormelZelle.Formula = Formel_BlattAendern(strFormel, strNameAlt)
            Next rgFormelZelle
          
         End With
      End With
    End Sub
    Function Formel_BlattAendern(strFormel As String, BlattAltName As String) As String
       Dim VorBlattAltName As String
       Dim Bl() As String, BlI As Integer
      
       VorBlattAltName = "kw" & (Mid$(BlattAltName, 3) - 1)
       Bl$ = Split(strFormel, VorBlattAltName)
       strFormel = Bl$(0)
       For BlI = 1 To UBound(Bl$)
         If Bl$(BlI) Like "[0-9_A-Za-z]*" Then
           'Es liegt ein anderer Bezeichner vor, der mit dem VorBlattAltName beginnt
           'Keine Blattnamensersetzung!
         Else
           'Es handelt sich um den VorBlattAltName-Blattnamen.
           'Ersetze ihn durch den BlattAltName:
           strFormel = strFormel & BlattAltName & Bl$(BlI)
         End If
       Next BlI
       Formel_BlattAendern = strFormel
    End Function
    Sub Druckbereich_festlegen()
        ActiveSheet.PageSetup.PrintArea = "$A$2:$X$5"
    End Sub
    
     
    Exl121150, 26. Mai 2021
    #5
    1 Person gefällt das.
  6. sengir_cdv Neuer User
    Das werde ich nächste Woche direkt mal einbinden und testen.

    Vielen vielen Dank dir dafür :)
     
    sengir_cdv, 28. Mai 2021
    #6
  7. Exl121150 Erfahrener User
    Hallo,

    ich habe die enthaltenen Makros nochmals ausführlich getestet und festgestellt, dass ein paar Fehler enthalten waren, die unter bestimmten Umständen zu Tage treten. Ich hoffe, ich habe jetzt alle Fehler ausgemerzt.
    Die korrigierte und in einigen Details verbesserte Datei liegt bei. Der enthaltene Makrocode:
    Code:
    Option Explicit
    
    Sub NeuesBlatt_Anfuegen()
      Dim WsAlt As Worksheet
      Dim strNameAlt As String, strNameNeu As String
      Dim rgFormelZelle As Range, strFormel As String
     
      Set WsAlt = GetLetztesKwBlatt()
      If WsAlt Is Nothing Then MsgBox "Kein 'kw<nn>'-Blatt": Exit Sub  'Falls kein "kw<NN>"-Blatt vorhanden ist
     
      With WsAlt
         strNameAlt = .Name
         strNameNeu = "kw" & (Mid$(strNameAlt, 3) + 1)
         .Copy After:=Worksheets(Worksheets.Count)
      End With
     
      With Worksheets(Worksheets.Count)
         .Name = strNameNeu
        
         'Ersetze in den Formeln alle Bezüge auf den Vorvorblattnamen auf den Vorblattnamen(=strNameAlt):
    '--> 'Das kann nur dann funktionieren, wenn bereits im WsAlt-Blatt Bezüge auf das VorBlatt enthalten waren!!
        
         With .UsedRange.SpecialCells(xlCellTypeFormulas)
        
            For Each rgFormelZelle In .Cells
              strFormel = rgFormelZelle.Formula
              'Testen, ob es sich tatsächlich bei allen entdeckten Stellen um den strNameAlt-Blattnamen handelt:
              rgFormelZelle.Formula = Formel_BlattAendern(strFormel, strNameAlt)
            Next rgFormelZelle
          
         End With
      End With
    End Sub
    Function GetLetztesKwBlatt() As Worksheet
      Dim Ws As Worksheet
      Dim Nr As Integer, NrL As Integer
     
      On Error GoTo Err_KwBlatt
      Set GetLetztesKwBlatt = Nothing: NrL = 0
      For Each Ws In Worksheets
        If Ws.Name Like "kw[0-9]*" Then
          Nr = Mid$(Ws.Name, 3)  '<-- Error abfangen, falls nicht numerisch
          If Nr > NrL Then NrL = Nr: Set GetLetztesKwBlatt = Ws
        End If
    Nxt_KwBlatt:
      Next Ws
      Exit Function
    Err_KwBlatt:
      Resume Nxt_KwBlatt
    End Function
    Function Formel_BlattAendern(strFormel As String, BlattAltName As String) As String
       Dim VorBlattAltName As String
       Dim Bl() As String, BlI As Integer
      
       VorBlattAltName = "kw" & (Mid$(BlattAltName, 3) - 1)
       Bl = Split(strFormel, VorBlattAltName)
       strFormel = Bl(0)
       For BlI = 1 To UBound(Bl$)
         If Bl$(BlI) Like "[0-9_A-Za-z]*" Then
           'Es liegt ein anderer Bezeichner vor,
           'der zwar mit dem VorBlattAltName beginnt, aber noch weitere Zeichen enthält:
           'Keine Blattnamensersetzung!
           strFormel = strFormel & VorBlattAltName
         Else
           'Es handelt sich um den VorBlattAltName-Blattnamen.
           'Ersetze ihn durch den BlattAltName:
           strFormel = strFormel & BlattAltName
         End If
         strFormel = strFormel & Bl$(BlI)
       Next BlI
       Formel_BlattAendern = strFormel
    End Function
    Sub Druckbereich_festlegen()
        ActiveSheet.PageSetup.PrintArea = "$A$2:$X$5"
    End Sub
    
     
    Exl121150, 29. Mai 2021
    #7
    1 Person gefällt das.
  8. sengir_cdv Neuer User

    Funktionen im Datenblatt

    Grüsse Anton,
    vielen Dank erst einmal für die Anpassung :)
    Ich habe meine Probedatei genommen und einfach untereinander weitere Namen geschrieben, welcher er dann ohne Probleme mit dem Makro übernommen hat.
    Nun habe ich das Makro bei mir eingefügt 1:1 und auch die 2 Boxen.
    Ich erhalte nun eine Fehlermeldung "Laufzeitfehler 9" - Index ausserhalb des gültigen Bereiches.
    Wende ich den debugger an zeigt er die Zeile "strFormel = Bl(0)" an.
    Kann ich dies evtl. eigenständig lösen oder erweitern? oder auf noch etwas achten?

    Vielen Dank schon einmal vorab :)
     
    sengir_cdv, 2. Juni 2021
    #8
  9. Exl121150 Erfahrener User
    Hallo,
    1) ich nehme an, dass du die enthaltenen Makros markiert, kopiert und in der neuen Excel-Datei wieder in einem allgemeinen Codemodul (im Projektexplorer unterhalb von "Module") eingefügt hast.
    (Bei der alternativen Methode, dem händischen Abtippen, kann sich nämlich leicht irgendwo ein Fehler einschleichen).
    2) hast du eh die neueren Makros, die ich dir im Posting #7 vom 29.5. übermittelte, genommen. Die Makros im Posting #5 vom 26.5. enthalten nämlich Fehler, die unter bestimmten Umständen zuschlagen können. Sie haben allerdings nichts mit dem im Folgenden geschilderten Problem zu tun.
    Zu einem Laufzeitfehler "Index außerhalb ..." kann es an dieser Stelle nur kommen, wenn der Index 0 im Array Bl(...) nicht vorkommen darf, weil die dazugehörige Deklaration im Dim-Statement anders lautet.
    Dieses Dim-Statement lautet (innerhalb der Funktion "Formel_BlattAendern"):
    Dim VorBlattAltName As String
    Dim Bl() As String, BlI As Integer
    Damit wird eine dynamische Array-Variable "Bl" definiert, die String-Werte aufnehmen darf. "Dynamisch" bedeutet dabei, dass die Anzahl der String-Werte, die sie enthalten kann, zum Zeitpunkt der Dim-Deklaration noch nicht feststeht (deshalb das leere Klammernpaar).
    Diese Anzahl wird in der folgenden Zuweisung (im Makro 2 Zeilen später) festgelegt:

    Bl = Split(strFormel, VorBlattAltName)
    Die Split-Funktion macht dabei Folgendes: Sie nimmt den String, der in der Variablen "strFormel" enthalten ist, und schneidet (=splittet) alle Teilstücke heraus, an denen der Inhalt der Stringvariablen "VorBlattAltName" gefunden wurde. Die so verbleibenden Reststücke werden durchnummeriert, wobei mit der Nummerierung bei 0 begonnen wird.
    Angenommen: In "strFormel" ist folgender Formelstring enthalten: "='kw4'!A2" und in "VorBlattAltName" folgender String-Wert: "kw4". Dann weist vorgenannte Split-Formel der Array-Variablen Bl() folgende Werte zu: Bl(0)="='"; Bl(1)="'!A2"
    Daraus folgt aber, dass es stets ein String-Array-Element "Bl(0)" muss.
     
    Exl121150, 3. Juni 2021
    #9
  10. sengir_cdv Neuer User
    Guten Abend,
    ich habe in der Tat beides probiert.
    Einmal natürlich die Formel kopiert und unter Modul eingefügt, korrekt :)
    Auch habe ich die neuere Version von dir genommen, vielen Dank :)
    Ebenfalls habe ich versucht einfach die Beispieldatei zu nehmen und dort den Aufbau zu kopieren.
    Das habe ich allerdings von zuhause aus gemacht, daher hat er die Formeln nicht übernommen.
    Ich habe einfach die ersten beiden Zeilen eingetragen (alles andere gelöscht).
    Die erste Zeile enthält eine kwx!xx Formel - welche angepasst wird.
    Ab der 2. Zeile klappt dies allerdings nicht mehr - obwohl da auch der gleiche Wert (bzw die gleiche Formel nur mit einer anderen Zelle) enthalten ist.
    Beim Rest des Blattes ist alles "leer" an Formeln.
    Soweit ich den letzten Teil von dir verstanden habe (sorry ich konnte da nicht alles verstehen) macht er aber genau das - es soll alles erhalten bleiben (in deinem Beispiel = !A2). nur der kwX soll angepasst werden.
    Ich dachte erst es gibt Probleme weil ich halt mehrere (als Beispiel) kw4!yx drin habe; aber in der Beispieldatei - wenn ich da untereinander mehrere nehme - klappt es ohne Probleme.

    Kann ich dich evtl in einem Discord antreffen, wo ich dir ggf das Dokument einmal zeigen kann?
    Vielleicht kann ich dir das dann einfach besser beschreiben bzw zeigen.
    Ansonsten würde ich sonst die Beispieldatei einfach mal soweit erweitern oder "leer" machen, das der Fehler auftaucht und ich die Datei auch öffentlich zeigen kann.

    Ansich kann ich auch kein Problem erkennen und bin sehr dankbar das du dich dem Problem hier weiter annimmst.
    Vielen vielen Dank dafür.
     
    sengir_cdv, 3. Juni 2021
    #10
  11. Exl121150 Erfahrener User
    Hallo,
    darum möchte ich dich bitten - denn ich kann mir dein Problem nicht erklären.
     
    Exl121150, 4. Juni 2021
    #11
  12. sengir_cdv Neuer User
    Grüsse,
    anbei die entsprechende Datei.
    Ich habe mal die Formeln mit =kw!xy blau im Hintergrund gemacht, sowie Zellen in denen das Wort KW auftaucht rot).
    Nach unten hin geht die Tabelle immer gleich weiter.

    Vielen Dank.
     
    sengir_cdv, 4. Juni 2021
    #12
  13. Exl121150 Erfahrener User

    Funktionen im Datenblatt

    Hallo,

    ich habe die Datei getestet. Resultat: Excel weist bei Verbundzellen mit Formeln nicht bloß die 1.Zelle als Formelzelle aus, sondern auch die restlichen Zellen des Verbundes, in denen gar keine Formel enthalten ist.
    So geschehen zB. im Zellbereich M2:O2. Dieser Zellverbund enthält die Formel "='kw3'!M2+7". Diese Formel ist aber tatsächlich nur in Zelle M2 enthalten und nicht auch in N2 und O2. In N2 und O2 ist tatsächlich überhaupt keine Formel enthalten, obwohl sie in der Liste der Formelzellen geführt werden.
    Die beiden blauen Makrozeilen habe ich hinzugefügt, damit Zellen, die zwar in der Formelliste enthalten sind, aber keine Formel enthalten, übergangen werden:

    strFormel = rgFormelZelle.Formula
    'Testen, ob es sich tatsächlich bei allen entdeckten Stellen um den strNameAlt-Blattnamen handelt:

    If Len(strFormel) Then
    rgFormelZelle.Formula = Formel_BlattAendern(strFormel, strNameAlt)
    End If

    Damit müsste das Makro jetzt funktionieren.
    Was wieder einmal mehr beweist: Finger weg von Verbundzellen, sie verursachen nur Ärger.

    In der beiliegenden Datei habe ich bereits das Makro um das IF-Statement (blaue Makrozeilen) ergänzt.
     
    Exl121150, 4. Juni 2021
    #13
  14. sengir_cdv Neuer User
    Grüsse,
    perfekt das passt genau so :)

    Vielen vielen Dank dafür!
     
    sengir_cdv, 6. Juni 2021
    #14
Thema:

Funktionen im Datenblatt

Die Seite wird geladen...
  1. Funktionen im Datenblatt - Similar Threads - Funktionen Datenblatt

  2. Variabler Blattbezug bei SUMMEWENNS Funktion

    in Microsoft Excel Hilfe
    Variabler Blattbezug bei SUMMEWENNS Funktion: Guten Tag, Gibt es eine Möglichkeit den Blattbezug einer SUMMEWENNS Funktion variabel zu gestalten? Ich habe eine Datei mit 3 Tabellenblättern auf dem dritten Blatt sollen Daten aus den zwei...
  3. Index und Aggregat Funktion

    in Microsoft Excel Hilfe
    Index und Aggregat Funktion: Liebe Community, ich habe folgendes Problem, welches ich meine, dass es mit index und aggregate gelöst werden kann. Im Reiter A habe ich eine Tabelle mit einer ID in Spalte A und Datumin Spalte...
  4. Automatische Ermittlung der Anrede mit WENN Funktion

    in Microsoft Excel Hilfe
    Automatische Ermittlung der Anrede mit WENN Funktion: Hallo zusammen, ich komme bei meiner Tabelle momentan nicht weiter und bin etwas am Verzweifeln. Meine Tabelle hat 3 Spalten: 1 - Anrede 2 - Geschlecht 3 - Sprache Jetzt soll bei Eingabe des...
  5. Kreuzwert ermittel mit Funktion

    in Microsoft Excel Hilfe
    Kreuzwert ermittel mit Funktion: Hallo, Ich habe eine Tabelle ähnlich wie im Anhang erstellt. An den Zeilen-/Spaltenenden ist jeweils eine Auflistung mit Vor- und Zuname getrennt durch ein Komma (kann auch geändert werden wenn zB...
  6. Textkette-Funktion

    in Microsoft Excel Hilfe
    Textkette-Funktion: Hallo allerseits, ich wollte via Textkette-Funktion Namen in einem Excel-Blatt erstellen. Das hat in fast allen Zeilen wunderbar funktioniert, nur in einigen wiederholt sich das Ergebnis immer...
  7. Word 03 Standard-Schrifteinstellung ohne Funktion

    in Microsoft Word Hilfe
    Word 03 Standard-Schrifteinstellung ohne Funktion: Hi Leute, wenn ich in meinem Word 2003 unter Format - Zeichen, die Schrift Arial 11pt. als Standardschrift eintragen will, also dass in Zukunft Word immer mit Arial als Schrift startet,...
  8. Word Standard-Schrifteinstellung ohne Funktion

    in Microsoft Word Hilfe
    Word Standard-Schrifteinstellung ohne Funktion: Hi Leute, wenn ich in meinem Word 2003 unter Format - Zeichen, die Schrift Arial 11pt. als Standardschrift eintragen will, also dass in Zukunft Word immer mit Arial als Schrift startet,...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden