#value error ao atualizar fórmulas SUMIFS com intervalo de outra pasta de trabalho no Excel 2013

Question

Eu tenho em uma pasta local um arquivo de origem com uma tabela de duas colunas com pares de valores-chave e um arquivo de destino que usa dados da origem da seguinte forma:

1. link para uma célula específica:

   = 'C: \ Temp [source.xlsx] Folha1'! $ B1

2. consulta um valor de um intervalo:

   = VLOOKUP (A1, 'C: \ Temp [fonte.xlsx] Folha1'! $ A $ 1: $ B $ 6,2,0)

3. Função SUMIFS com intervalo e condição:

   = SUMIFS ('C: \ Temp [source.xlsx] Folha1'! $ B $ 1: $ B $ 6, 'C: \ Temp [source.xlsx] Folha1'! $ A $ 1: $ A $ 6, D1)

Ao abrir a pasta de trabalho de destino sem abrir a pasta de trabalho de origem, recebo a mensagem "Esta pasta de trabalho contém links para ..." com as opções "Atualizar" e "Não atualizar". No fundo desse prompt, posso ver os valores salvos quando fechei o arquivo.

Se o arquivo de origem permanecer fechado e eu escolher a opção "Atualizar", recebo os valores corretos para o link (1) e para a consulta (2), mas erro #VALUE! para SUMIFS (3) . Se eu abrir agora o arquivo de origem, o valor SUMIFS será calculado corretamente.

Por favor, note que - sem o arquivo fonte aberto - na caixa de diálogo "Editar Links" (da pasta Dados) recebo primeiro um status "desconhecido" para o arquivo fonte, depois "OK" depois clico em "verificar status" e ainda #VALUE depois que clico em "atualizar valores"

Este é o caso de teste que usei para uma situação relacionada ao trabalho: um arquivo com a função SUMIFS com argumentos apontando para um arquivo de origem que mostra o valor correto quando solicitado para atualização / não atualizar, mas muda para #VALUE! erro independentemente da opção que escolhi (atualizar ou não atualizar)

As perguntas óbvias: por que o Excel 2013 está fazendo isso e como resolvê-lo?

    

Answer 1

O Excel está funcionando conforme projetado. Ele não permite que fórmulas leiam dados em pastas de trabalho fechadas.

Para contornar essa limitação, você precisará usar o VBA para recuperar dados dos arquivos fechados. Você também pode procurar por "excel 2013 closed workbook data" para encontrar outros métodos.

Aqui está um exemplo de como realizá-lo a partir de Leia informações de uma pasta de trabalho fechada usando o VBA no Microsoft Excel :

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Foldername\Filename.xls", True, True) 
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("TargetSheetName")
        ' read data from the source workbook
        .Range("A10").Formula = wb.Worksheets("SourceSheetName").Range("A10").Formula
        .Range("A11").Formula = wb.Worksheets("SourceSheetName").Range("A20").Formula
        .Range("A12").Formula = wb.Worksheets("SourceSheetName").Range("A30").Formula
        .Range("A13").Formula = wb.Worksheets("SourceSheetName").Range("A40").Formula
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Aqui está outro exemplo - Excel “Pull ”Função: Criando links dinâmicos para pastas de trabalho fechadas .

Você terá que experimentar isso para atender às suas próprias necessidades.