[Software] EXCEL x.0: SVERWEIS gibt teilweise #NV aus

[nordstern]

hi,

ich arbeite gerade beruflich an der Umstrukturierung der BWA. Dafür will ich die sverweis-Funktion nutzen, da diese auch auf geschlossene Dateien zugreift. Nur habe ich dabei ein Problem, an dem mit das Internet bisher nicht helfen konnte.

Die Funktion umfasst 30 Zeilen und 3 Spalten. Sie gibt aber an 6 Stellen NV aus. Internet meinte es liegt an der Formatierung... oder Leerstellen/Schreibfehler. Beides kann ich ausschließen (Copy-Paste), außer es kann sein das die eingestellte und angezeigte Formatierung nicht die gespeicherte ist (trotz speichern natürlich). Ich habe inzwischen rausgefunden das der Fehler am importierten Tabellenblatt liegt.
Spalte 1 ist überall als Text formiert, Spalte 2 und 3 (2 irrelevant) als Zahl. Und die Funktion geht... nur eben an einigen Stellen nicht.

hier die Funktion:
=SVERWEIS($C20;'[XXX.xls]FB 3'!$A$7:$C$53;3)

Die importierte Matrix besteht selbst aus Formeln, aber ich habe alle Felder zurückverfolgt. Keine ist nicht als Zahl formatiert.

Hat jemand noch ne andere Idee woran es liegen könnte?

grüsse
nordstern

[Horgan]

Schwer zu sagen, ohne die Datei zu haben. Ich hätte noch nen ";FALSCH" vor das Klammerende gesetzt. Ansonsten können es mMn nur die Formate sein, wenn die Inhalte wirklich vollidentisch sind.

[nordstern]

aber wie kann ich das ändern wenn mir überall die selben Formate angezeigt werden? Ich hab ja auch schon alles auf Datum und wieder zurück geändert um sicher zu gehen das er die Änderung vornimmt.

Ich habe mich heute Abend mit einem Ersatz versucht: Makro... würde ich aber gerne nicht einsetzen. Dafür muss er nämlich die Dateien öffnen... bei 11 Tochtergesellschaften mit nochmals je etwa 3 Untergesellschaften und je 12Monaten sowie 30 BWA-Daten und 2 Tabellen müsste das Makro nämlich 24.000 Zellen auslesen. Abgesehen vom Programmieraufwand braucht das Makro bereits bei 66 Tabellen zu je 30 Daten ganze 2 Minuten. Eben weil alles geöffnet werden muss. Das wollte ich ja mit dem sverweis umgehen.

Alternativ wüsste ich nur: Chef gehen, meckern weil kein gescheites ERP/Access und mich auf einen Kompromiss einigen der wenigstens den Monatsfaktor 12 rausnimmt, indem ich in der Ursprungsdatei statt jeden Monat/UN eine neue Datei, einfach in einer Datei ein Jahr unterbringe. War aber im Vorfeld ausdrücklich nicht gewünscht.

[Horgan]

Musst Du halt analysieren.. ..Formatänderungen sind nicht immer mit der Spaltenformatierung erledigt.
Manchmal muss man die einzelnen Zellen nochmal aktivieren (Doppelklick und ENTER).
Manchmal haben Zahlen noch nen ' vor dem Wert oder . als Dezimaltrennzeichen erfasst, dann ist die Spalte/das Feld zwar Zahlenformat, der Wert aber TEXT - muss man rausnehmen.
Manchmal hat die Spalte zwar das Format, aber die Daten müssen separat nochmal umgewandelt werden (wenn man die Zellen markiert, über diese gelbe Raute mit dem Ausrufezeichen, die neben dem markierten Bereich oben oder unten erscheint).

Kannst ja mal den hier versuchen, bevor du die ganzen Quelldateien durchsuchst:

Code: Alles auswählen

