Clever-Excel-Forum - Excel: Bereich verschieben / zweistufige Auswahl

Einen schönen guten Morgen,

ich kämpfe mit einem Problem der Mitarbeiterauswahl nach dem eine Abteilung ausgewählt worden ist. Siehe Anhang [attachment=11949]
(Biete mir nur die Liste der Mitarbeiter der jeweiligen Abteilung an).

Der Versuch erfolgte über den "Bereich.Verschieben". Das funktioniert solange die Daten in einem Tabellenblatt sind.
Der Übersicht halber ist der Wunsch einer Trennung in Arbeitsplan (Kalender) und Arbeitsdaten (Abteilung & Mitarbeiter).

Beim Umzug und Verknüpfung der Daten wird folgende Meldung präsent: "Dieser bezugstyp kann in der datenüberprüfung-formel nicht verwendet werden".

Forsch gedachtes Ziel war:
- aus Arbeitsblatt "Mitarbeiter" (mit Inhalten Abteilung & Mitarbeiter je Abteilung) aus das Blatt "Arbeitsplan" zu verknüpfen
- dabei sollte in der C Spalte (Arbeitsblatt "Arbeitsplan") zuerst die Abteilung ausgewählt werden
- in Spalte D sind dann nur noch die Mitarbeiter der in Spalte C ausgewählten Abteilung wählbar

Via: Datenüberprüfung.

In C liegt:

Code:
=BEREICH.VERSCHIEBEN(Mitarbeiter!D:L;1;VERGLEICH(C4;D1:L1;0)-1;ANZAHL2(INDEX(Mitarbeiter!D:L;;VERGLEICH(Mitarbeiter!C4;D1:L1;0)))-1;1)

In D sollte:

Code:
=BEREICH.VERSCHIEBEN(Mitarbeiter!§D§L;1;VERGLEICH(Arbeitsplan!C4;D3:L3;0)-1;ANZAHL2(INDEX(Mitarbeiter!D:L;;VERGLEICH(Arbeitsplan!C4;D3:L3;0)))-1;1)

So war der Plan. Leider gescheitert.

- Anregungen?
- Meinungen?
- Funktioniert es auf die gedachte Weise überhaupt?

Vielen Dank.

Alternativen sind sehr gern willkommen.

Hallo,

wenn ich dich richtig verstanden habe, benötigst Du abhängige Auswahllisten der Datenüberprüfung.

Schau mal, ob das so passt

(27.06.2017, 07:46)Jonas0806 schrieb: [ -> ]Hallo,

wenn ich dich richtig verstanden habe, benötigst Du abhängige Auswahllisten der Datenüberprüfung.

Schau mal, ob das so passt

Hallo Jonas,
wow - es funktioniert.

Du hast es ebenso über die Datenüberprüfung umgesetzt.
In den Einstellungen zu der Spalte C habe ich gesehen, dass du dort "Quelle: =Abteilungen" ausgewählt hast. Das entspricht dem benannten Bereich, die Benennung der Quelle erfolgte über eine Formel?

Mich verwundert ein wenig, warum da nicht der Bereich "=Mitarbeiter!$D$3:$L$3" steht?
Es wirkt als hast du den Bereich "Mitarbeiter!$E$3:$L$3" als "Abteilungen" definiert, falls ja: wie? :)

Für die Spalte D: die "=INDIREKT" ist automatisch auf den Bereich der Spalte C referenziert oder sind dafür (im Hintergrund) ebenso Modifikationen oder Definitionen nötig?

Weitere Ausführungen zu deiner sehr erfolgreichen Lösung sind sehr willkommen.
Ich fürchte die Problemstellung verfolgt mich auch künftig.

Vielen Dank.

Hallo,

Hier nochmal eine andere Lösung, ohne aufwändig Namen für die einzelnen Spalten anzulegen.

Du hattest den Namen test zweimal vergeben, einmal auf Blattebene, einmal auf Arbeitsmappenebene. Ich habe mal den ersten gelöscht und bei der Datenüberprüfung in C4 =test als Formel hinterlegt.

Ich habe dann einen Namen für den Datenbereich der Mitarbeiter (Test2) und einen Namen MA erstellt, der die Spalte in der Mitarbeiter-Tabelle ermittelt und diesen bei D4 hinterlegt.

(27.06.2017, 08:05)Der Steuerfuzzi schrieb: [ -> ]Hallo,

Hier nochmal eine andere Lösung, ohne aufwändig Namen für die einzelnen Spalten anzulegen.

Du hattest den Namen test zweimal vergeben, einmal auf Blattebene, einmal auf Arbeitsmappenebene. Ich habe mal den ersten gelöscht und bei der Datenüberprüfung in C4 =test als Formel hinterlegt.

Ich habe dann einen Namen für den Datenbereich der Mitarbeiter (Test2) und einen Namen MA erstellt, der die Spalte in der Mitarbeiter-Tabelle ermittelt und diesen bei D4 hinterlegt.

Hallo Steuerfuzzi,

vielen Dank für deine Nachricht. Es funktioniert.

Leider verstehe ich noch nicht wie du es gemacht hast.
Vor allem die (ich nenne es einmal "Bereichsdefinition" zu "=test").
Kannst du mir die Funktionsweise bitte näher bringen.
Ich sehe in der oberen Funktionsleiste die definierten Bereiche/Gruppen, bekomme die Definition selbst aber nicht hin. :/
Der Namens-Manager unter "Formeln"?

Vielen Dank in jedem Fall.

(27.06.2017, 08:32)newiE schrieb: [ -> ]Der Namens-Manager unter "Formeln"?

Ja, genau den. Anbei die Vorgehensweise in Bildern. Der Name test existierte ja bereits und umfasste den Bereich mit den Überschriften. Den Namen test2 habe ich eingefügt, er umfasst den Datenbereich (ohne Überschriften). Im Namen MA mache ich im Prinzip das gleiche wie Du mit BEREICH.VERSCHIEBEN allerdings mit INDEX und VERGLEICH. Die Formel ist deshalb so lang, da ich gleichzeitig auch die Auswahl auf die tatsächliche Länge der Liste beschränke.

Man könnte das ganze auch mit einer intelligenten Tabelle machen, dann kann man sich das Anlegen der Namen sparen und die Bereiche passen sich automatisch an die tatsächliche Größe des Bereichs an. Würde ich in diesem Fall sogar empfehlen.

Vielen dank für die weiteren Erklärungen und Ausführungen.

Zur intelligenten Tabelle versuche ich mich an youtube, komme bei Problemen sicher auf euch zurück! Blush

Eine schöne Woche,
und bis bald.