Excel, Formel indirekt bezogen auf Tabellennamen

[steterfreund]

Hallo Leute,

ich stelle kurz die Situation dar:
Tabellenblatt "Übersicht" hat in Spalte "A" die Namen der Mitarbeiter.(Spalte "A2" bis "A150")
In der Spalte "AD" ist der Name des jeweiligen Tabellenblattes angegeben, das erstellt wird, per VBA.("AD2" bis "AD150")
Das Tabellenblatt "Übersicht" zeigt die Summen, welche in den einzelnen Tabellenblättern gewonnen werden.
Namen der Tabellenblätter: Fahrer 1, Fahrer 2, etc.





Tabellenblatt"Vorlage" gibt vor, welcher Inhalt in jedes Tabellenblatt beim Erstellen eingefügt wird

Der Grund, weshalb das Tabellenblatt nicht nach dem Mitarbeiter benannt ist, liegt an den Formeln der Übersicht, z.B.
=INDIREKT("'" & AD2 & "'!G5") (diese Formel gilt für "E2")
So können Namen ohne Umstände verändert werden.
Da das jeweilige Tabellenblatt aber nicht den Namen des Mitarbeiters hat, sollte zur Orientierung in der Spalte "B1" von jedem Tabellenblatt der Name des Mitarbeiter stehen.
Also benötige ich eine Formel (kein VBA!), die beim Erstellen der einzelnen Blätter sagt:
Füge in "B1" des Blattes "Fahrer 1" den Namen ein, der in Spalte "A2" steht.
Füge in "B1" des Blattes "Fahrer 2" den Namen ein, der in Spalte "A3" steht.
Sollten sich die Namen ändern, sollte natürlich sich auch in "B1" der Name anpassen.



Ich hoffe, es kommt verständlich rüber.
Beschäftige mich erst seit ein paar Tagen mit Formeln und VBA.
An dieser Stelle einen riesen Dank an Janush. Ich hatte auf ein altes Thema geantwortet, und ohne ihn hätte nix geklappt.
Hab natürlich noch andere Fragen, da die Tabelle bei mir in der Firma allen Vorgesetzten wichtige Informationen übersichtlich dargestellt werden sollen. Aber lieber Schritt für Schritt.
Danke vorab.
Emilio

 

[cumulonimbus8]

Ich habe es zugegeben nur überflogen…

Was du da bastelst ist glockenklar Job einer Datenbank.

Und Formeln mit solchen Kriterien sind naturgemäß dynamisch, so weit ich das begreife müsse das aber statisch sein was am Ziel ankommen soll.
Um zu lügen - du müsstest den Index des Blattes (seine laufenden Nummer) kenne um daraus die Zeile zu berechnen aus de gelesen wird. Dieser Index ist nicht mit Formeln erfragbar.

CN8

 

[Sun_set_1]

Bevor das Tabellenblatt erstellt wird, existiert die Zelle ja gar nicht. Somit kann das über eine Formel nicht funktionieren.
Es bleibt letztlich VBA. Wo ist das Problem? Du schreibst, dass die Tabellenblätter sowieso per VBA erzeugt werden?

In dem Code muss dann eine Funktion rein, die die Zellen in Spalte A durchläuft. Bzw sonst wie die relative Position ermittelt. Wie cumulonimbus8 sagte, bspw Tablenblatt Index 1 = Range.Cell(1, 1), for each + 1
etc pp.

 

[steterfreund]

Hallo Sun_set_1 und cumulonimbus8,

