Dynamische Listen - Auswahl von Spalten in einer Tabelle

[Elex]

Hi

Also wenn in Tabelle "tab_Standorte" in Spalte "Ort" der Wert "Neu-Ulm" gewählt wurde, dann liste die Einträge der Tabelle "tab_Kostenstellen_NU" aus der Spalte "Bezeichnung" auf.

Das gleiche Schema dann natürlich für den Standort Parchim.

Ich schaffe es noch nicht deinen Gedanken zu folgen.

Was meinst du mit gewählt wurde. -> Wo/Wie gewählt.
Dann Liste die Einträge. -> Wo soll das dann aufgelistet werden.
Wo ist der Zusammenhang von Ort und tab Kosten-NU

Gruß Elex

[bennySB]

Also konkret am Beispiel meiner Datei:

Grundsätzlich erst einmal: Hierbei liegen alle Bezüge im Blatt "Externe Bezüge".

Wenn man in der Zelle M2 den Standort auswählt, welcher auf die Tabelle "tab_Standorte" referenziert muss nun gefiltert werden ob als Quell-Datei für die Dropdown-Liste die Tabelle "tab_Kostenstellen_NU" oder "tab_Kostenstellen_PCH" genommen wird.
Diese Dropdown-Liste wird dann in allen Spalten mit der Überschrift "Auftrag - Bezeichnung" zur Verfügung gestellt. Die Auswertung für die Spalten "Kostenstelle" kann ich dann wieder über den zuvor gemachten Eintrag mit den bereits bekannten Formeln machen.

Ps. Sorry hätte schon früher geantwortet, hab nur nicht gesehen das du auf Seite 2 gelandet bist.

[Elex]

Um hier sinnvoll zu Antworten bräuchte ich noch mehr Input.

Wird es nur die Zwei Standorte geben oder ist das nur Beispiel.
Wenn mehr Standorte wird es dann für jeden eine Kostenstellen-Tab. und Bezeichnung geben.

[bennySB]

Die enthaltenen Daten entsprechen dem Ist-Stand.
An den Standorten wird sich in den nächsten Jahren nichts verändern, einzig die Kostenstellen-Listen variieren in der Anzahl der enthaltenen Zeilen. Diese werden auch in einer externen Datei von der kaufmännischen Abteilung gepflegt und über den Power Query Editor formatiert und importiert.

Dachte es wäre irgendwie mit den Funktionen "indirekt" und "index" möglich, aber da scheitern meine Kenntnisse.

[Elex]

In Namesmanger Namen vergeben. z.B. KSBezeichnung
Die Formel dazu.
=WENN(Reisekosten!$M$2="Neu-Ulm";tab_Kostenstellen_NU[Bezeichnung];tab_Kostenstellen_PCH[Bezeichnung])

In allen Zellen wo du die Liste brauchst. 
=KSBezeichnung

[bennySB]

Okay, das ist einfacher als ich jetzt gedacht habe...Danke dir für deine Mühe.

Jetzt werde ich mir noch meine Spalte "Kostenstelle" füllen und schon bin ich fertig um es testen zu lassen.

[bennySB]

Hmmm doch noch kurz eine Sache:

Gibt es eigentlich eine einfache Methode die Formel

Code:

=WENN(ISTFEHLER(WENN(Reisekosten!$M$2="Neu-Ulm";INDEX(tab_Kostenstellen_NU[Kostenstelle];VERGLEICH(Reisekosten!$H15;tab_Kostenstellen_NU[Bezeichnung];0));INDEX(tab_Kostenstellen_PCH[Kostenstelle];VERGLEICH(Reisekosten!$H15;tab_Kostenstellen_PCH[Bezeichnung];0))));"";WENN(Reisekosten!$M$2="Neu-Ulm";INDEX(tab_Kostenstellen_NU[Kostenstelle];VERGLEICH(Reisekosten!$H15;tab_Kostenstellen_NU[Bezeichnung];0));INDEX(tab_Kostenstellen_PCH[Kostenstelle];VERGLEICH(Reisekosten!$H15;tab_Kostenstellen_PCH[Bezeichnung];0))))

in einen VBA Code zu verwandeln und dann auf diverse Zellen zu erweitern (durch Copy & Paste)?

