Office: Formel mit Text nach Bedingung

Hallo

In einer Excel Tabelle sind folgende Daten eingetragen:

Gerätename Abteilung Beziehung Benutzer
Computer 1 Informatik Hans
Computer 2 Finanzen Peter
Bildschirm 1 x Computer 1 y
Bildschirm 2 x Computer 2 y
usw.

Nun soll eine Formel im Feld x die Abteilung automatisch einsetzn, wenn das Feld Beziehung bei der Zeile wo der Gerätename steht, ausgefüllt wird, und auch gleich der Benutzername bei y.

Ziel ist es, dass man ersehen kann welcher Monitor zu welchem PC gehört. Kann so etwas in Excel gemacht werden?

So soll die Tabelle mit Formel aussehen:

Gerätename Abteilung Beziehung Benutzer
Computer 1 Informatik Hans
Computer 2 Finanzen Peter
Bildschirm 1 Informatik Computer 1 Hans
Bildschirm 2 Finanzen Computer 2 Peter
usw.

Vielen Dank und Gruss.

 

Hola,

ich hab’s nicht wirklich verstanden. Lade eine Exceldatei hoch und zeige händisch dein Wunschergebnis.
Gruß,
steve1da

 

Hallo, hier ist eine Beispieldatei. Die blauen Felder sollen Formeln enthalten und automatisch ausgefüllt werden. Vielen Dank für Ihre Hilfe

 

Hallo,

in der beiliegenden Datei habe ich dir 2 Varianten eingebaut, die beide mit strukturierten (="intelligenten") Tabellen arbeiten.

 

@Exl121150., Herzlichen Dank für die Hilfe. Jetzt muss ich es nur noch verstehen und in meine Tabelle einfügen. Viele Grüsse

 

@Exl121150: Ich habe noch folgende Herausforderung:

Die Tabelle wird nun laufend mit neuem Inventar gefüllt. (mal Computer, mal Bildschirm) Die Formeln sollen demnach nur ausgeführt werden, wenn in den Felder der Spalte A der Text Bildschirm eingetippt wird. Ist das möglich?

Besten Dank im Voraus.

 

Hallo,

für Variante 1 in der beiliegenden Datei wird keine Änderung benötigt. Dort kann man weitere Zeilen an die Tabelle "tbBildschirme" anfügen, indem man in Spalte A ("Bildschirm") weitere Bildschirme eingibt. Die Formeln für Spalte "Abteilung" und Spalte "Benutzer" werden automatisch eingefügt.

Anders sieht es bei Variante 2 aus. Da hier in Abhängigkeit von Spalte A ("Computername") in den Spalten "Abteilung" und "Benutzer" Textkonstanten bzw. Formeln erzeugt werden sollen, habe ich für diese Pfuschsituation ein VBA-Makro geschrieben, das abfragt, ob in Spalte A der eingegebene Text mit "Bildschirm" beginnt: Falls ja, werden in den Spalten "Abteilung" und "Benutzer" SVERWEIS-Formeln eingefügt; Falls nein, sind dort Texte einzugeben.
Dieses Makro befindet sich im Codemodul des Arbeitsblattes "Tabelle1"

Code:

Option Explicit
'
Const STRUKTABELLE As String = "tbInventar"      '<== Name der Inventartabelle
'
Const GERAETESPALTE As String = "Computername"   '<== Name der Gerätespalte (Computer X, Bildschirm Y)
Const ABTEILGSPALTE As String = "Abteilung"      '<== Name der Abteilungsspalte
Const BEZIEHGSPALTE As String = "Beziehung"      '<== Name der Beziehungsspalte
Const BENUTZRSPALTE As String = "Benutzer"       '<== Name der Benutzerspalte
'
Const KRITERIUMASKE As String = "Bildschirm *"   '<== Inhalt der Kriterienmaske (für die Gerätespalte)
'
Const FORMEL As String = "=VLOOKUP([@{Bez}],{Tb},{Sp},FALSE)"  'Abfrageformel für Abt./Ben.Spalte
'
Dim lstObj As ListObject
Dim lstRowIdx As Long, lstColIdx As Long
Dim strFormel As String

Private Sub Worksheet_Change(ByVal Target As Range)
  If IstInListObjekt(Target) Then
    With lstObj
      If .Name = STRUKTABELLE Then
         On Error GoTo Exit_Ws_Change
         Application.EnableEvents = False
         
         If .ListColumns(lstColIdx).Name = GERAETESPALTE Then
            If Target.Value Like KRITERIUMASKE Then
               strFormel = Replace(Replace(FORMEL, "{Tb}", .Name), "{Bez}", BEZIEHGSPALTE)
               With .ListColumns(ABTEILGSPALTE)
                  .DataBodyRange(lstRowIdx).Formula = Replace(strFormel, "{Sp}", .Index)
               End With
               With .ListColumns(BENUTZRSPALTE)
                  .DataBodyRange(lstRowIdx).Formula = Replace(strFormel, "{Sp}", .Index)
               End With
            End If
         End If
       
Exit_Ws_Change:
         Application.EnableEvents = True
      End If
    End With
  End If
End Sub
'
'Funktion testet, ob "rngZelle" innerhalb einer strukturierten Tabelle liegt:
'  falls ja:   werden Objektzeiger lstObj, ZeilenNr lstRowIdx, SpaltenNr lstColIdx befüllt
'  falls nein: werden lstObj=NOTHING, ZeilenNr=0, SpaltenNr=0 gesetzt
'
Function IstInListObjekt(rngZelle As Range) As Boolean
   Set lstObj = rngZelle.ListObject
   IstInListObjekt = Not lstObj Is Nothing  'Rückgabe Funktionsresultat
   If IstInListObjekt Then
     'falls ja:
     With lstObj.HeaderRowRange
        lstRowIdx = rngZelle.Row - .Row
        lstColIdx = rngZelle.Column - .Column + 1
     End With
   Else
     'Falls nein:
     lstRowIdx = 0: lstColIdx = 0
   End If
End Function

Ich habe es so konstruiert, dass man die Konstanten-Definitionen am Anfang des Makros, deren Kommentar mit "<==" beginnt, gegebenenfalls an geänderte Inhalte des Arbeitsblattes anpassen kann.