Referenciando um intervalo nomeado dinâmico entre pastas de trabalho

Question

Eu herdei essa estrutura de dados. Eu tenho um monte de produtos e suas informações de fornecedor, assim:

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

Vou referenciar esses dados de outras planilhas. Obviamente, usar vlookup apenas no número do pedido não será confiável sem diferenciar os fornecedores.

Minha primeira solução funciona localmente, mas não consigo trabalhar entre pastas de trabalho. Eu configurei um intervalo nomeado dentro da planilha acima usando INDIRECT, MATCH e COUNTIF para que os intervalos aumentem à medida que novos itens forem adicionados.

Neste exemplo, eu tenho Range_ACME definido como:

=INDIRECT("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))

(Na verdade, estou usando outra célula nomeada como o valor de pesquisa MATCH, mas simplifiquei para fins de teste.) Para que eu possa encontrar o custo para o item 00123 da ACME da seguinte forma:

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

Ao tentar usar esses intervalos em outra pasta de trabalho, recebo um erro informando que o intervalo é inválido. Se eu usar um intervalo de nome mais simples sem todo o estado selvagem INDIRETO, ele funcionará bem. Aqui, Range_ACME_Simple é apenas

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

Eu entendo que isso requer que eu tenha as pastas de trabalho abertas ao mesmo tempo. Existe outro método para definir os intervalos dinâmicos, ou devo apenas concatenar os nomes dos fornecedores com os números de ordem?

    

Answer 1

Para uma maneira simples de classificar esse problema e demonstrar o que é necessário, recriou as planilhas fornecidas e criou intervalos nomeados na pasta de trabalho do conjunto de dados ACME.xlsx em relação aos cabeçalhos de coluna de dados

• Fornecedor =Sheet1!$A:$A
• Pedido =Sheet1!$B:$B
• Nome =Sheet1!$C:$C
• Custo =Sheet1!$D:$D

Em seguida, na outra pasta de trabalho, criei pontos de entrada para os valores que você deseja pesquisar (número do fornecedor e do pedido) O nome do item que você quer que seja devolvido está no próximo ponto de entrada

Na célula B3 da pasta de trabalho que está sendo criada, entrei

=INDEX('C:\ACME.xlsx'!Name,MATCH(B1&B2,'C:\ACME.xlsx'!Supplier&'C:\ACME.xlsx'!Order,0)) e pressionado CTRL + SHIFT + ENTER

Isso nos dá o resultado desejado. Agora, se você alterar a célula B1 para ACME, o nome do item mudará automaticamente para F e você não precisará da pasta de trabalho que contém a estrutura de dados aberta se tiver o caminho completo do arquivo na fórmula.