SVERWEIS mit INDIREKT Pfadangabe der Tabelle

Computer: Software & Programmierung Office Software
#1

Hallo,

ein kleines Problemchen mal wieder, das mich schon seit 3 Stunden fesselt und ich bin kein Stückchen weiter.

Situation:
Lieferantenbewertung, Excel 2010.
Hardfacts etc wurden aus dem ERP importiert, jetzt geht es um das Softfact Handling. Man will ja nicht jedes mal bei 300 Lieferanten jeweils 10 Softfacts neu eingeben. Aber man bestellt auch nicht immer bei den gleichen Lieferanten bzw bei allen. Nun will ich aus der vorherigen Auswertungsdatei, in der die meisten Lieferanten ja schon bewertet sind, die letzte Bewertung abholen. Das sollte ja, da ich keinen Plan von VBA und co habe, mit dem SVERWEIS ganz gut gehen. SVERWEIS bekomm ich hin. Nun will ich aber dem Einkäufer auch nicht zumuten, jedes mal alle sverweise auf den aktuellen Pfad zu ändern. Daher wollte ich mit der mir bisher unbekannten Funktion INDIREKT den Speicherort der Datei in den SVERWEIS bekommen.

Mein Pfad steht in Zelle $O$2,

Suchkreterium ist (großzügig) $A$9:blush:A$500

Matrix(mein Problem): Pfad aus Zelle „$O$2“ , Tabellenblatt „Bewertung“ , $A$9:blush:AD$500

Spaltenindex: fangen wir mit der 7 an, da steht der erste Softfact… das auf die andren Softfacts anzupassen bekomme ich hin :smile:

Bereich_Verweis: FALSCH

Wie gesagt, ich komme nicht weiter, da mir das Wissen um den Aufbau der Pfadbeschreibung in einem INDIREKT Verweis auf eine andere Datei fehlt. Wenn mir jemand die Formel basteln könnten + mir erklären wie sie funktioniert, damit ich nicht immer fragen muss, wäre ich euch sehr verbunden :smile:

Grüße
M.Wolf

#2

Mein Pfad steht in Zelle $O$2,

Hallo M.,

was genau steht in O2?
Man braucht den Pfad und den Mappennamen.

Gruß
Reinhard

#3

in der Zeile steht aktuell Z:\Michael Wolf\Lieferantenbewertung\Entwurf für neue LBW Tabelle-test.xlsx

Das Tabellenblatt heisst Bewertung

ich hoffe das sind ausreichend Infos um mir mit der Formel weiter zu helfen

Aber ich dachte mit bisher, den Pfad brauch ich nicht angeben für die Formel, da in der Formel nur der „Verweis“ auf auf die Zelle erfolgen soll, damit der Sverweis dynamisch auf die jeweilig letzte Bewertung angepasst werden kann.

Grüße
M.Wolf

#4

Grüezi Wolf

in der Zeile steht aktuell Z:\Michael
Wolf\Lieferantenbewertung\Entwurf für neue LBW
Tabelle-test.xlsx

Das Tabellenblatt heisst Bewertung

ich hoffe das sind ausreichend Infos um mir mit der Formel
weiter zu helfen

Um diesen Namen herum müssen noch Hochkommas gesettz werden, da er Leerzeichen enthält.

Aber ich dachte mit bisher, den Pfad brauch ich nicht angeben
für die Formel, da in der Formel nur der „Verweis“ auf auf
die Zelle erfolgen soll, damit der Sverweis dynamisch auf die
jeweilig letzte Bewertung angepasst werden kann.

Ja, das ist prinzipiell richtig - aber (und jetzt kommt die Krux an der ganzen Sache) um mit INDIREKT() arbeiten zu können muss die jeweilige Datei aus der Du die Daten holst geöffnet sein.

Meiner Ansicht nach ist es daher einfacher mit STRG+H den jeweiligen Teil des Pfades in allen Formeln zu ersetzen.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#5

Es sich hier um eine Lieferantenbewertung mit ca 400 aktiven Lieferanten die sich von in den Bewertungszeiträumen auch teilweise unterscheiden können. Das ERP liefert die jeweilige Liste in Alphabetischer Ordnung mit diversen Hardfacts (Liefertreue, Mengentreue, Reklamationen etc). Zu jedem Lieferant gibt es 15 Softfacts, die händisch gepflegt werden müssen. Um diesen Aufwand nicht jedes mal zu haben, sollen die alten Werte übernommen werden (SVERWEIS wegen der eventuell veränderten Liste) und nur die bei denen etwas sich wissentlich verändert hat geändert werden.

Okay, einfache Mathematik: 400 x 15 bedeutet 6000 Formeln händisch mit dem neuem Pfad versehen :s

2 Dateien zu öffnen und einen Zelle umschreiben scheint mit bei den selbst gegen mich Excel-noobs weitaus praktischer also denen zu erklären wie sie „alles Ersetzen“ hinbekommen… zumal ich mir auch gerade nicht sicher bin, ob dass auf die Formel zugreift, auch wenn sie ein Ergebnis auswerfen…

Ich hoffe ich habe mein jetzt anliegen eindeutig geschildert.

#6

Grüezi Redflow

Dass Du dir so umfassende Gedanken gemacht hast, konnte ich deinen Beiträgen nicht entnehmen und es stellt IMO eine löbliche Ausnahme dar.

