Office: (Office 2016) Wort suchen und Bezug einfügen mehrere Arbeitsblätter

Hallo Zusammen,

ich habe eine Arbeitsmappe mit mehreren Blättern. Auf einigen sind Angebotstexte, die ich auf einem Arbeitsblatt anzeigen lassen möchte. Dazu wird nach einer Modellnummer gesucht und die Zellen darunter werden ausgegeben.
Das funktioniert mithilfe der INDEX und VERGLEICH Funktion auf einem Arbeitsblatt auch ganz gut. Nun möchte ich auf mehreren Arbeitsblättern suchen.
Mein Formel :
=WENNFEHLER(INDEX('Tabellee1'!$B$1:'Tabelle1'!$B$323;VERGLEICH(Dateneingabe!$B$15;'Tabelle1'!$B$1:$B$323;0)+1);"")
hat jemand eine Idee, wie ich das Ganze auf mehrere Blätter erweitern kann?

 

Hallo,

am einfachsten ist es, anstelle der beiden Anführungszeichen am Ende deiner Formel nochmals die gleiche Formelsequenz zB. für Arbeitsblatt "Tabelle2" einzufügen:
=WENNFEHLER(INDEX(Tabelle1!$B$1:$B$323; VERGLEICH(Dateneingabe!$B$15; Tabelle1!$B$1:$B$323;0)+1);
WENNFEHLER(INDEX(Tabelle2!$B$1:$B$323; VERGLEICH(Dateneingabe!$B$15; Tabelle2!$B$1:$B$323;0)+1);""))
Wird der gesuchte Wert aus Dateneingabe!$B$15 nicht in Tabelle1 gefunden, wird aufgrund der 1. WENNFEHLER-Funktion die Suche in der 2. WENNFEHLER-Funktion durchgeführt und somit im Arbeitsblatt Tabelle2 gesucht. Nur wenn dies scheitern sollte, wird schließlich "" zurückgegeben.

 

Danke, das wird dann über viele Arbeitsblätter eine recht lange Formel, aber sollte funktionieren.

 

Ich habe nun eine Datei in der auf bis zu 10 verschiedenen Tabellenblättern gesucht werden soll. Gibt es da noch eine kürzere Lösung? Kann man Tabellenblätter irgendwie als Array definieren?

 

Hallo,

das kann man, wenn man VBA verwendet.

In der beiliegenden Excel-Datei habe ich so aus:
=WENNFEHLER(INDEX(Tabelle1!$B$1:$B$323; VERGLEICH(Dateneingabe!$B$15; Tabelle1!$B$1:$B$323;0)+1); WENNFEHLER(INDEX('Bl2'!$B$1:$B$323; VERGLEICH(Dateneingabe!$B$15; 'Bl2'!$B$1:$B$323;0)+1); WENNFEHLER(INDEX(Tabelle4!$B$1:$B$323; VERGLEICH(Dateneingabe!$B$15; Tabelle4!$B$1:$B$323;0)+1);"")))
folgende Formel gemacht:
=SucheNxtZeile(Dateneingabe!$B$15; $B$1:$B$323; "Tabelle1";"Bl2";"Tabelle4")

Dabei befindet sich die benutzerdefinierte Funktion "SucheNxtZeile" im allgemeinen VBA-Modul "Modul1":

Code:

Option Explicit

Public Function SucheNxtZeile(rgKrit As Range, rgSuchbereichJeBlatt As Range, ParamArray Blaetter() As Variant) As String
  Dim Blatt As Variant, Ws As Worksheet
  Dim rgWsSuchbereich As Range, SuchBereich$
  Dim FundZeile As Long
  Dim Krit As String
  
  'Wert des 1.Parameters:
  Krit = rgKrit.Value
  'Suchbereich je Arbeitsblatt:
  SuchBereich$ = Split(rgSuchbereichJeBlatt.Address(external:=True), "!")(1)
  'Rückgabewert, falls "Krit" nicht gefunden wurde:
  SucheNxtZeile = ""
  
  On Error GoTo Err_Suche
  'Durchsuche die Arbeitsblattliste "Blaetter()"
  For Each Blatt In Blaetter()
    'Ermittle ein bestimmtes aufgelistetes Arbeitsblatt
    Set Ws = Worksheets(Blatt)
    'Ermittle den Suchbereich in diesem Arbeitsblatt
    Set rgWsSuchbereich = Range(Ws.Name & "!" & SuchBereich$)
    
    'Wurde nichts gefunden -> Fehler (->Err_Suche:) -> N?chstes Arbeitsblatt
    'Wurde "Krit" gefunden, enth?lt "FundZeile" die Zeilennummer des Fundes im Arbeitsblatt
    FundZeile = WorksheetFunction.Match(Krit, rgWsSuchbereich, 0)
    'Wurde "Krit" gefunden, wird der Wert der nächsten Zeile zurückgegeben
    SucheNxtZeile = rgWsSuchbereich.Cells(FundZeile + 1).Value
    'Wurde "Krit" gefunden, wird die Suche in den Arbeitsblättern abgebrochen.
    Exit For
NxtBlatt:
  Next Blatt
  Exit Function
  
Err_Suche:
  Resume NxtBlatt

End Function

Eine genaue Beschreibung dieser Funktion befindet sich im Arbeitsblatt "Tabelle1".