bn2023
Goto Top

Excel - Platzhalter in Formel möglich?

Hallo liebe Admin-User,

ich habe eine Excel-Datei (Datei 1), in die MA etwas eintragen können.
Eine zweite Excel-Datei (Datei 2) nimmt sich per Formeln viele Angaben aus den Excel-Dateien (Datei 1).
In dieser 2. Excel-Datei befinden sich viele Formeln, die ich natürlich nicht in jedem neuen Jahr anpassen/ändern will. Daher muss Datei 1 immer den gleichen Namen haben.

Da nun bald der Jahreswechsel ansteht, müssen die MA auch schon etwas in die neue Excel-Datei (Datei 1) für 2024 eingeben können. Leider habe ich das bei der Erstellung nicht berücksichtigt... face-sad

Die Datei, auf die zugegriffen wird (Datei 1) nennt sich z.B. "Eintrag_Kartei_AW.xlsm".
In Datei 2 gibt es nun die folgende Formel:

=WENN($E7="eingetragen";'A:\Dateien\Personal\Eintraege\AW\[Eintrag_Kartei_AW.xlsm]Eingaben'!D9;"")  

Wie Ihr seht, heißt die Datei 1 für den MA "AW" IMMER "Eintrag_Kartei_AW.xlsm".
Wenn ich jetzt im gleichen Verzeichnis (und es geht nur dort) die neue Datei für das Jahr 2024 reinlegen will, muss die Datei für 2023 ja dort auch noch stehenbleiben, da wir ja erst November haben. Sie hätten aber den GLEICHEN Namen, was natürlich nicht möglich ist!!

Wenn es jedoch möglich wäre, einen PLATZHALTER in die Formel zu integrieren, dann würde es gehen... z.B. so:

Datei 1 für 2023: Eintrag_Kartei_AW_2023.xlsm
Datei 1 für 2024: Eintrag_Kartei_AW_2024.xlsm

Platzhalter sollte hier sein "_2023" bzw. "_2024".
Wie würde ich die obige Formel so anpassen können, damit es völlig egal ist, was hinter dem MA-Kürzel steht (hier "AW")?

Grüße an Alle,
BN

Content-Key: 61352699741

Url: https://administrator.de/contentid/61352699741

Printed on: July 21, 2024 at 00:07 o'clock

Member: BN2023
BN2023 Nov 02, 2023 updated at 12:06:33 (UTC)
Goto Top
...sorry Leute... Asche über mein Haupt face-wink

Glaube das würde so auch nicht funktionieren, denn die Formel weiß ja dann nicht, welche der beiden Dateien (Datei 1 od. Datei 2) sie nehmen soll.

Spontan fiel mir eben ein, dass ich eine Jahreszahl in eine Zelle von Datei 2 schreiben könnte.
- dann müsste ich bei jedem neuen Jahr nur diese eine Zahl anpassen
- die Formel sollte dann über diese Zelle vervollständigt werden... das müßte aber irgendwie automatisch passieren.

Ich müßte in der Formel also sagen können "nimm Dir an dieser Stelle den Eintrag aus Zelle A4".
Geht sowas?

Dann müßte ich in Datei 2 für das neue Jahr die Zelle A4 einfach nur an das neue Jahr anpassen.
Und der Name von Datei 1 könnte dann auch einfach hinten mit "_2023" oder später "_2024" enden.

Grüße an Alle,
BN
Member: Blackmann
Blackmann Nov 02, 2023 updated at 12:28:26 (UTC)
Goto Top
Hallo,

irgendwie musst Du ja den gewünschten Dateinamen anpassen.
Hier mal eine sehr triviale Lösung:

optionen

- 2 Optionsfelder 2023 und 2024
- Die Zellverküpfung wurde jeweils auf $D$4 gelegt

Somit steht bei Klick auf 2023 in D4 eine 1 drin, bei 2024 eine 2


Mit VBA müssen wir nun unsere Entscheidungen aus dem Arbeitsblatt auswerten und die Variable DateiName füllen:

Sub Schalten()

 If Cells(4, 4) = 1 Then DateiName = "Eintrag_Kartei_AW_2023.xlsm"  
 If Cells(4, 4) = 2 Then DateiName = "Eintrag_Kartei_AW_2024.xlsm"  

 Cells(6, 4).Value = DateiName
End Sub

Das Makro 'Schalten schaut nun in die Zelle(4.4) - ist im Arbeitblatt D4 und entscheidet dadurch
welchen Inhalt die Variable DateiName bekommen soll:

Bei 1 --> "Eintrag_Kartei_AW_2023.xlsm"
Bei 2 --> "Eintrag_Kartei_AW_2024.xlsm"

In Zelle D6 geben ich den Wert der Variablen DateiName im Arbeitblatt aus.

