☰

Error #valor al actualizar fórmulas SUMIFS con rango de otro libro en Excel 2013

tag-icon • tag-icon microsoft-excel-2013
Error #valor al actualizar fórmulas SUMIFS con rango de otro libro en Excel 2013

Tengo en una carpeta local un archivo de origen con una tabla de dos columnas con pares clave-valor y un archivo de destino que utiliza datos del origen de la siguiente manera:

  1. enlace a una celda específica:

    ='C:\Temp[fuente.xlsx]Hoja1'!$B1

  2. consultar un valor de un rango:

    =BUSCARV(A1,'C:\Temp[fuente.xlsx]Hoja1'!$A$1:$B$6,2,0)

  3. Función SUMIFS con rango y condición:

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

Al abrir el libro de destino sin abrir el libro de origen, aparece el mensaje "Este libro contiene enlaces a ..." con las opciones "Actualizar" y "No actualizar". En el fondo de este mensaje puedo ver los valores guardados cuando cerré el archivo.

Si el archivo fuente permanece cerrado y elijo la opción "Actualizar", obtengo valores correctos para el enlace (1) y para la consulta (2), pero#¡VALOR!error para SUMIFS (3). Si abro ahora el archivo fuente, el valor SUMIFS se calcula correctamente.

Tenga en cuenta que, sin el archivo fuente abierto, en el cuadro de diálogo "Editar enlaces" (de la carpeta Datos), primero aparece un estado "desconocido" para el archivo fuente, luego "Aceptar" después de hacer clic en "verificar estado" y sigue siendo #VALOR después Hago clic en "actualizar valores"

Este es el caso de prueba que utilicé para una situación relacionada con el trabajo: un archivo con la función SUMIFS con argumentos que apuntan a un archivo fuente que muestra el valor correcto cuando se le solicita actualizar/no actualizar, pero cambia a #VALOR. error independientemente de la opción que elija (actualizar o no actualizar)

Las preguntas obvias:¿Por qué Excel 2013 hace esto y cómo solucionarlo?

Respuesta1

Excel funciona según lo diseñado. No permite que fórmulas lean datos en libros cerrados.

Para solucionar esta limitación, deberá utilizar VBA para recuperar datos de los archivos cerrados. También puede buscar "datos del libro cerrado de Excel 2013" para encontrar otros métodos.

A continuación se muestra un ejemplo de cómo lograrlo desdeLeer información de un libro cerrado usando VBA en 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

Aquí hay otro ejemplo:Función "Pull" de Excel: creación de enlaces dinámicos a libros cerrados.

Tendrá que experimentar con esto para adaptarlo a sus propias necesidades.

información relacionada