=WENN(ISTFEHLER(SVERWEIS($B5;$H$5:$I$7;2;FALSCH));SVERWEIS(TEXT($B5;"0");$H$5:$I$7;2;FALSCH);SVERWEIS($B5;$H$5:$I$7;2;FALSCH))

Wenn das Ergebnis #NV rauskommt, soll er nach der Zahl im Textformat suchen, andernfalls normal.

Gibt noch mehr denkbare Optionen.. ..kann die nicht alle aufzählen. Da aber nur einige Zellen #NV sind, solltest Du den Fehler schnell finden können - ist ja schon eingegrenzt.

[nordstern]

danke dir werd das morgen mal nwenden.

[nordstern]

hat funktioniert. Ironischerweise hat deine Formel den Fehler sogar behoben.

Neues Problem:
Ich habe eine Mappe mit 13 Tabellenblätter, dabei haben 12 Verknüpfungen an externe Dateien. Nun werde ich zum Start gefragt ob ich aktualisieren will, das dauert 18 Sekunden. Doch irgendwie mag ich das nicht. Es ändert sich nie mehr als ein Tabellenblatt.

Ich habe die automatische Aktualisierung deaktiviert, weis nun aber nicht wie ich eine Tabellenblattweise Aktualisierung auf Wunsch hinbekomme. Ich dachte da an ein Makro, aber finden tu ich im Internet nichts. Meines Erachtens ist es jedoch absolut unwirtschaftlich, wenn ich 12 Tabellenblätter aktualisieren muss und jedes Mal 20 Sekunden vergehen bzw. das Dokument neu gestartet werden muss (da er anders nicht wirklich Verknüpfungen aktualisiert im Sverweis).

Weist du da was?

[Horgan]

Aus der Hüfte geschossen würde ich die automatische Aktualisierung deaktiviert lassen und dann per VBA eine kleine Workbook_Open- oder Worksheet_Activate-Routine schreiben.

Also so, dass beim Öffenen der Datei oder beim Aktivieren des entsprechenden Arbeitsblattes ein

Code: Alles auswählen

worksheets("XYZ").calculate

durchgeführt wird, um das Arbeitsblatt neu zu berechnen und damit die Verknüpfungen zu aktualisieren.

[nordstern]

hab etwas rumprobiert. worksheet.calculate hat nicht funktioniert, da er dabei (ähnlich wie neuberechnen) die Formeln neu rechnet aber nicht externe Daten aktualisiert.

Ich habs jetzt auf die Noobvariante gelöst:
Ich hab einfach Makro aufzeichnen aktiviert und die Verknüpfungen aktualisiert. Dadurch sah ich welchen Befehl er dafür nutzt und habe das dann für meine Zwecke gebraucht. Er nutzt dabei eine soweit ich das noch weis *.updatelink befehl.

So funktioniert es nun. Habe 12 Blätter mit einen Button der beim Klick sich updated, was lediglich 1,5sec dauert und im Hauptdatenblatt einen Button der alles aktualisiert, was ca. 20 sec dauert.


Ich bin jetzt erstmal an nem anderen Projekt. Doch was mich aktuell noch etwas stört sind die festen Datei und Verzeichnisstrukturen. Ich versuche das irgendwann (wenn vom AG gewünscht) noch zu ändern. Ist aber ne heiden Arbeit. Da ich sämtliche Formeln (lt Suche 1364/Tabellenblatt) dann anpassen muss. Und C&P nicht so einfach möglich ist wg wechselnder Dateien beim ziehen.

Ich dachte da (ist das machbar?) an eine Zelle in welcher der Pfad steht und an einer mit dem Dateinamen. Idealerweise währe es natürlich wenn ich ihn dazu bringen könnte nach dem Wert in einer Zelle der untegerodneten xls zu suchen wo der Dateiname drin steht. Aber kann ich mir überhaupt in der Datei den Dateinamen anzeigen lassen? Und wie würden diese Abfragen laufen? Sehr lange?

[Horgan]

