Excel externe Bezüge und Formel in Zeile, Werte in Spalte

[janjan]

Hallo,
ich bin versuche die Urlaubsplanung in unseren Verein zu optimieren.

Szenario;
Mitglieder tragen ihre Urlaubsplanung in eine Tabelle ein;

in einer eigenen Datei "NAME.xlsx" wird in der Zeile C, Feld 6-36 das Schlagwort "Urlaub" eingetragen.

Wir verwenden die Datei "Urlaubsplaner 2022.xlsx" (https://sven-brunn.de/tag/urlaubsplaner/) welche kostenfrei im Netz zur Verfügung steht, in dieser sind die Monatstage nicht spalten- sondern zeilenweise eingetragen.

Nun soll, wenn bspw. in C6 das Schlagwort "Urlaub" in der Mitgliedsdatei gefunden wird, in der Datei "Urlaubsplaner 2022.xls" in T36 ein "G" angezeigt werden.

Aktuell habe ich das so gelöst;
In T36
=WENN('\\Netzwerkfreigabe\[NAME.xlsx]Januar'!$C$6 = "Urlaub";"G";"").

Das funktioniert leider nur mit einschränkungen;
1. Erhalte ich beim öffnen der Detei "Urlaubsplaner 2022.xlsx" eine Meldung das externe Bezüge icht aktualisiert werden konnten (da die Datei NAME.xlsx nicht geöffnet ist)
2. Funktioniert das übertragen oder "aufziehen nach rechts" der Formel so nicht, so das in "Urlaubsplaner 2022.xlsx" stehts
=WENN('\\Netzwerkfreigabe\[NAME.xlsx]Januar'!$C$6 = "Urlaub";"G";"")

übertragen wird und nicht wie benötigt

=WENN('\\Netzwerkfreigabe\[NAME.xlsx]Januar'!$C$7 = "Urlaub";"G";"")
=WENN('\\Netzwerkfreigabe\[NAME.xlsx]Januar'!$C$8 = "Urlaub";"G";"") usw.

Ich bräuchte also einen Tipp wie
1. Externe Zellbezüge aktualisiert werden können, wenn die Quelldatei nicht geöffnet ist.
2. Wie kann ich die Formel entsprechend anpassen das diese nach rechts aufgezogen werden kann, die Zellbezüge aber vertikal angepasst werden.

Thx
Jan

[Oldiefan]

Hey Jan!

Mit externen Dateien kenn ich mich nicht so gut aus, aber zur zweiten Frage gibt es von mir den Tipp:

=WENN('\\Netzwerkfreigabe\[NAME.xlsx]Januar'!C$6 = "Urlaub";"G";"").

Du kannst Bezüge komplett absolut setzen ($C$6), aber auch nur die Spalte ($C6) oder die Zeile (C$6). Für dich wird wohl die Spalte das wichtige sein.

Liebe Grüße
alex

[janjan]

Hallo Alex,

danke für Deine Antwort!

Relative uns absolute Zellbezüge kenne ich aber wenn die formel aus T36
"=WENN('\\Netzwerkfreigabe\[NAME.xlsx]Januar'!$C$6 = "Urlaub";"G";"")
in
=WENN('\\Netzwerkfreigabe\[NAME.xlsx]Januar'!$C6 = "Urlaub";"G";"")
ändere, wird beim "aufziehen" nach rechts die Zeile nicht angepasst (weil die formel ja nicht nach unten "aufgezogen" wird - das würde so funktionieren).

Generell hast Du aber recht, $C6 ist richtiger und für den folgenden Schritt nötig.

Viele Grüße!
Jan

[Oldiefan]

Hoi Jan!

Ich bins nochmal. Hast du es schon mal mit Index versucht? Die Syntax lautet: =Index(Matrix;Zeile;Spalte). Wenn du nun den gegebenen Bereich absolut setzt, kannst du mit den Funktionen Zeile() und Spalte() deine Werte auslesen. 

Liebe Grüße
Alex

[janjan]

Hi Alex :)

Index kenne ich noch nicht, bin aber bei meinen Recherchen darauf gestossen das diese Funktion externe Bezüge (aus einer anderen Datei) nur aktualisiert wenn die Datei ebenfall in Excel geöffnet ist.