Für dich ist es nun wichtig, überall dort, wo Du Dich auf die feste Datei beziehst die Variable DateiName einzusetzen.
Member: BN2023
BN2023 Nov 02, 2023 updated at 12:51:10 (UTC)
Goto Top
Hallo @blackman,

wow, das sieht schon sehr gut aus und offenbar kennst Du Dich ja super aus.

Vielleicht habe ich mein Problem auch blöde oder einfach nicht verständlich beschrieben und will das nochmal nachholen.

Es gibt nämlich nicht die Möglichkeit für die MA, hier eine Option auswählen zu können (also 2023 od. 2024).
Einträge werden ausschließlich in Datei 1 von den MA gemacht.
Datei 2 ist nur eine Anzeigedatei und für die MA nicht veränderbar!!
Und Datei 2 ist eine Jahresdatei, die nur für das jeweils laufende Jahr existiert.
Im neuen Jahr gibt es Datei 2 dann für das neue Jahr (hier dann 2024).

Problem:

A) Datei 1:
Für jeweils November und Dezember müssen die Datei 1 aus dem laufenden Jahr und die Datei 1 für das kommende Jahr im gleichen Ordner liegen. Deshalb können sie ja nicht den gleichen Namen haben.

B) Datei 2:
Aber die Formeln in Datei 2 für das laufende Jahr muss irgendwie wissen, aus welcher der beiden "Datei 1" (siehe A) sie die Daten entnehmen soll.
Gleiches gilt für die Datei 2 für das kommende Jahr, die ja dann auch schon existiert.

Allerdings ist es immer so, dass Datei 2 aus dem laufenden Jahr sich immer nur Daten aus Datei 1 für das laufende Jahr nehmen soll... Gleiches dann für das kommende Jahr.

FAZIT:

- Datei 2 aus 2023 nimmt per Formeln nur Daten aus Datei 1 aus 2023
- Datei 2 aus 2024 nimmt per Formeln nur Daten aus Datei 1 aus 2024

- Im Verzeichnis "A:\Dateien\Personal\Eintraege\AW\" liegt die Datei 1 für 2023 und für 2024.

>> Im Grunde muss Datei 2 aus 2023 die Daten aus den gleichen Zellenbereichen BEIDER Datei 1 entnehmen können. Das Gleiche dann für Datei 2 aus 2024. <<

Ich hoffe, ich konnte es jetzt ein bisschen besser erläutern.
Sorry, wenn ich es etwas verkompliziere, aber ich weiß nicht, wie ich es besser beschreiben soll.
Notfalls gerne nochmal nachfragen.

Vielen Dank schon mal für die super Hilfe face-smile

Grüße,
BN
Member: BN2023
BN2023 Nov 02, 2023 at 14:57:39 (UTC)
Goto Top
Hier habe ich mal versucht face-wink das Ganze optisch darzustellen.
Hoffe das macht alles etwas klarer...

foto u-kalender problem

Grüße,
BN
Member: Blackmann
Blackmann Nov 02, 2023 updated at 18:38:01 (UTC)
Goto Top
Schau Dir deine Grafik noch mal genau an ...
Kann es sein, dass sich da Fehler eingeschlichen haben?

N'8
Member: BN2023
BN2023 Nov 03, 2023 updated at 08:26:11 (UTC)
Goto Top
@Blackmann,

danke für Deine Nachricht... Aber um ehrlich zu sein, finde ich jetzt keinen Fehler in meiner Darstellung.
Allerdings ist das ja oft so, dass man in eigenen Aufzeichnungen die Fehler immer wieder übersieht.

In der Datei 2 (die NUR einsehbare, für die MA unveränderbare Datei) für das Jahr 2023 gibt es ein weiteres Tabellenblatt mit dem Namen "Tage". Wenn ich in diesem Tabellenblatt nun z.B. in die Zelle A1 das Jahr 2023 reinschreibe, dann müßten sich die Formeln (z.B. wie die ganz oben zu sehende) doch eigentlich diese Jahreszahl aus dieser Zelle A1 holen können.

Ggf. würde das ja auch über den von Dir beschriebenen Weg (Dein Post vom 02.11.2023 um 13:28:26 Uhr) gehen.

Das Gleiche würde dann für die Datei 2 für das Jahr 2024 gelten. Da würde im Tabellenblatt "Tage" in der Zelle A1 dann eben das Jahr 2024 eingetragen werden.

Somit bekämen die Dateinamen (für Datei 1) in den entsprechenden Formeln immer den Zusatz "2023", bzw. "2024", womit dann auch die passenden Dateien 1 angesprochen werden könnten (für 2023 und 2024).

Oder mache ich hier einen grds. Denkfehler? face-wink

