14
Excel: dynamische Zelladressierung
Hallo,
ich möchte in einem Excel Arbeitsmappe im ersten Blatt eine Tabelle folgender Form erstellen
Blatt 1:
1 Peter Müller 89
2 Lara Lustig 90
Dann soll erstelle ich zu jeder Zeile aus der Tabelle ein weiteres Blatt in der Excel-Mappe.
Jetzt möchte ich in jedem weiteren Blatt die Werte aus immer einer Zeile holen.
Also in Blatt Peter die Werte aus Zeile 1 und in Blatt Lara die Werte aus Zeile 2.
Kann ich irgendwo auf den Blättern Peter oder Lara eine Zelle erstellen in die ich die gewünschte Zeilennummer aus dem Blatt 1 eintrage und dann ändern sich in dem Blatt automaisch alle Werte?
Also so das ich mit dem Ändern einer Zelle alle Werte auf dem Blatt ändern kann?
Blatt Peter:
Bitte geben Sie hier die Personalnummer ein: 1
Vorname: Peter
Nachname: Müller
Alter: 89
Vielen Dank Hobi84.
ich möchte in einem Excel Arbeitsmappe im ersten Blatt eine Tabelle folgender Form erstellen
Blatt 1:
1 Peter Müller 89
2 Lara Lustig 90
Dann soll erstelle ich zu jeder Zeile aus der Tabelle ein weiteres Blatt in der Excel-Mappe.
Jetzt möchte ich in jedem weiteren Blatt die Werte aus immer einer Zeile holen.
Also in Blatt Peter die Werte aus Zeile 1 und in Blatt Lara die Werte aus Zeile 2.
Kann ich irgendwo auf den Blättern Peter oder Lara eine Zelle erstellen in die ich die gewünschte Zeilennummer aus dem Blatt 1 eintrage und dann ändern sich in dem Blatt automaisch alle Werte?
Also so das ich mit dem Ändern einer Zelle alle Werte auf dem Blatt ändern kann?
Blatt Peter:
Bitte geben Sie hier die Personalnummer ein: 1
Vorname: Peter
Nachname: Müller
Alter: 89
Vielen Dank Hobi84.
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 317743
Url: https://administrator.de/contentid/317743
Ausgedruckt am: 22.11.2024 um 17:11 Uhr
14 Kommentare
Neuester Kommentar
Moin,
sverweis ist dein Freund.
Gruß Krämer
sverweis ist dein Freund.
Gruß Krämer
Hallo Krämer,
vielen Dank für die Antwort, leider funktionieren meine Tests damit nicht, könntest du ein Beispiel geben wie ich die Funktion sverweis verwenden muss?
Vielen Dank Hobi 84.
vielen Dank für die Antwort, leider funktionieren meine Tests damit nicht, könntest du ein Beispiel geben wie ich die Funktion sverweis verwenden muss?
Vielen Dank Hobi 84.
Hi,
Also wenn du schon mit Personalnummer arbeitest, dann sollte der SVVERWEIS dein Freund und Helfer sein.
Achte dann nur darauf, dass der vierte Parameter auf FALSE/ FALSCH gesetzt wird; denn dann musst du die Personalnummern nicht der größe nach sortieren.
Aber dazu musst du dann nicht für jeden Mitarbeiter ein eigenes Tabellenblatt anfertigen.
Ein Tabellenblatt mit der Datenbasis und das andere, auf dem du die Felder abfragst.
Ich kenne zwar die inhalte des Master-Tabellenblattes nicht, aber je nach Inhalt solltest du hier überlegen, ob du solch eine Tabelle jedem zugänglich machen willst/ sollst/ darfst. Stichwort Datenschutz & Co.
Das kann man ggf. aber alles abfangen, in dem du dieses Tabellenblatt sperrst, alle Daten mit weisser Farbe versetzt und auf dem "Abfrage-Blatt" eine weitere prüfung "irgendwie" einbaust, die die Personalnr. den Nachnamen und das Geburtsdatum abfragt.
Das könnte z.B. so aussehen, dass du auf der Master Tabelle folgendes machst:
und im Anzeigeblatt folgendes
Ist nicht elegant, aber so kann man nicht einfach irgendwelche Personalnr. erraten.
Gruß
em-pie
Gruß
em-pie
Also wenn du schon mit Personalnummer arbeitest, dann sollte der SVVERWEIS dein Freund und Helfer sein.
Achte dann nur darauf, dass der vierte Parameter auf FALSE/ FALSCH gesetzt wird; denn dann musst du die Personalnummern nicht der größe nach sortieren.
Aber dazu musst du dann nicht für jeden Mitarbeiter ein eigenes Tabellenblatt anfertigen.
Ein Tabellenblatt mit der Datenbasis und das andere, auf dem du die Felder abfragst.
Ich kenne zwar die inhalte des Master-Tabellenblattes nicht, aber je nach Inhalt solltest du hier überlegen, ob du solch eine Tabelle jedem zugänglich machen willst/ sollst/ darfst. Stichwort Datenschutz & Co.
Das kann man ggf. aber alles abfangen, in dem du dieses Tabellenblatt sperrst, alle Daten mit weisser Farbe versetzt und auf dem "Abfrage-Blatt" eine weitere prüfung "irgendwie" einbaust, die die Personalnr. den Nachnamen und das Geburtsdatum abfragt.
Das könnte z.B. so aussehen, dass du auf der Master Tabelle folgendes machst:
und im Anzeigeblatt folgendes
Ist nicht elegant, aber so kann man nicht einfach irgendwelche Personalnr. erraten.
Gruß
em-pie
Gruß
em-pie
[OT]
Moin,
Gruß Krämer
[/OT]
Moin,
Das kann man ggf. aber alles abfangen, in dem du dieses Tabellenblatt sperrst, alle Daten mit weisser Farbe versetzt und auf dem "Abfrage-Blatt" eine weitere prüfung "irgendwie" einbaust, die die Personalnr. den Nachnamen und das Geburtsdatum abfragt.
das ist ein klassischer Fall von "Security through obscurity"Gruß Krämer
[/OT]
Hallo,
dieses Beispiel kannte ich schon und damit klappt es nicht ganz.
Blatt Peter:
Bitte geben Sie hier die Personalnummer ein: 1
Vorname: Peter
Nachname: Müller
Alter: 89
Formeln Blatt Peter:
Bitte geben Sie hier die Personalnummer ein: "Eingabe von 1 für Peter, 2 für Lisa usw."
Vorname: =Personalnummern!B1
Nachname: =Personalnummern!C1
Alter: =Personalnummern!D1
Es soll ja nur die 1 immer in jeder Formel ersetzt werden, mit der Zahl die das Feld Eingabe Personalnummer eingegeben wird, wie geht das?
funktioniert nicht: Vorname: =Personalnummern!B&B2
funktioniert nicht: Vorname: =Personalnummern!B&"B2"
funktioniert nicht: Vorname: =Personalnummern!B&'B2'
Viele Grüße.
dieses Beispiel kannte ich schon und damit klappt es nicht ganz.
Blatt Peter:
Bitte geben Sie hier die Personalnummer ein: 1
Vorname: Peter
Nachname: Müller
Alter: 89
Formeln Blatt Peter:
Bitte geben Sie hier die Personalnummer ein: "Eingabe von 1 für Peter, 2 für Lisa usw."
Vorname: =Personalnummern!B1
Nachname: =Personalnummern!C1
Alter: =Personalnummern!D1
Es soll ja nur die 1 immer in jeder Formel ersetzt werden, mit der Zahl die das Feld Eingabe Personalnummer eingegeben wird, wie geht das?
funktioniert nicht: Vorname: =Personalnummern!B&B2
funktioniert nicht: Vorname: =Personalnummern!B&"B2"
funktioniert nicht: Vorname: =Personalnummern!B&'B2'
Viele Grüße.
Hallo em-pie,
vielen Dank für dein Beispiel.
Ich muss für jede Person ein eigenes Tabellenblatt anlegen. Dieses wird dann als PDF ausgedruckt. Die Excel Datei bekommt niemand ausgehändigt.
Ich möchte ungern in meiner Mastertabelle eine weitere Kombi-Spalte einfügen müssen. Geht das nicht auch anders?
Viele Grüße Hobi84.
vielen Dank für dein Beispiel.
Ich muss für jede Person ein eigenes Tabellenblatt anlegen. Dieses wird dann als PDF ausgedruckt. Die Excel Datei bekommt niemand ausgehändigt.
Ich möchte ungern in meiner Mastertabelle eine weitere Kombi-Spalte einfügen müssen. Geht das nicht auch anders?
Viele Grüße Hobi84.
[OT]
Moin,
Gruß Krämer
[/OT]
Moin,
Das kann man ggf. aber alles abfangen, in dem du dieses Tabellenblatt sperrst, alle Daten mit weisser Farbe versetzt und auf dem "Abfrage-Blatt" eine weitere prüfung "irgendwie" einbaust, die die Personalnr. den Nachnamen und das Geburtsdatum abfragt.
das ist ein klassischer Fall von "Security through obscurity"Gruß Krämer
[/OT]
Habe ja nicht behauptet, dass es das non Plus Ultra ist.
Aber mir fiel spontan nicht ein, wie man es besser lösen könnte, ohne eine DB dahinter zusetzen, auf die eben nicht jeder zugreifen kann.
Das war/ ist eben die Quick 'n' Dirty Variante...
Sauberer ginge es wohl mittels VBA-Code, denke ich. Hier bin ich dann aber raus.
Und solange keine sensiblen, personenbezogenen Daten enthalten sind, muss ja auch nichts abgesichert werden
@Hobi84
Dein Vorhaben würde mit der Formel INDIREKT funktionieren.
Aber das fällt auf die Nase, wenn Peter nicht mehr in Zeile 1, sondern in Zeile 2 anzutreffen ist, weil jemand oberhalb von Peter eine neue Zeile eingefügt hat.
Arbeite mit dem SVERWEIS, orientiere dich an meinem Beispiel und die Sache ist gegessen.
Wenn du keine Pseudo-Validierung einbinden willst/ haben musst, arbeite direkt mit der Personalnr. welche du ja anscheind mit verwenden willst. Eindeutiger geht es ja nicht.
Wäre ein Serienbrief dann nicht angebrachter?
Das geht doch nur unter Word, oder? Ich muss Excel verwenden, weil ich dann noch Berechnungen durchführen will.
Ziel ist es, wenn ein neuer Kollege dazu kommt wird er mit seinen Daten auf dem ersten Tabellenblatt in einer Zeile angelegt und bekommt auch eine eigene eindeutige Personalnummer zugewiesen. Dann wir im zweiten Schritt irgendein Seitenlbatt eines anderen schon existierenden Kollegen geklont. Dort möchte ich dann nur die Personalnummer ändern vom alten Kollegen in die des Neunen Kollegen und die Werte auf der Seite sollen sich passend ändern.
Ich habe es mit INDIREKT nicht hinbekommen.
Vielen Dank für jede Hilfe Hobi84.
Ziel ist es, wenn ein neuer Kollege dazu kommt wird er mit seinen Daten auf dem ersten Tabellenblatt in einer Zeile angelegt und bekommt auch eine eigene eindeutige Personalnummer zugewiesen. Dann wir im zweiten Schritt irgendein Seitenlbatt eines anderen schon existierenden Kollegen geklont. Dort möchte ich dann nur die Personalnummer ändern vom alten Kollegen in die des Neunen Kollegen und die Werte auf der Seite sollen sich passend ändern.
Ich habe es mit INDIREKT nicht hinbekommen.
Vielen Dank für jede Hilfe Hobi84.
OK, dass auch etwas berechnet werden soll, wusste ich nicht. Dann ist der SB egal.
Ansonsten bleibt es beim SVERWEIS, ohne indirekt.
hier wie folgt an einem Beispiel.
Datenbestand:
Benutzerblatt, welches du klonen kannst, OHNE etwas zu verändern:
Ansonsten bleibt es beim SVERWEIS, ohne indirekt.
hier wie folgt an einem Beispiel.
Datenbestand:
Benutzerblatt, welches du klonen kannst, OHNE etwas zu verändern:
Hallo em-pie,
vielen Dank, es funktioniert.
Das einzige was mich wundert ist das ich nicht die ganze Mastertabelle im SVERWEIS als Matrix angeben kann.
=SVERWEIS($B$1;USVs!$B$8:$BB$48;13;FALSCH) funktioniert
=SVERWEIS($B$1;USVs!$A$8:$BB$48;13;FALSCH) funktioniert nicht
Die Personalnummer steht in Spalte B, kann es damit zu tun haben,
dass das wonach gesucht wird, immer in der ersten Spalte stehen muss?
Viele Grüße.
vielen Dank, es funktioniert.
Das einzige was mich wundert ist das ich nicht die ganze Mastertabelle im SVERWEIS als Matrix angeben kann.
=SVERWEIS($B$1;USVs!$B$8:$BB$48;13;FALSCH) funktioniert
=SVERWEIS($B$1;USVs!$A$8:$BB$48;13;FALSCH) funktioniert nicht
Die Personalnummer steht in Spalte B, kann es damit zu tun haben,
dass das wonach gesucht wird, immer in der ersten Spalte stehen muss?
Viele Grüße.
Super.
Ja, das stimmt.
Per standard erwartet Excel die zu durchsuchende Spalte an erster Stelle.
Mit ein wenig Klimmzügen (google mal nach "Excel sverweis Spalten links" ) bekommt man es wohl auch hin...
Habe mich damit aber nie näher beschäftigt, da Spalte verschieben immer schneller geht...
Nachtrag:
Überdenke aber nochmal deinen Plan, für jeden MA ein eigenes Tabellenblatt anzulegen. Bei 50 MAs finde ich das etwas unübersichtlich und Datenverplemperei. Mit dem sverweis kannst du ja jederzeit einzelne MAs auswählen und dir alle erforderlichen Daten anzeigen lassen. Jetzt kenne ich eure Fluktuationsrate nicht, aber wenn dann mal 100 MAs da sind, klickt man sich ja 'nen Wolf, bis man den erforderlichen Satz hat...
Ja, das stimmt.
Per standard erwartet Excel die zu durchsuchende Spalte an erster Stelle.
Mit ein wenig Klimmzügen (google mal nach "Excel sverweis Spalten links" ) bekommt man es wohl auch hin...
Habe mich damit aber nie näher beschäftigt, da Spalte verschieben immer schneller geht...
Nachtrag:
Überdenke aber nochmal deinen Plan, für jeden MA ein eigenes Tabellenblatt anzulegen. Bei 50 MAs finde ich das etwas unübersichtlich und Datenverplemperei. Mit dem sverweis kannst du ja jederzeit einzelne MAs auswählen und dir alle erforderlichen Daten anzeigen lassen. Jetzt kenne ich eure Fluktuationsrate nicht, aber wenn dann mal 100 MAs da sind, klickt man sich ja 'nen Wolf, bis man den erforderlichen Satz hat...
Hallo em-pie,
vielen Dank für deine Hilfe!
Ich werde in das Mastertabellenblatt noch Links auf die Personalnummer legen, welche dann zu dem entsprechenden Mitarbeiterblatt springen. Damit ist das mit der Übersichtlichkeit und suchen erledigt. Einfach drauf klicken und schon beim richtigen Mitarbeiter landen und dann wieder zurück zum "Deckblatt" und zum nächsten Mitarbeiter.
Vielen Dank und ein schönes Wochenende.
vielen Dank für deine Hilfe!
Ich werde in das Mastertabellenblatt noch Links auf die Personalnummer legen, welche dann zu dem entsprechenden Mitarbeiterblatt springen. Damit ist das mit der Übersichtlichkeit und suchen erledigt. Einfach drauf klicken und schon beim richtigen Mitarbeiter landen und dann wieder zurück zum "Deckblatt" und zum nächsten Mitarbeiter.
Vielen Dank und ein schönes Wochenende.