Es geht schon eine Menge, um mit dem Pfad zum gewünschten Ergebnis zu kommen. Allerdings muss man sich schon fragen, wie oft sich der Pfad denn so ändert und ob es bei seltenen Änderungen ein Suchen und Ersetzen auch tut - dann ändert EXCEL die Dateinamen in den Formeln auch.

Wenn es aber Hyperlinks und keine Formeln sind, wird bei Suchen und Ersetzen nur die angezeigte Hyperlinkbezeichnung geändert - der Link selbst bleibt unberührt. Bin mir jetzt nicht sicher, was du konkret meinst, denn in Formeln kenne ich nur den Dateinamen - die Pfadangabe steht in den Formeln eigentlich nicht drin (habe das eben noch einmal mit unterschiedlichen Laufwerken getestet). In einem Hyperlink steht der Pfad.

Ansonsten kannst Du sowas freilich auch per VBA lösen.. ..bspw. irgendwo (im Code selbst oder in einer Zelle irgendwo auf einem Arbeitsblatt) den Pfad hinterlegen. Änderst Du diesen Pfad auf dem Arbeitsblatt, könntest du in einer "Worksheet_SelectionChange"-Sub entsprechend prüfen, ob sich der Inhalt geändert hat und dann ggf. eine Schleife programmieren, die im Bereich XYZ die Formeln oder Hyperlink prüft und ggf. anpasst - bei Formeln ist es die Formula-Eigenschaft, bei Hyperlinks die Adress in Item.

Schwer zu beurteilen, welche Lösung die optimale für Dich ist.. ..das gesamte Konstrukt klingt wirr.

[nordstern]

Danke, ich habe es mir überlegt und etwas rumprobiert und mich schließlich für ein Makro mit Button-Klick entschieden. Leider funktioniert das nicht wie gewollt.

Aktuell gebe ich ihm vor welche Links er aktualisieren soll. Kann ich ihm aber auch einfach sagen alle im Tabellenblatt? Des Weiteren kommt ein Laufzeitfehler 1004.

Hier mal mein Code:

Code: Alles auswählen