Ich hatte das kurz versucht aber bin icht so recht weiter gekommen.
Ich glaube ich muss mir erstmal eine kleinere Tabelle zum testen bauen.
Was "Matrix" angeht, stehe ich gerade auf dem Schlauch.

LG
Jan

[Oldiefan]

Hoi Jan!

Ja, das stimmt, auch die Index-Funktion kann nur mit geöffneten externen Dateien umgehen. Das ist - meines Wissens - bei allen Funktionen so. Ich hatte nur daran gedacht, weil es mit Index leichter ist, Matrizen (Plural von Matrix) zu durchsuchen.

Die Matrix ist grundsätzlich immer ein Bereich, also ein Teil eines Blattes, der X * Y Zellen groß ist. Beispiel: =Index(Tabelle1!$A$1:$B$10;Zeile();Spalte()) kopiert den Bereich in Tabelle 2. Du kannst aber auch Bezüge wie =Index(Tabelle1!$A$1:$B$10;Zeile()-2;Spalte()-4) nehmen, wenn du in anderen Zellen starten möchtest. Selbstverständlich funktionieren auch Namen und Bezüge auf Zellen, die Zahlen beinhalten:  =Index(Namen;C1;Spalte()) würde dir aus der Namensliste den Eintrag mit der Nummer aus C1 auslesen.

Liebe Grüße
Alex

[schauan]

Hallöchen,

ein einfacher Bezug wie in Deinen ursprünglichen WENN sollte auch bei geschlossener Datei funktionieren
Für das Formelziehen gib die Formel z.B. als #WENN ein, ziehe, und ersetze dann # durch =

[janjan]

Hallo :)

nun habe ich ein Phänomen erlebt, zwischenzeitlich ging es in dem ich in der Datei Urlaubplanung.xls die Zeilen, in werlcher die Formeln stehen sollen (aktuell T38 bis NT39), ausgewählt habe und bei Eingabe der WENN Formel die vertikalen Zellen ausgewählt hatte.

So wurde z.B. aus:

Code:

=WENN('X:\Zeiterfassung\Sample-User\[Arbeitszeitverwaltung_2022_sample.xlsx]Januar'!$C$6="urlaub";"G";"")

Code:

=WENN('X:\Zeiterfassung\Sample-User\[Arbeitszeitverwaltung_2022_sample.xlsx]Januar'!$C$7="urlaub";"G";"")

=WENN('X:\Zeiterfassung\Sample-User\[Arbeitszeitverwaltung_2022_sample.xlsx]Januar'!$C$8="urlaub";"G";"")

usw.
Aus irgendeinem Grund klappt das nun nicht mehr :(

Externe Bezüge funktionieren prima mit WENN Formeln!
Das = durch ein # zu ersetzen hat leider keine Änderung gebracht, hier wird der Wert C6 nicht auf C7, C8 usw. erhöht.

Die Datei Urlaubsplanung hat 365 horizontale Zeillen (für jeden Tag im Jahr), das alles händisch anzupassen wäre ein Alptraum und stundenlange, langweillige Arbeit :(
Es wird sicher eine Möglichkeit geben WENN Formeln horizontal zu erweitern (nach Rechts ziehen) so das die Zeillen angepasst werden aber trotz etlicher Stunden Websuche finde ich nicht das richtige
- meist wird auf die INDIREKT Funktion verwiesen aber da externe Bezüge hier nur bei geöffneten Dateien aktualisiert werden, muss ich etwas anderes finden.

Viele Grüße!
Jan

[schauan]

Hallöchen,

Das = durch ein # zu ersetzen hat leider keine Änderung gebracht, hier wird der Wert C6 nicht auf C7, C8 usw. erhöht.

im Prinzip

="#WENN(...C" & zeile() & "..."
anschließend kopieren und Werte einfügen
anschließend ersetzen ...

[janjan]

Hallo Schauan,

danke für den Tipp!
Leider habe ich Schwierigkeiten mit der richtigen Formatierung und bekomme stehts eine Meldung "Sie wollen keine Formel eingeben?" ...
Nach etlichen Stunden Webrecherche habe ich nun mittels Copy&Paste und Search&Replace in "nur" 2 Stunden ebenfalls das gewünschte erreicht.

Gute Nacht :)