Zu deinem Anliegen:

  • Erstelle erstmal einen direkten Link zu einer deiner Dateien mit SVERWEIS() und schau dir genau an, wie der Dateiname und ggf Pfad der Datei dort geschrieben wird.

  • Gehe dann daran, diesen Teil mit INDIREKT() zu ersetzen und nimm auch die Bezüge zur SVERWEIS-Matrix mit in die INDIREKT()-Formel auf.

Wenn Du uns eine deiner SVERWEIS()-Formeln 1:1 zeigst, dann können wir dich auch da zielgerichtet unterstützen. BTW: Das Suchkriterium im SVERWEIS() soll/darf nur eine einzelne Zelle sein…

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#7

Hallo Michael,

in der Zeile steht aktuell Z:\Michael
Wolf\Lieferantenbewertung\Entwurf für neue LBW
Tabelle-test.xlsx
Das Tabellenblatt heisst Bewertung

also
Pfad: Z:\Michael Wolf\Lieferantenbewertung
Name: Entwurf für neue LBW Tabelle-test.xlsx

Für SVerweis (ohne Indrekt()) bedeutet dies:

=SVerweis(A9;‚Z:\Michael Wolf\Lieferantenbewertung[Entwurf für neue
LBW Tabelle-test.xlsx]Bewertung‘!$A$9:blush:AD$500;7;0)

Ist das so korrekt aufgrund deiner Angaben?

Gruß
Reinhard

#8

Da die Dateien an meinem Arbeitsplatz in der Firma sind, kann ich das jetzt nicht ausprobieren, aber es sieht auf den ersten Blick schon mal nicht falsch aus :wink: (Mein Arbeitgeber hält noch nicht viel von Clouds, ich komme als die nächsten Tage nicht ran an die Dateien)

Wenn ich das jetzt noch INDIREKT hinbekomme, wird alles schick :wink:

Vielen Dank erstmal bis hierhin

#9

Hallo Michael,

Da die Dateien an meinem Arbeitsplatz in der Firma sind, kann
ich das jetzt nicht ausprobieren, aber es sieht auf den ersten
Blick schon mal nicht falsch aus :wink:

ja, sieht richtig aus. Kann aber noch ein Fehler drin sein da
ich es nicht testete.

(Mein Arbeitgeber hält
noch nicht viel von Clouds, ich komme als die nächsten Tage
nicht ran an die Dateien)

Na und, warum die Osterzeit verschenken. Für das was du lernen
, wissen willst brauchste doch nicht die Originalmappen-
strukturen.

Nimm 'ne leere Mappe, in Tabelle1!A schreibste paar Suchkriterien
rein, in Tabelle1!H (7-te Spalte von A:AD) auch paar Werte.

In eine andere neue Mappe schreibst du auch in A die Suchkriterien.
In B1 dann
=sverweis(a1;[Mappe1]Tabelle1!$A$9:blush:AD$500;7;0)

Nun speicherst du Mappe1 in C:\test oder so, mit einem anderen
Namen als Mappe1.
Nun muß in B1 ein SVerweis wie von mir stehen.

Spätestens jetzt wird dir auffallen daß du neben SVerweis und Indirekt
noch eine Problematik hast. In der SVerweis Formel brauchst du Pfad
und Mappenname getrennt.
Im Original haste aber beides zusammen in O2.

Wenn du es bei der „herstellung“ der Originaltabelle nicht hinkriegst
beide zu trennen muß das jetzt geschehen aufgrund des Inhalts von O2.

Mit Makros geht das fix, aber dazu müßten die User IMMER bei
Mappenstart Makros zulassen anklicken.
Ohne Vba geht das auch aber mir fällt derzeit keine Lösung ohne
Hilfsspalten dazu ein.

Wenn ich das jetzt noch INDIREKT hinbekomme, wird alles schick
:wink:

Indirekt geht dann wieder recht einfach.

Gruß
Reinhard

#10

Grüezi Reinhard

In eine andere neue Mappe schreibst du auch in A die
Suchkriterien.
In B1 dann
=sverweis(a1;[Mappe1]Tabelle1!$A$9:blush:AD$500;7;0)

Nun speicherst du Mappe1 in C:\test oder so, mit einem anderen
Namen als Mappe1.
Nun muß in B1 ein SVerweis wie von mir stehen.

Spätestens jetzt wird dir auffallen daß du neben SVerweis und
Indirekt
noch eine Problematik hast. In der SVerweis Formel brauchst du
Pfad
und Mappenname getrennt.

Hmmm, da die verknüpfte Mappe bei der Verwendung von INDIREKT() ja eh geöffnet sein muss, ist der Pfad zur Mappe dann wieder nicht notwendig - oder irre ich mich am frühen Morgen?

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#11

Hmmm, da die verknüpfte Mappe bei der Verwendung von
INDIREKT() ja eh geöffnet sein muss, ist der Pfad zur Mappe
dann wieder nicht notwendig - oder irre ich mich am frühen
Morgen?

Grüezi Thomas,

ich glaub da haste wahr :smile:
Der Pfad in O2 hat mich wohl irritiert oder sowas.

@Michael, damit ich mal was produktives „liefere“:
=SVERWEIS(A1;INDIREKT("’["&$O$2&"]Bewertung’!$A$9:blush:AD$500");7;0)

Diese Formel bedingt daß in O2 nur der Mappenname samt Endung steht.

Gruß
Reinhard