Bei SVerweis Matrix zu groß .. Dynamisch möglich?

Bild

Betrifft: Bei SVerweis Matrix zu groß .. Dynamisch möglich?
von: sabrina
Geschrieben am: 08.05.2015 11:15:42

Hallo Jungs,
danke für eure Hilfe.
Mein Beitrag ist leider aud dem Forum weg, deswegen eröffne ich einen neuen Beitrag
Ich habe jetzt herausgefunden wann der Fehler beim Speichern auftritt.
Der Bereich der Matrix des SVerweis umfasst mehr als 24000 Zeilen.
Wenn ich den Bereich mit

 .. Daten!$A:$H .. 
festlege kommt der Fehler.
Kann man den SVwerweis so gestallten das die Matrix immer die letzte 4000 Zeilen beinhaltet?
Wenn ich den Bereich manuell festlege
=WENN(AD2="";"";WENN(AD2="Leihgerät";"";WENN(AD2="Eigen";"";SVERWEIS(AD2;
[Auftrag.xls]Daten!$A$20000:$H$24000;8;FALSCH))))

geht es zwar, aber dann muss ich es zu oft anpassen
Der SVerweis steht im Bereich AE2:AF61, AH2:AH49 und AK2:AK24
https://www.herber.de/bbs/user/97531.zip
Gruß
Sabbel

Bild

Betrifft: AW: Bei SVerweis Dynamisch möglich? Ja
von: Daniel
Geschrieben am: 08.05.2015 12:10:18
Hi
du müsstest in der Datei "Auftrag" einen Namen definieren, welcher immer auf die letzten 4000 Zeilen verweist.
Dazu brauchst du eine Spalte, in der alle Zellen gefüllt sind (keine Leerzellen zwischen drin)
dann sieht diese Namensdefinition so aus:

=Index(Daten!$A:$A;Anzahl2(Daten!$A:$A)-3999):Index(Daten!$H:$H;Anzahl2(Daten!$A:$A))
im SVerweis gibst du dann diesen Namen als Matrix an.
Gruß Daniel

Bild

Betrifft: AW: Bei SVerweis Matrix zu groß .. Dynamisch möglich?
von: Kenny
Geschrieben am: 08.05.2015 12:12:54
Hi Sabbel,
wenn ich alles richtig verstanden habe, müsste es so funktionierten:
Visual Basics öffnen
Einfügen --> Modul
Dann in das Fenster, das sich öffnet, das hier einfügen:


Function matrix()
ende = Worksheets("Lager").UsedRange.Rows.Count
matrix = Range(Cells(ende - 4000, 38), Cells(ende, 39))
End Function
Anschließend kannst du die SVERWEIS funktion folgendermaßen gestalten:

=WENN(AD2="";"";WENN(AD2="Leihgerät";"";WENN(AD2="Eigen";"";SVERWEIS(AD2;
[Auftrag.xls]Daten!matrix();8;FALSCH))))
Die Spalten 38 und 39 musst du eben entsprechend anpassen.
Das ist die Matrix in der gesucht wird.
Liebe Grüße

Bild

Betrifft: AW: Bei SVerweis Matrix zu groß .. Dynamisch möglich?
von: Kenny
Geschrieben am: 08.05.2015 12:18:11
Eventuell noch ein public vor die Funktion setzen damit sie überall gilt


Public Function matrix()
ende = Worksheets("Lager").UsedRange.Rows.Count
matrix = Range(Cells(ende - 60, 38), Cells(ende, 39))
End Function


Bild

Betrifft: Danke .. es funktioniert wunderbar o.T.
von: Sabrina
Geschrieben am: 11.05.2015 22:28:21
LG
Sabbel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Bei SVerweis Matrix zu groß .. Dynamisch möglich?"