Hintergrund meiner Frage:
Mit der Formel lasse ich mir wunderbar meine Kostenstellen füllen, jedoch möchte ich auch den Fall abfangen, dass jemand eine Kostenstelle manuell eintragen kann/darf. Nun möchte ich aber nicht das dadurch die Formel zerstört wird, daher dachte ich daran diesen Part an VBA zu übergeben.

Ich habe es schon mit der Maktro-Aufzeichnen Funktion probiert, die liefert bspw. dies hier

Code:

Sub Makro6()
'
' Makro6 Makro
'

'
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(IF(Reisekosten!R2C13=""Neu-Ulm"",INDEX(tab_Kostenstellen_NU[Kostenstelle],MATCH(Reisekosten!RC8,tab_Kostenstellen_NU[Bezeichnung],0)),INDEX(tab_Kostenstellen_PCH[Kostenstelle],MATCH(Reisekosten!RC8,tab_Kostenstellen_PCH[Bezeichnung],0)))),"""",IF(Reisekosten!R2C13=""Neu-Ulm"",INDEX(tab_Kostenstellen_NU[Kostenstelle],MATCH(Reisekosten!RC8,tab_Kostenstelle" & _
        "n_NU[Bezeichnung],0)),INDEX(tab_Kostenstellen_PCH[Kostenstelle],MATCH(Reisekosten!RC8,tab_Kostenstellen_PCH[Bezeichnung],0))))" & _
        ""
    ActiveCell.Offset(1, 0).Range("A1:F1").Select
End Sub

Wobei natürlich der letzte "ActiveCell" Bereich gelöscht werden kann. Jedoch funktioniert dies nicht so wirklich.

[Elex]

Mit der Formel lasse ich mir wunderbar meine Kostenstellen füllen, jedoch möchte ich auch den Fall abfangen, dass jemand eine Kostenstelle manuell eintragen kann/darf. Nun möchte ich aber nicht das dadurch die Formel zerstört wird

In einer Zelle steht entweder eine Formel oder ein fester Wert. Heist wenn jemand was rein schreibt ist die Formel weg.

[bennySB]

Ja das stimmt, aber wenn man die von mir gepostete Formel per VBA ausführen würde und dadurch die Formel aus der Zelle verschwindet, würde es doch funktionieren...oder nicht?

Dann der der Vorgang ja folgender:
Wenn in den Spalten "Auftrag - Bezeichnung" eine Auswahl per Dropdwon-Liste gemacht wird füge die zugehörige Kostenstelle in die Spalte "Kostenstelle" ein. Dies wird aktuell mit der Formel gemacht und soll nach VBA exportiert werden.
Wenn nun in der Spalte "Auftrag - Bezeichnung" eine manuell Eintragung erfolgt kann der VBA Code ja kein Ergebnis liefern, weil keine Auswahl aus der Liste stattgefunden hat, also kann manuell etwas eingetragen werden in der Spalte "Kostenstelle".

Oder würde der VBA-Code die manuelle Eintragung immer mit einem leeren Zelleninhalt überschreiben?

[Elex]

mal auf die schnelle. Probieren. Ich bin erst Morgen wieder dabei.

Der Code kommt in das Modul Reisekosten.
In der Zeile gibst du die Bereiche an für die es gelten soll.
If Not Application.Intersect(Target, Range("H15:H19, H22:H28")) Is Nothing And Target.Cells.Count = 1 Then

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Range

If Not Application.Intersect(Target, Range("H15:H19, H22:H28")) Is Nothing And Target.Cells.Count = 1 Then
  If Range("M2") = "Neu-Ulm" Then
    Set a = Sheets("Externe Bezüge").ListObjects("tab_Kostenstellen_NU").ListColumns(1).Range.Find(Target, LookAt:=xlWhole)
      If Not a Is Nothing Then Target.Offset(, 1).Value = a.Offset(, 1).Value
      Set a = Nothing
  Else
      Set a = Sheets("Externe Bezüge").ListObjects("tab_Kostenstellen_PCH").ListColumns(1).Range.Find(Target, LookAt:=xlWhole)
      If Not a Is Nothing Then Target.Offset(, 1).Value = a.Offset(, 1).Value
      Set a = Nothing
  End If
End If
End Sub

Gruß Elex