Verweis auf einen dynamischen benannten Bereich zwischen Arbeitsmappen

1072
picobit

Ich habe diese Datenstruktur geerbt. Ich habe eine Reihe von Produkten und deren Lieferanteninformationen so angelegt:

Supplier Order Number Name Cost ACME 00123 A 1 ACME 00321 B 2 ACME 20798 C 3 ACME 11010 D 4 ACME 32333 E 5 ACME 20001 F 6 Babar 20001 G 7 Babar 54687 H 8 Babar 69441 I 9 Babar 65777 J 10

Ich werde auf diese Daten aus anderen Tabellen verweisen. Die Verwendung von vlookup allein für die Bestellnummer ist offensichtlich unzuverlässig, ohne zwischen den Lieferanten unterscheiden zu müssen.

Meine erste Lösung funktioniert lokal, aber ich kann nicht zwischen Arbeitsmappen arbeiten. Ich habe mit INDIRECT, MATCH und COUNTIF einen benannten Bereich in der obigen Tabelle eingerichtet, damit die Bereiche mit dem Hinzufügen neuer Elemente wachsen.

In diesem Beispiel habe ich Range_ACME definiert als:

= INDIREKT ("B" & MATCH (Sheet1! $ A $ 2, Sheet1! $ A: $ A, 0) & ": D" & (MATCH (Sheet1! $ A $ 2, Sheet1! $ A: $ A, 0) + COUNTIF (Sheet1! $ A: $ A, Sheet1! $ A $ 2) -1))

(Eigentlich verwende ich eine andere benannte Zelle als MATCH-Lookup-Wert, aber ich habe sie zu Testzwecken vereinfacht.) Damit ich die Kosten für ACMEs Artikel 00123 wie folgt ermitteln kann:

= VLOOKUP ("00123", Range_ACME, 2, false)

Beim Versuch, diese Bereiche in einer anderen Arbeitsmappe zu verwenden, wird die Fehlermeldung angezeigt, dass der Bereich ungültig ist. Wenn ich einen einfacheren benannten Bereich ohne die gesamte INDIRECT-Wildheit verwende, funktioniert das einwandfrei. Hier ist Range_ACME_Simple gerade

= Tabelle1! $ B $ 2: $ D $ 7

Verweis auf einen dynamischen benannten Bereich zwischen Arbeitsmappen

Ich verstehe, dass dazu die Arbeitsmappen gleichzeitig geöffnet sein müssen. Gibt es eine andere Methode, um die dynamischen Bereiche zu definieren, oder sollte ich die Lieferantennamen einfach mit den Bestellnummern verketten?

1
Chris 'Antwort ist praktikabel, aber ich lasse dies vorerst unbeantwortet. Ich würde trotzdem gerne wissen, ob das möglich ist, was ich tun möchte. Wenn es unmöglich ist, warum dann? picobit vor 7 Jahren 0

1 Antwort auf die Frage

2
Chris Rogers

Um dieses Problem auf einfache Weise zu lösen und zu zeigen, was erforderlich ist, habe ich die von Ihnen bereitgestellten Arbeitsblätter neu erstellt und benannte Bereiche in der Datensatz-Arbeitsmappe ACME.xlsx in Bezug auf die Kopfzeilen der Datenspalten erstellt

  • Lieferant =Sheet1!$A:$A
  • Auftrag =Sheet1!$B:$B
  • Name =Sheet1!$C:$C
  • Kosten =Sheet1!$D:$D

In der anderen Arbeitsmappe wurden dann Einstiegspunkte für die Werte erstellt, die Sie suchen möchten (Lieferanten- und Auftragsnummer). Der Name des Artikels, den Sie zurückgeben möchten, befindet sich am nächsten Einstiegspunkt

Andere Arbeitsmappe

In die Zelle B3der zu erstellenden Arbeitsmappe habe ich eingegeben

=INDEX('C:\ACME.xlsx'!Name,MATCH(B1&B2,'C:\ACME.xlsx'!Supplier&'C:\ACME.xlsx'!Order,0))und CTRL+ SHIFT+ gedrücktENTER

Dies gibt uns das gewünschte Ergebnis. Wenn Sie jetzt die Zelle B1in ACME ändern, ändert sich der Name des Elements automatisch in F, und Sie müssen die Arbeitsmappe mit der Datenstruktur nicht öffnen, wenn Sie den vollständigen Dateipfad in der Formel haben.

Unsere Antwort lautet also: Ja. Verketten Sie den Namen und die Bestellnummer des Lieferanten. picobit vor 7 Jahren 0
Als Array-Formel ist dies sehr langsam. Durch die Anwendung der Formel auf 400 Zellen und das Durchsuchen von 500 Elementen dauert das Filtern / Editieren auf meinem gen 2 i7 bis zu 10-15 Sekunden. picobit vor 7 Jahren 0

Verwandte Probleme