Grüße,
BN
Member: Blackmann
Blackmann Nov 03, 2023 at 08:36:49 (UTC)
Goto Top
DAS hätte ich erwartet:

anderst
Member: Blackmann
Blackmann Nov 03, 2023 updated at 08:50:40 (UTC)
Goto Top
Und gleich nochmal,

Datei2 ist EINE Datei in der Du 2 Tabellenblätter (Namen: 2023 und 2024) zwecks logischer Trennung nutzen möchtest.

Zur Selektion brauchst Du doch nur den jeweiligen Namen des Tabllenblattes heranzuziehen ... der Eintrag in A1 wäre überflüssig... (und unten rechts ach wirklich falsch, oder)?

BG
Mitglied: 8030021182
8030021182 Nov 03, 2023 updated at 08:58:13 (UTC)
Goto Top
=WENN($E7="eingetragen";INDIREKT("'A:\Dateien\Personal\Eintraege\AW\[Eintrag_Kartei_AW_" & Tage!$A$1 & ".xlsm]Eingaben'!D9");"")    
https://support.microsoft.com/de-de/office/indirekt-funktion-474b3a3a-8a ...

Katrin
Member: BN2023
BN2023 Nov 03, 2023 updated at 09:35:50 (UTC)
Goto Top
@Blackmann:

Oh mann... da habe ich zig man auf die Darstellung geschaut und nicht gesehen, dass da in der Datei 2 für 2024 noch 2 x der Wert 2023 steht face-wink

Zu Deinem ersten Satz:
Nein, die Datei2 gibt es für jedes Jahr neu!!
Das Einzige, was ich also in jedem Jahr in dieser Datei anpassen müßte, wäre dann in dem zusätzlichen Tabellenblatt "Tage" das jeweils neue Jahr in die Zelle A1 einzufügen.

@8030021182 und @Blackmann:

Wenn ich die Jahreszahl also im Tabellenblatt "Tage" der Datei 2 für 2023 und 2024 eingefügt habe, könnte ich dann alle Formeln so abändern, wie @8030021182 es hier aufgeführt hat?

Ich verstehe es so, dass jede Formel (es gibt ja nicht nur die Eine, die man ganz oben sieht) dann zunächst im Tabellenblatt "Tage" die Zelle A1 auslesen würde und die jeweilige Formel dann durch diesen Wert ergänzt. Ist das richtig so?

Die Namen der jeweiligen Datei 1 müßten dann so aussehen:
"Eintrag_Kartei_AW_2023.xlsm" (...halt das entspr. Jahr hinter dem Unterstrich).
Richtig?

Danke und Grüße,
BN
Mitglied: 8030021182
8030021182 Nov 03, 2023 updated at 11:47:50 (UTC)
Goto Top
Zitat von @BN2023:
Wenn ich die Jahreszahl also im Tabellenblatt "Tage" der Datei 2 für 2023 und 2024 eingefügt habe, könnte ich dann alle Formeln so abändern, wie @8030021182 es hier aufgeführt hat?
Das was du halt brauchst, würde dann aber einen "Namen" dafür definieren dann muss man das nicht x mal wiederholen
Ich verstehe es so, dass jede Formel (es gibt ja nicht nur die Eine, die man ganz oben sieht) dann zunächst im Tabellenblatt "Tage" die Zelle A1 auslesen würde und die jeweilige Formel dann durch diesen Wert ergänzt. Ist das richtig so?
Indirekt() baut einen Zellbezug aus einem String zusammen, welcher sich hier aus statischen Bestandteilen und dem Text aus der Zelle zusammensetzt, löst diesen dann auf und gibt die Daten des Bezugs zurück.
Die Namen der jeweiligen Datei 1 müßten dann so aussehen:
"Eintrag_Kartei_AW_2023.xlsm" (...halt das entspr. Jahr hinter dem Unterstrich).
Richtig?
Ja.

Würde aber ehrlich gesagt gleich eine Datenbank favorisieren als so ein Gebastel aus voneinander abhängigen Mappen zu bauen die versagen sobald die verknüpften Mappen nicht mehr an Ort und Stelle liegen. -JustMy2Cent-
Member: BN2023
BN2023 Nov 03, 2023 updated at 12:00:45 (UTC)
Goto Top
@8030021182:

das mit dem "Namen" habe ich noch nicht so ganz verstanden.
Im Moment habe ich 3 unterschiedliche Formeln, die jedoch alle mit "INDIREKT", bzw. "_" & Tage!$A$1 &" ergänzt werden müßten. Diese 3 Formeln müssen für verschiedene Kategorien nach unten erweitert werden, was ja per runterziehen geht. Allerdings in jeder der 12 Kategoriern müßte ich diesen Vorgang (Formel einsetzen und runterziehen) wieder erneut durchführen.