[/Sub CommandButton1_Click()
' Makro Aktualisierung
    ActiveWorksheet.UpdateLink Name:= _
        ActiveWorkbook.Path & "\September\TochterunternehmenX.xls , Type:= _
        xlExcelLinks
       
              ' das ganze mit 11 verschiedenen Zieldateien.

End Sub

Irgendwas scheint da nicht zu funktionieren. Ich vermute das es sich um "ActiveWorkbook.Path &" handelt. Ich brauche aber eine Stamm-Dateipfadermittlung da diese flexibel sein muss und nur die Unterstruktur ist fix.

[Horgan]

Zumindest fehlt bei der Verkettung des neuen Pfadnamens ein abschließendes ".

Ansonsten hatte ich Dir die eine Lösung ja aufgezeigt
- Du kannst Hyperlinks in den Eigenschaften des Worksheets auslesen und die so ermittelten Links entsprechend sinnig anpassen.
- Du kannst einen Bereich X:Y auf Inhalt ".xlsx" etc. durchsuchen und bei Treffern einen neuen Hyperlink in den jeweiligen Zellen hinterlegen.
- Du kannst auch nur den Dateinamen erfassen und die Zelle "by_click" mit ner Suche und Öffnung der Datei im Pfad XYZ programmieren. (Bei Click in Zelle prüfen, ob Inhalt mit xlsx endet. Wenn ja, dann suche im definierten Pfad die Datei und öffne sie - quasi ein eigener custom hyperlink.)

[nordstern]

hab das Problem gefunden:

Dadurch das Sverweis auf externe, geschlossene Dateien zugreifen kann gibt es eine Art Sperre die Bezüge im Sverweis verhindert, um zu verhindern das sverweis die Dateien öffnen muss quasi.

Ich habe ein Makro geschrieben, das funktioniert aber nicht richtig. Beim Ausführen nach der MsgBox ändert er nicht den Pfad sondern öffnet eine Dialogbox in der ich die Datei manuell auswählen soll. Eigentlich sollte er den Pfad automatisch ändern. Die in der MsgBox ausgegebene Funktion stimmt bis auf die Kleinigkeit das er den sverweis durch eine vlookup ersetzt hat.

Code: Alles auswählen

Private Sub CommandButton2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim zeile As Long
 Dim spalte As Long
 Dim pfadend As Integer
 Dim pfadanf As Integer
 Dim formel As String
 Dim test As Long
 On Error GoTo Fehler1
 For zeile = 1 To UsedRange.Rows.Count
  For spalte = 1 To UsedRange.Columns.Count
   If Cells(zeile, spalte).HasFormula = True Then
    formel = Cells(zeile, spalte).Formula    ' mit Fall umgehen, dass vor SVERWEIS Leerzeichen stehen (= immer am Anfang!)
    Mid(formel, 1, 1) = " "                   ' das Gleichheitszeichen am Anfang durch Leerzeichen ersetzen
    formel = LTrim(formel)                    ' führende Leerzeichen entfernen, jetzt muss ganz links VLOOKUP stehen
    If Left(LTrim(formel), Len("VLOOKUP(")) = "VLOOKUP(" Then   ' komischerweise vor Funktionsklammer kein Leerzeichen möglich
     pfadend = InStr(formel, "\[")                              ' Poition von \[ (zwischen \ und [ kein Leerzeichen sonst Fehlfunktion von Excel)
     If pfadend <> 0 Then                                       ' ist \[ vorhanden?
      pfadanf = InStrRev(formel, "'", pfadend)
      If pfadanf <> 0 Then                      ' \[ und ' müssen vorhanden sein
      ' SVERWEIS mit Dateiname
       test = test + 1    ' zum Testen zählen wieviele Formeln geändert
         MsgBox "Alte Formel: " & Cells(zeile, spalte).Formula & Chr(10) & "Neue Formel: " & "=" & Mid(formel, 1, pfadanf) & Application.ThisWorkbook.Path & Mid(formel, pfadend)
       Cells(zeile, spalte).Formula = "=" & Mid(formel, 1, pfadanf) & Application.ThisWorkbook.Path & Mid(formel, pfadend)
      End If
     End If
    End If
   End If
  Next spalte
 Next zeile
 MsgBox "Fertig, " & test & " Formeln angepasst (Bearbeiteter Bereich $A$1:" & Cells(zeile - 1, spalte - 1).Address & ")."
 Exit Sub
 
Fehler1:
 MsgBox "Formel in Zelle " & Cells(zeile, spalte).Address & " kann nicht ge?ndert werden, Abbruch"
 Exit Sub
End Sub

[nordstern]

Es fehlte ein Unterordnerpfad^^

Ist aber uninteressant das Makro. Er braucht dafür für etwa 24.000 Formeln rund 50min. Wieso? Keine Ahnung. Wenn ich es manuell über die Ersetzenfunktion machen würde bräuchte er 30-40sec/Tabellenblatt also etwa 5-6min inkl. manueller Eingaben. Ich dachte er schafft das mit Makro in 2min.

Ich vermute es liegt an Excel selbst. Das durch die Änderungen per Makro er im Gegensatz zum ersetzen, jede Zelle ändert und anschließend aktualisiert. Und das dauert dann natürlich sehr lange, da er nicht wie beim aktualisieren Makro alles zusammen macht sondern jede Zelle einzeln. Wie ich das abstellen kann weis ich nicht und auch keinen den ich gefragt habe weis das (u.a. ein Informatik-Prof.).

Je mehr ich an Excel mache, desto mehr finde ich das es sehr schlampig und unstrukturiert programmiert wurde und das Mircosoft das dringend neu aufsetzen sollte... würde sie vermutlich auch machen, währen da nicht daraus resultierende Kompatibilitätsprobleme.