erst mal Danke für die schnelle Rückmeldung.
@Sun_set_1: Bin mir nicht sicher, ob in der Firma laufende Makros erwünscht sind.
Soweit ich es überblicke, erzeuge ich mit dem Makro die Tabellen, kann dann im .xlsx Format speichern und fertig.
Beim ständigen ändern des Namens müsste das Makro "ständig laufen", wenn das so richtig ausgedrückt ist.
Wenn es anders nicht geht, werde ich es so umsetzten, ganz klar.
Zusatzinfo: Das Makro, welches ich gefunden hatte, erzeugte der Tabellen durch Abfrage der Namen in Spalte"A"
Also waren die Tabellenblätter nach den Fahrern benannt.
Dann gab mir Janush die Formel: =INDIREKT("'" & A2 & "'!G5")
Somit griff die Formel auf die Fahrernamen zu. Bei Änderung der namen durch Personalwechsel hätte auch jedes Mal das Tabellenblatt geändert werden müssen.
Also habe ich die Bezeichnung Fahrer 1,etc. in AD eingetragen und die Formel auf =INDIREKT("'" & AD2 & "'!G5") geändert.
Wenn ich die Tabelle final nochmals erzeuge, werde ichs wohl ebenso machen, was kein großer Umstand ist.

Nun wieder zur Frage:
Ok, nur um sicher zu gehen, das ich mich richtig ausgedrückt habe, kurz eine Grafik.
Wenn auch dann noch nicht möglich, dann gerne VBA. Ist dann halt so.

Beim Erzeugen des Blattes "Fahrer 1" soll in"B1" der Name rein, der in Spalte "A" von "Übersicht" in der Spalte die zu "Fahrer 1" gehört, steht.


Oder ist es etwa so, das eine Formel den Rechenweg nur "vorgeben " kann, ihn sich aber nicht "holen" wie in diesem Fall?

Ergänzung (12. März 2018)

Hab heute Nacht darüber geschlafen, und ich denke, jetzt verstehe ich....
Tabellenblatt "Vorlage" ist nicht in Spalte "A" von Übersicht..
Was auch nicht möglich ist, da das Makro sonst ne Fehlermeldung raushaut.
So richtig?

Gruß..

 

[Sun_set_1]

Du könntest eine neue Spalte A in Übersicht Einfügen. Diese dupliziert Spalte AD.

Anschließend geht ganz normal ein SVERWEIS, Du müsstest nur noch den Tabellenblatt Namen in eine Zelle auf die jeweiligen Blätter schreiben.

Also "Fahrer1" auf dem Blatt Fahrer1. Irgendwo, wo es nicht stört. Das dann im SVERWEIS als Suchkriterium nehmen, dann steht oben der richtige Name. Zum Schluss blendest Du Spalte A in Übersicht ganz einfach aus, und für die Chefs sieht alles aus wie immer.

Wieso geht keine direkte Verlinkung der Zellen? Also in Fahrer1 Zelle B1 =Übersicht!A2 ?

Ansonsten kann man den Tabelleblattnamen doch automatisiert abfragen. Schau mal hier:

https://www.tabellenexperte.de/blattnamen-in-excel-ermitteln/

Somit würde es auch bei Änderungen der Bezeichnungen weiter funktionieren.

 

[steterfreund]

Vorlage 1 in Spalte "A" eingefügt



Name wird in "B1" übertragen



jedoch auch in anderem Tabellenblatt..

 

[Sun_set_1]

Genau, der SVERWEIS vermeidet das Problem.

Ab in das Tabellenblatt. Die Formel aus meinem Link anschauen, wie man an den Namen kommt.
Diese Formel ist dein Suchkriterium bei SVERWEIS. Dann gehts.

Also in Übersicht eine Spalte A einfügen, welche die Werte aus AD enthält. "Fahrer1", "Fahrer2", etc.

Die Funktion zum ermitteln des Tabellenblattnamens als Suchkriterium innerhalb einer SVERWEIS auf den Blättern Fahrer1, Fahrer2, etc jeweils in B1.

Komplett hier:
https://www.tabellenexperte.de/blattnamen-in-excel-ermitteln/

Du musst dann halt diese Formel als Suchkritierium in deinen SVERWEIS packen. Das sieht komplizierter aus, als es ist