Ja, das wäre eine Menge Arbeit, aber ja nur 1 x zu tun.
Für die Tabelle des neuen Jahres, müßte ich ja nur die Jahreszahl in A1 anpassen.

Was würde ich also durch Vergabe eines Namens an Arbeit einsparen?
Und - wie müßte ich dazu vorgehen?

Grüße,
BN
Mitglied: 8030021182
8030021182 Nov 03, 2023 updated at 12:03:02 (UTC)
Goto Top
Member: Blackmann
Blackmann Nov 06, 2023 updated at 06:56:33 (UTC)
Goto Top
Moin @BN2023,

- trage in A1 Dein Jahr ein, 2023 oder 2024
- Frage in einer Wenn-Funktion nach dem Inhalt von A1 und entscheide, woher Du die Daten haben willst, beachte dabei, dass eine Wenn-Funktion genau EINEN Wert/Jahr vergleichen kann
- Dieses Beispiel ist eine geschachtelte Wenn-Funktion, die auf beide Jahre abfragt und einen Fehlertext ausgibt, wenn die Eingabe nicht zu den Jahren passt.

=WENN($A1=2023;'A:\Dateien\Personal\Eintraege\AW\[Eintrag_Kartei_AW_2023.xlsm]Eingaben'!D9;WENN(A1=2024;'A:\Dateien\Personal\Eintraege\AW\[Eintrag_Kartei_AW_2024.xlsm]Eingaben'!D9;"Ist Falsch"))  

Dieses Konstrukt musst Du überall dort einsetzen, wo Du Daten aus den anderen Dateien holst.

Diese Lösung lehnt sich sehr an Dein Ausgangspost an und sollte Dir verständlich sein, denn blindes Copy und Paste ist hier fehl am Platze.

Schöne AW BM
Member: BN2023
BN2023 Nov 06, 2023 at 08:17:44 (UTC)
Goto Top
Danke Dir @Blackmann für Deine detaillierte Erläuterung.

In dieser Formel hast Du ja beispielhaft die Jahre 2023 und 2024 eingesetzt (über die Zelle A1).
Das würde aber dann bedeuten, dass ich für das Jahr 2025 all diese Formeln wieder abändern müßte auf 2024 und 2025.

Gibt es einen Weg, in der Formel einfach nur die Ausgabe der Zelle A1 anzusprechen, ohne in der Formel angeben zu müssen, was genau in A1 steht? Denn so müßte ich all die Formeln nicht jedes Jahr anpassen.

Viele Grüße,
BN
Mitglied: 8030021182
8030021182 Nov 06, 2023 updated at 08:24:02 (UTC)
Goto Top
Ähm genau das habe ich doch schon oben gepostet ...?!🧐 Simple String-Verkettung...
Wie oft denn jetzt noch?
Member: Blackmann
Blackmann Nov 06, 2023 at 10:42:59 (UTC)
Goto Top
Zitat von @BN2023:

dass ich für das Jahr 2025 all diese Formeln wieder abändern müßte auf 2024 und 2025.


Sorry, das war nicht Thema Deiner Fragestellung oben!

Zitat von @8030021182:

Würde aber ehrlich gesagt gleich eine Datenbank favorisieren als so ein Gebastel aus voneinander abhängigen Mappen zu bauen die versagen sobald die verknüpften Mappen nicht mehr an Ort und Stelle liegen. -JustMy2Cent-


Dem schließe ich mich an.

BG BM
Member: BN2023
BN2023 Nov 06, 2023 updated at 12:45:57 (UTC)
Goto Top
Das mit den Formeln hat jetzt funktioniert.
Offenbar müssen die Entsprechenden Ziel-Dateien während dessen geöffnet sein... das war mir nicht bewußt.

Eine Frage jedoch noch:
Wie übernehme ich mit der obigen Methode einfach nur einen Wert in eine Zelle.
Mit der folgenden Formel funktioniert es nicht, den Wert aus der Zelle "P9" der Datei "Eintrag_Kartei_AW_2023.xlsm" zu übernehmen:

=INDIREKT('"A:\Dateien\Personal\Eintraege\AW\[Eintrag_Kartei_AW_"&Tage!$A$1&".xlsm]Eingaben'!P9")  

Ich habe schon mehrere Konstellationen ausprobiert, aber finde den Fehler nicht.
Könnt Ihr bitte nochmal helfen, die Formel zu korrigieren?

Danke und Grüße,
BN
Member: BN2023
BN2023 Nov 06, 2023 updated at 14:50:30 (UTC)
Goto Top
...hat jetzt funktioniert.
Das Problem war einfach, dass das normale Anführungsstrichchen direkt vor dem kleinen kommen muss.

Aber dafür gibt´s jetzt ein anderes Problem.
Sobald ich die Datei "Eintrag_Kartei_AW_2023.xlsm" schließe und die Datei für den nächsten MA öffne, erhalte ich nun wieder die Ausgabe #BEZUG!.

Das war vorher nicht so, als ich noch nicht per "INDIREKT" und dem Bezug auf das entspr. Jahr über die Verknüpfung gearbeitet habe.

Seltsamer Weise ist jetzt auch im Menü unter "Daten" der Eintrag "Verknüpfungen bearbeiten" ausgegraut. Offenbar sind also keine Verknüpfungen mehr hinterlegt. In der Ursprungsdatei (also ohne die "INDIREKT"-Funktion" waren dort alle Verknüpfungen hinterlegt. Und die Zieldateien mußten natürlich auch nicht geöffnet sein, nachdem die Formeln eingetragen waren.

Hat jemand hierzu eine Idee?

Grüße,
BN
Member: Tomtom33
Tomtom33 Nov 06, 2023, updated at Nov 08, 2023 at 10:28:24 (UTC)
Goto Top
Wenn ich es richtig verstanden habe, liegen die Dateien der einzelnen Mitarbeiter in unterschiedlichen Ordnern und haben im Namen das Kürzel des Mitarbeiters.
Wenn Du aber nun einen Indirekt-Bezug herstellst und den nicht jeweils anpasst (das AW und den Ordner dazu), dann ist der damit erzeugte Pfad ja nicht gültig, bzw. die Zugriffsberechtigung ist u.U. (anderer MA und keine Leseberechtigung) nicht gegeben ;)

Prinzipiell müsstest Du also die Indirekt-Bezüge für jeden MA anpassen, damit das funktioniert.
Beim Namen der Datei wäre es einfach, denn da brauchst Du ja nur das Kürzel rausnehmen, beim Pfad wird es etwas schwieriger, aber dass wird hier erklärt, wie man den Pfad in einer Formel ermitteln kann:
Pfad in Formel ermitteln

Genaugenommen könntest Du damit sogar das Kürzel im Namen ableiten, indem Du ihn aus dem Pfad extrahierst, wie es bereits für den Pfad selbst erfolgt, indem Du quasi eine Ebene höher gehst und den Ordnernamen verwendest.

Aber ich schließe mich da durchaus den Anderen an, eine Datenbanklösung via bspw. Access, die Du mit deinen Excel-Dateien verknüpfst, wäre sicherlich sinnvoller.
Member: Tomtom33
Tomtom33 Nov 07, 2023, updated at Nov 08, 2023 at 10:32:55 (UTC)
Goto Top
Um das nochmal in Einzelschritten zu erklären, was ich meine (schreibe die am Besten erstmal in einzelne Zellen eines leeren Blatts, um die Funktionsweise kennenzulernen):

1 nur den Pfad ermitteln (den Namen der Datei setzt Du ja später eh zusammen):
=LINKS(ZELLE("filename";B2);FINDEN("[";ZELLE("filename";B2))-1)  
Der Wert -1 am Ende bestimmt, um wie viele Zeichen der Pfad gekürzt werden soll (-1 bedeutet Länge -1).
Der Wert B2, bzw. G8 (in 2.) ist beliebig und nur der Bezug zu irgendeiner Zelle im Arbeitsblatt, also völlig egal, solange Du keine Referenz auf eine andere Arbeitsmappe nimmst, denn dann wäre das Ergebnis evtl. ein anderes.

2. nun den Namen des Ordners für den MA (dafür muss der Wert -1 auf -2 vom vorherigen Befehl angepasst werden und in ein Rechts() mit positiver Ganzzahl gefasst werden, um den Namen des Ordners zu erhalten):
=RECHTS(LINKS(ZELLE("filename";G8);FINDEN("[";ZELLE("filename";G8))-2);2)  
Der letzte Wert 2 bestimmt dabei, wie lang der Wert ist (in deinem Beispiel 2 Zeichen), allerdings sollten alle Ordner für die Mitarbeiter die selbe Länge des Namens haben, also bspw. nur die Initialen.

3. Als nächstes setzen wir den Dateinamen zusammen (hier mit Verketten(), der Wert in Zelle G9 ist der ermittelte Name des Ordners, G10 ist der Wert für das Jahr)
=VERKETTEN("Eintrag_Kartei_"; G9; "_"; G10; ".xlsm")  
So bekommst Du dann den formatierten, richtigen Namen

4. nun setzt Du den Pfad inkl. des Dateinamens wieder zusammen (dabei benutzt Du den als erstes ermittelten Wert , da der Pfad ja bereits richtig ist und setzt diesen dann mit dem Namen zusammen)
=G8&G11
hier benutze ich zum zusammensetzen das kaufmännische Und (&), anstatt verketten(), das nennt man übrigens auch konkatenieren. G8 ist der Dateipfad und G11 der zusammengesetzte Dateiname.

Das Ergebnis sieht dann so aus:
C:\Users\Tomtom\Desktop\Eintrag_Kartei_op_2023.xlsm
das op stammt natürlich aus dem Ordnernamen Desktop 😉

Auf diese Weise erreichst Du, dass sich die Ordner und Dateinamen immer auf die geöffnete Datei beziehen, egal um welchen MA es sich handelt.

Denke daran, dass es am sichersten ist, wenn Du den Dateipfad inkl. Namen am Ende in einfache Anführungszeichen ' packst, sonst würde bei einem Leerzeichen ggf. im Pfad ein Fehler verursacht werden (siehe nächsten Kommentar).

Nun musst Du dir nur noch Gedanken machen, wie Du das so verschachtelst, dass es in eine Zelle mit Indirekt() passt, oder Du arbeitest mit ausgeblendeten Zellen und nutzt die letzte Stufe des Ergebnisses mit Indirekt().

Was mir übrigens aufgefallen ist, ist dass Du offensichtlich eh Makros verwendest (sieht man an der Dateiendung *.xlsm), das Ganze könntest Du auch über ein Makro erledigen, ohne die Bezüge, aber gut, das muss nicht verstehen.
Member: Tomtom33
Tomtom33 Nov 08, 2023 updated at 10:52:07 (UTC)
Goto Top
Zum Abschluss noch ein Beispiel, wie man (theoretisch) auch mit einem anderen Pfad arbeiten könnte, um vlt. auf eine andere Datei, in einem anderen Ordner zugreifen zu können.
Das Problem bei der Sache ist, es funktioniert nur, wenn beide Dateien in Excel geöffnet sind, was man aber dadurch erreichen könnte, dass man beim Start der Hauptdatei diese ebenfalls laden lässt (bspw. als Addin ohne sichtbare Tabellenblätter), welcher wieder Daten beinhalten könnten, die für den Nutzer nicht sichtbar sein sollen.
Auch darf die 2. Datei nicht den gleichen Namen haben, sonst kann diese von Excel nicht geöffnet werden.
Wie das funktioniert kann man HIER nachlesen.
Es gibt sicherlich auch noch andere Methoden, nur diese erfüllt ja den eigentlichen Zweck.

Das wäre dann bspw. so zu realisieren (ohne jetzt auf die Erstellung eines Excel-Addin eingehen zu wollen), indem man Schritt 1 und optional Schritt 2 (immer jeweils beide Werte ist wichtig!) auf die geöffnete 2. Datei referenziert.

Schritt 1:
=LINKS(ZELLE("filename";'C:\Users\Tomtom\Desktop\Neuer Ordner\[dateipfad-einfuegen_bak.xlsx]Inhalt'!$D$7);FINDEN("[";ZELLE("filename";'C:\Users\Tomtom\Desktop\Neuer Ordner\[dateipfad-einfuegen_bak.xlsx]Inhalt'!$D$7))-1)  

Schritt 2 (falls erforderlich anpassen, sonst so lassen wie er im Ursprung war, wenn die Struktur identisch im anderen Ordner ist):
=RECHTS(LINKS(ZELLE("filename";'[dateipfad-einfuegen_bak.xlsx]Inhalt'!$D$7);FINDEN("[";ZELLE("filename";'[dateipfad-einfuegen_bak.xlsx]Inhalt'!$D$7))-2);2)  

Ist die Datei nicht geöffnet, erscheint '#NV' als Fehler in der Zelle, was aussagt, dass der Referenzwert nicht gefunden wurde, andernfalls wird der Pfad dorthin entsprechend ausgegeben.

In meinem Beispiel würde das Ergebnis (Schritt 2 ist angepasst und die andere Datei ist geöffnet) dann so aussehen:
C:\Users\Tomtom\Desktop\Neuer Ordner\Eintrag_Kartei_er_2023.xlsm

Wie man hier ersehen kann, stammt das "er" im Namen aus "Neuer Ordner" und der Pfad ist der neue Pfad.

Prinzipiell ließe sich so einiges rein Formelbasiert realisieren, aber es ist deutlich umständlicher wie mit Makros (also VBS) zu arbeiten.
Nachteil bei Makros sind die nervigen Hinweise von Excel, die zwar der Sicherheit dienen und erst signiert ohne Schutz laufen, dafür aber auch fast alle Möglichkeiten einer echten Programmiersprache bieten, denn Makros sind ja nicht nur einfach "Aufzeichnungen" bestimmter Abläufe wie viele Laien vermuten, man kann da wirklich richtig mit Programmieren (VBA = Visual Basic Script).

Ich habe vor Jahren (2006) für einen großen Konzern etwas programmiert, mit dem Versandpläne in Excel mit tausenden unterschiedlichen Einträgen, unterschiedlichen Sorten und Zeitfenstern für die Produktion so ausgewertet wurden, dass daraus ein Fahrplan für den Umbau der Maschinen erstellt (nach einer Vorlage, für die MA etwa 2 Stunden benötigten!) und in den Plan als neues Blatt eingefügt wurde. Das nicht etwa statisch mit fixen Werten, nein völlig flexibel und egal um welches Produkt es sich gehandelt hatte.
Bei der Umsetzung habe ich eine Excel-Datei mit einem Tabellenblatt erstellt, darin war die grobe Formatierung, also quasi ein leeres Blatt mit der Grund Formatierung, damit es später den selben Stil hat, wie der ursprüngliche Versandplan. Am Anfang wird eine Datei-Auswahl geöffnet und man wählt den Versandplan aus, der dann in Excel geladen wird (und geprüft).
Die eigentliche Auswertung und das einfügen in den Fahrplan (es wird immer umgeschaltet) hat dann damals teilweise ein paar Minuten gedauert, da Excel ja bei jeder Änderung einer Zelle aktualisiert, was man zwar Ein- und Ausschalten könnte, aber nicht den gewünschten Erfolg brachte.
Nun hatte ich mir überlegt ich kann ja auch einfach die Benutzerschnittstelle für die Berechnung abschalten uns siehe da, es dauerte nur noch etwa 1 Sekunde (diese Datei funktioniert auch heute noch unter O2021 einwandfrei).
Naja zum Schluss habe ich das Ganze als Addin definiert, also ohne dass Excel die anzeigt (nur die Dateiauswahl) und zum Schluss das neue Tabellenblatt in den Versandplan eingefügt, wobei natürlich die Änderungen im Programm verworfen wurden.

Wie man daran unschwer ersehen kann, ist über Makros so einiges möglich.

Eine Sprachreferenz bietet u.A. Microsoft: Sprachreferenz Visual Basic

Ansonsten gibt es einiges an Fachliteratur und einige Seiten, die da sehr hilfreich sind, wie etwa diese:
W3schools VBScipt Functions
Member: BN2023
BN2023 Nov 09, 2023 at 08:17:22 (UTC)
Goto Top
WOW... vielen Dank für die detaillierte Beschreibung @Tomtom33

Ich werde etwas Zeit brauchen, um mich damit zu beschäftigen.
Aber auch Du hast mir ja vorgeschlagen, all das besser über eine Datenbank zu realisieren (ggf. Access).
Nur kenne ich mich mit Access überhaupt nicht aus.

Von daher müßte ich mal recherchieren, wie man sowas macht und dann mal einen Versuch starten, ob das Alles damit besser umsetzbar ist und vielleicht auch unkomplizierter und weniger fehleranfällig.

Grüße,
BN
Member: Tomtom33
Tomtom33 Nov 11, 2023 updated at 10:01:51 (UTC)
Goto Top
Zitat von @BN2023:

WOW... vielen Dank für die detaillierte Beschreibung @Tomtom33

Ich werde etwas Zeit brauchen, um mich damit zu beschäftigen.
Aber auch Du hast mir ja vorgeschlagen, all das besser über eine Datenbank zu realisieren (ggf. Access).
Nur kenne ich mich mit Access überhaupt nicht aus.

Von daher müßte ich mal recherchieren, wie man sowas macht und dann mal einen Versuch starten, ob das Alles damit besser umsetzbar ist und vielleicht auch unkomplizierter und weniger fehleranfällig.

Grüße,
BN

Es freut mich, dass ich dir in diesem Punkt weiterhelfen konnte 😎

Ob sich der Einsatz von einer Datenbank lohnt, hängt sehr stark von der Komplexität der Daten ab und zu welchem Zweck diese erhoben werden (also bspw. ob die Daten ausgewertet werden), was mir anhand deiner Schilderungen nicht bekannt ist, also ob es sich um Dateien zur Urlaubs-, zur Arbeitszeiterfassung, oder was auch immer handelt.

Access lässt sich relativ einfach mit Excel verknüpfen, bedingt aber auch ein gewisses KnowHow über Datenbanken allgemein, sowie Kenntnisse der Rechtevergabe (die Datenbank muss ja für Excel des MA zugänglich sein).
Wie bei allen Datenbanken sollte man die Möglichkeit eines direkten Zugriffs jedoch strikt unterbinden, damit der MA die Daten weder einsehen, noch verändern kann (Datenschutz), oder anders ausgedrückt, der MA soll ja keinen Zugriff auf das Netzlaufwerk, bzw. das Laufwerk deines Kontos erhalten, sondern lediglich soll Excel die Daten daraus beziehen können.
Access speichert diese Datenbank in einer Datei (*.mdb, oder *.accdb) ab, wird diese Datei zentral verwaltet (was ggf. Sinn ergibt), muss diese auch für Access zugänglich sein, also der Dateipfad (bspw. ein eigenes Netzlaufwerk) für den MA vorhanden sein.

Wie Du siehst ist einiges dabei zu beachten, aber es würde natürlich auch funktionieren, wenn die Datenbank für jeden einzelnen MA separat vorhanden wäre, was dann jedoch kaum Vorteile hätte, wenn es darum geht die Daten auszuwerten.

Makros anstatt Verknüpfungen zu verwenden, macht da aber auf jeden Fall Sinn, zumal man dadurch deutlich mehr Möglichkeiten bietet, wenn man sich dort erst einmal eingearbeitet hat und so bspw. auch direkt auf eine Datenbank zugreifen, welche dann später für eine Auswertung genutzt werden könnte.

Es ist halt Abwägungssache ob sich der Einsatz einer Datenbank in deinem Fall lohnt und auch wenn es für Excel/Access einige gute Assistenten gibt, kommt man um ein gewisses KnowHow kaum herum, was ich versucht habe dir zu verdeutlichen.

Dazu mein Tipp: versuche erstmal dir die Makro-Programmierung näher zu bringen (siehe die Links aus dem vorhergehenden Beitrag von mir).
Später (falls Du eine Datenbank für sinnvoll hältst) würde ein Code dafür bspw. so aussehen können (habe ich mal eben aus einem anderen Forum kopiert, ist nur leider nicht vernünftig dokumentiert):

Sub DatenübernahmeNachExcel()
' Bernd Held, Excel VBA in 21 Tagen  
' unbedingt Verweis auf ActiveX Data Objects setzen  
Dim ADOC As New ADODB.Connection
Dim DBS As New ADODB.Recordset
Dim cmd As ADODB.Command
On Error GoTo Fehlerbehandlung
ADOC.Open "Provider=Microsoft.Jet.oledb.4.0;" & _  
"data source=C:\Daten\Buchungen.mdb;"  
DBS.Open "Veranstaltung", ADOC, adOpenKeyset, _  
 adLockOptimistic
 Set cmd = New ADODB.Command
 cmd.CommandText = "Select * from Veranstaltung"  
 cmd.ActiveConnection = ADOC
 Set DBS = cmd.Execute
 Sheets("Import").Activate  
 Range("A2").Select  
Do While Not DBS.EOF
 ActiveCell.Value = DBS!Bdatum
 ActiveCell.Offset(0, 1).Value = DBS!Vdatum
 ActiveCell.Offset(0, 2).Value = DBS!Veranstaltung
 ActiveCell.Offset(0, 3).Value = DBS!V_Ort
 ActiveCell.Offset(0, 4).Value = DBS!Teilnehmer
 ActiveCell.Offset(0, 5).Value = DBS!Straße
 ActiveCell.Offset(0, 6).Value = DBS!PLZ
 ActiveCell.Offset(0, 7).Value = DBS!Ort
 ActiveCell.Offset(0, 8).Value = DBS!Kosten
 If DBS!Bezahlt = True Then
 ActiveCell.Offset(0, 9).Value = "ja"  
 Else
 ActiveCell.Offset(0, 9).Value = "Nein"  
 End If
 DBS.MoveNext
 ActiveCell.Offset(1, 0).Select
Loop
Columns("A:J").AutoFit  
DBS.Close
ADOC.Close
Set DBS = Nothing
Set ADOC = Nothing
Set cmd = Nothing
Exit Sub
Fehlerbehandlung:
MsgBox "Es ist ein Fehler aufgetreten!" _  
& Chr(13) & Err.Description
DBS.Close
ADOC.Close
Set ADOC = Nothing
Set DBS = Nothing
End Sub
Quelle: www.ms-office-forum.net/forum/showthread.php?t=128958

Dazu kannst Du dann ja einfach mal nach den einzelnen Bestandteilen des Codes recherchieren, um zu verstehen was der Code bewirkt und wie man Daten aus der Datenbank in Excel einfügt (in dem Beispiel wird aus der Datenbank Buchungen.mdb, der Inhalt von Veranstaltungen ausgewertet und die einzelnen Elemente daraus in Excel-Zellen geschrieben und beim Wert Bezahlt durch if angepasst und das Ganze für alle vorhandenen Datensätze - grob umrissen).

Ich hoffe das hilft dir bei deinen Überlegungen, wie Du deine Erfassung am sinnvollsten verbessern könntest.