=SVERWEIS(RECHTS(ZELLE("dateiname").....FINDEN(...)....-LÄNGE(....);Übersicht!A:B;2;FALSCH)

Was Excel dann macht:

Er ermittelt den Tabellenblattnamen, nimmt diesen, dursucht auf Übersicht die (neue) Spalte A und übernimmt den zugehörigen Wert aus Spalte B (der wirkliche Name).

Das wäre dann mMn genau was Du brauchst. Denk drann, in die Spalte A nicht Werte einfügen - sondern auf AD verlinken! Also Formel für

A2:

=AD2

A3:

=AD3

usw. usf.

 

[steterfreund]

Stimmt, gar nicht so schwer, wenn man an die Hand genommen wird...
Trotzdem passt was nicht in B1.
schau mal...
Hab zur Vereinfachung alles in Spalte B. Würde ich natürlich noch verändern.
(Bin im Geschäft, deshalb der blaue Balken.)

 

[Sun_set_1]

Sorry ich hatte auch einen Flüchtigkeitsfehler, was ich postete, war mit Verwendung der Hilfsspalte.
(in Deiner Formel fehlt der Operand ("minus") zwischen LÄNGE und FINDEN, das ist der Fehler, aber ignoriere das..)

Besser ist:

=SVERWEIS(RECHTS(ZELLE("dateiname");LÄNGE(ZELLE("dateiname"))-FINDEN("]";ZELLE("dateiname")));Übersicht!A:B;2;FALSCH)

Kopiere die mal so auf die jeweiligen Tabellenblätter in B1. Müsste dann direkt funktionieren und Du brauchst dann auch die Hilfsspalten für Finden und Länge nicht mehr, das ist in dem Link nur zur Veranschaulichung, damit "neue" Leute wissen, was man da eigentlich wie berechnet.

Das hast Du ja soweit verstanden, von daher... nimm die obere Formel

 

[steterfreund]

Hat bestens geklappt




...aber bei Fahrer 2..






in jedes B1 manuell einfügen..??
Über Vorlage beim Erstellen der Tabellenblätter über VBA mit dieser Fomel in "B1" von Vorlage nicht möglich,
da die Zeile "Vorlage" nicht vorhanden ist..

Sind aktuell in der Testphase.
Ab nächsates Jahr ändern sich die gesetzlichen Vorgaben für die Dokumentation.
Extern wollen es Chef/GF nicht vergeben, da zu teuer.
Deshalb wird diese Tabelle (Datenbank) möglicherweise noch erweitert für andere NL.



..also wohl doch über VBA.
Hab heute mit unserer EDV gesprochen. Glücklich waren se net,... aber bei der Dringlichkeit wärs möglich...

 

[Sun_set_1]

Na klar manuell in jedes
Spaß, siehe unten in der Quote

Damit das automatisch geschieht, muss die Formel in VBA ans Ende des Makros. Das Tabellenblatt muss erst erstellt sein, damit die Funktion ZELLE() funktionieren kann.

Wenn ein Makro jetzt hingeht und diese Vorlage dupliziert, wird es die Formel während Erstellung des Tabellenblatts einfügen und berechnen wollen. Da Zelle() sich aber selbst auswertet, das Tabellenblatt an sich aber zu dem Zeitpunkt noch garnicht existiert, führt dies zu einem Fehler und das Makro läuft vrmtl. in einen Error-Catch.

Ergo, erst muss das Tabellenblatt im Code erstellt und abgeschlossen sein, anschließend die Formel in B1 einfügen. Dürfte für die IT aber keine große Sache sein, das sind zwei Zeilen Code. Geht mit Google vllt auch selbst

Schick der IT die Formel und sag, die möge in jedes Tabellenblatt mit dem Namen Fahrer* in B1 eingefügt werden. Wenn Du nen netten Programmierer hast, ist das in einer Minute durch. Und schick die File mit, damit die sehen, dass da schon ein Makro läuft.

Aber, für die Zwischenzeit:

Sparen Sie Zeit bei der Eingabe von Formeln, die sich in der gleichen Zelle mehrerer Tabellenblättern wiederholen! Dazu gehen Sie folgendermaßen vor:

Markieren Sie in einem der Tabellenblätter die Zelle, in der die Formel erscheinen soll.
Fassen Sie alle Tabellenblätter, in die Sie die Formel eingeben möchten, zu einer Gruppe zusammen. Dazu klicken Sie mit gedrückt gehaltener STRG-Taste die entsprechenden Tabellen im Blattregister mit der Maus an.


Geben Sie in die markierte Zelle die benötigte Formel ein. Die Formel wird nun in der gewählten Zelle in allen Blättern zugleich eingetragen.
Mit einem Mausklick auf eines der Tabellenregister heben Sie die Gruppierung wieder auf und können die Tabellenblätter wieder wie gewohnt einzeln bearbeiten.

https://www.computerwissen.de/offic...n-mehrere-blaetter-gleichzeitig-eingeben.html

 

[steterfreund]

ok, bin grad am Erstellen der kompletten Tabelle.
Dauert ne Weile...
Und muss alleine gehen, unsere EDV ist bei der Holding angestellt.
Die macht nix ohne Ticket..

 

[Sun_set_1]

Ich kann Dir bei Zeiten den Code geben, aber den am Ende des Makros zu kopieren, ohne dass ich das Makro gesehen habe, ist immer so ne Sache. Aber prinzipiell dürfte da bei so einer einfachen Aufgabe nichts passieren.

Ansonsten müsste man Blätter bei Mehrfachauswahl mit STRG+Shift markieren können. Also B1 auswählen, STRG+Shift gedrückt halten, Blatt Fahrer 1 markieren, ans Ende springen, Blatt Fahrer 299 anklicken. Anschließend müssten alle markiert sein. STRG+Shift loslassen, Formel rein, Enter.

 

[steterfreund]

Nein,nein..
natürlich schicke ich das Makro.
Wollt nur nicht mehrere Baustellen gleichzeitig haben, sonst blick ich bei den Anworten nicht mehr durch..
Hab sowieso noch nen Anschlag auf dich (euch) vor..
Gibt da noch zwei Kleinigkeiten, die ich benötige.
Aber wie gesagt, erst möchte ich das hier verstehen.
Die beiden anderen Dinge hätte ich aber nur in Verbindung mit dem Makro gepostet, da ich mögliches Nacharbeiten seitens dir (euch) natürlich vermeiden möchte.
Ihr seid echt Geil, Leute, danke dafür.
Ich hab mir da mal wieder was ans Bein gepinkelt...dachte, mach mal kurz ne Tabelle..

Ergänzung (12. März 2018)

Hab jetzt mal in alle Tabellen in B1 die Formel rein wie beschrieben.
Ergebnis:
Öffne ich die Arbeitsmappe und ist grad Fahrer 2 offen, sehe ich seinen Namen in B1.


Wechsle ich dann zu Fahrer 1, sehe ich in B1 ebenfalls den Namen von Fahrer 2.


Übersicht:


Da bringt auch speichern nix.
Schließe und öffne ich die Arbeitsmappe, und ist der Reiter bei Fahrer 1 geblieben, sehe ich jetzt den richtigen Namen.
Wechsle ich dann auf Fahrer 2, sehe ich Fahrer 1.
Ändere ich den Namen, bekomme ich #NV.
Schließen, öffnen, dann wie vorher beschrieben.
Bleibt das so?

Gruß
SF

 

[cumulonimbus8]

Um mich völlig blöd zu stellen…

Was bringt mir der Name des aktuellen Tabellenbaltts wenn ich und im ersten Blatt etwas abzählen zu können seine laufende Nummer brauche?
Und was bringt mir SVERWEIS wenn der Witz der Übung doch der sein soll einem Blatt n (mehr n+1, da wir ja das 1. Datenblatt haben) den unbekannten Namen des Fahrer einzuimpfen? SVERWEIS braucht ein Suchkriterium das ich nicht habe!

CN8

 

[steterfreund]

Also, folgende Situation:
Ich habe keinen Plan, deshalb brauch ich euch, und ihr wollt helfen.
Da ich aber keinen Plan habe, weis ich auch nicht, wann Schluss ist.
Was nicht geht, das geht nicht.
Dann weg damit!
Punkt.
Will da kein großes Ding draus machen. dachte, wäre doch gut, wenn bei einem geöffneten Tabellenblatt nach ner Ablenkung noch kurz zu sehen wäre, "bei wem war ich grad?, ah, beim Max, weil "B1" mir das sagt." Mehr nicht.
Dan lasst uns das Thema abschließen, ich brauch eure Geduld noch..
Schick gleich mal das Makro zur Ansicht.
Dann schick ich getrennt, was ich noch brauche, damit es übersichtlich bleibt (für mich)

Gruß
SF

Ergänzung (12. März 2018)

Sub Anlegen()
Dim Wiederholungen As Long
Dim wksL As Worksheet
Set wksL = Worksheets("Übersicht")
Application.ScreenUpdating = False
Worksheets("Übersicht").Activate

For Wiederholungen = 1 To Range("A65536").End(xlUp).Row

If Cells(Wiederholungen, 1) <> "" Then
Worksheets("Vorlage").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = wksL.Cells(Wiederholungen, 1).Text

Else
Exit Sub
End If
Worksheets("Übersicht").Activate
Next

Set wksL = Nothing

Application.ScreenUpdating = True
End Sub


Quelle: https://www.computerbase.de/forum/t...tabellenblaetter-per-makro-erstellen.1404613/
#3
Danke unbekannterweise

Gruß
SF

Ergänzung (12. März 2018)

Was ich jetzt noch brauche ist
1. Den Link auf jedes Tabellenblatt, ohne es einzeln einfügen zu müssen.
Runterziehen ist nicht.

Ergänzung (12. März 2018)

Als 2. noch folgendes...

Um diese Tabelle benutzerfreundlicher zu gestalten, benötige ich noch folgende Lösung:
Die gesetzlich definierten Übertretungen/Verstöße finden sich im Tabellenblatt Nachschlagewerke.
Diese sind über Datenprüfung/Liste als Vorlage für die Tabellenblätter angelegt, wie auch die Monate.
Ansicht Tabellenblatt „Nachschlagewerke“


Ansicht Tabellenblatt „Fahrer 1“


Bei den Summen für z.B. „Fahrer Januar“ ist die Formel noch einfach gewesen: =SUMMEWENN(A5:A601;"Januar";D5601)
Die Summe muss schließlich in „G5“
Ich möchte nun, dass der Einzelbetrag für den jeweiligen Verstoß (Spalte B) mit „Anzahl“ in Spalte “C“ multipliziert wird.
Jeder Verstoß hat einen festen Betrag für Fahrer und/oder Unternehmen.
In diesem Beispiel also:
„E5“ = „Arbeitspause nicht eingehalten“ (50€) multipliziert mit Zahl in „C5“
„D7“ = „Manuelle Tachographen-Nachträge nicht vorhanden“ (25€) multipliziert mit Zahl in „C7“
„E7“ = „Manuelle Tachographen-Nachträge nicht vorhanden“ (25€) multipliziert mit Zahl in „C7“
Jedoch kann natürlich jeder Verstoß in jede Zeile der Spalte „B“ eingetragen werden.
Geht ja bis Dezember.
Wie soll die Formel aussehen, dass in Spalte „B“ beim Auswählen des Verstoßes der hinterlegte Betrag herangezogen wird.
Möglicherweise im Tabellenblatt „Nachschlagewerke“ irgendwie dem Verstoß zuordnen?
In der Summe sind es ca. 15 definierte Verstöße.
Und wichtig: Kann ich nach Erstellen und Nutzung dieser Tabelle die jeweiligen Summen neuen Verstößen, sobald sich möglicherweise gesetzlich etwas ändert, noch nachtragen, so wie die Erweiterung der Verstöße an sich im Tabellenblatt „Nachschlagewerke“ je problemlos möglich ist?
Fragen über Fragen…

Gruß
SF

Ergänzung (12. März 2018)

sorry, hab da ein grins-Gesicht , das ich über Bearbeiten nicht weg bekomme..
war nicht beabsichtigt.


Gruß
SF

 

[Sun_set_1]

Um mich völlig blöd zu stellen…

SVERWEIS braucht ein Suchkriterium das ich nicht habe!

CN8

Fahrername und zugehöriger Tabellenblattname stehen auf der Übersicht. Nun soll auf dem jeweiligen Fahrerblatt der Name aus Übersicht angezeigt werden.

Somit muss lediglich der Name des aktuellen Tabllenblattes ermittelt und damit in der Übersicht gesucht werden. Das Suchkriterium lässt sich somit mittels Zelle(„Dateiname“) ermitteln. Verstehe gerade nicht, woran es hakt?

@steterfreund

Okay, nichts kompliziertes. Schaue ich mir morgen mal an.
Ich hab Dir oben geschrieben wie Du die alle auf auf einmal einfügst, mit Strg+Shift!

 

[steterfreund]

Einfügen in alle Blätter klappt bestens...
Zwar nicht mit gleichzeitig gedrückten Tasten, aber das ist vielleicht Hersteller-abhängig.
Dell Vostro 1720 (zugegeben nicht der jüngste, bin aber täglich als Disponent ständig online, das reicht.)
1. Markieren "B1" "Fahrer 1".
Tabellenblatt "Fahrer 1" ist dadurch bereits markiert.
2. STRG+Klicken mit der linken Maustaste bei und ans Ende.
3. Dann Shift drücken und letzten Fahrer auswählen
4. Einfügen bei "B1" "Fahrer 299"

4. Date schließen und wieder öffnen.

kein Unterschied..

Gruß
SF

Ergänzung (13. März 2018)

Und das ich es richtig mache, bestätigt sich durch das Löschen.
Hab's locker schon 50 Mal neu eingefügt und auf dem gleichen Wege wieder gelöscht.
Gruß
SF

 

[Sun_set_1]

Hey,

nein hast alles richtig gemacht, habe es gerade überprüft. Die Lösung denkbar einfach, bitte nach Einfügen F9 drücken.

Dadurch berechnet Excel alle Formeln neu. Beim Einfügen wird nur die Erste berechnet. Danach das Ergebnis quasi als Cache mit kopiert. F9 nach dem Einfügen behebt das.

Dann wäre das gelöst, den Rest muss ich mir später anschauen, sieht aber nach normalen Aufgaben aus.

Grob würde ich sagen, Du brauchst nur ZÄHLENWENN() Zählenwenn gibt beispielsweise "2" zurück wenn du nach Fahrtzeitüberschreitung suchst und diese in einem Bereich 2x vorkommt.

Gruß von einem Road'ler zum anderen

 

[steterfreund]

Hallo Road'ler,
hätte jetzt lieber ein Radler.
Danke erstmal.
Hatte spasseshalber "F5" gedrückt, als ich an verzweifeln war...
Wusste jedoch nicht, das es auch eine Aktualisieren-Taste bei Excel gibt.
Das mit der Formel versuche ich mal in den nächsten Tagen, wenn ich dazu komme.
Frage mich jedoch, wo ich den Grundbetrag hinterlege, wen ich einen Verstoß auswähle..

Aber bis dahin, klasse von Dir.

Gruß
SF