¿Como incorporar un valor de una celda en una formula de combinación de datos?

Puedes hacerlo con la fórmula indirecto, la desventaja de esta opción es que deberás tener abierto el libro.

Cambia tu fórmula por esta

=INDIRECTO("'E:\CONTACTOS\CONTACTOS\"&C5&"\[1.xlsx]FICHA GENERAL'!$G$9")

En la celda C5 escribe el 1, el 2 ó el número de carpeta.

Con la siguiente opción el cambio es automático, pero tendrás que utilizar la siguiente macro.

Private Sub Worksheet_Change(ByVal Target As Range)
'Por.Dante Amor
    If Not Intersect(Target, Range("C5")) Is Nothing Then
        Range("D5") = "='E:\CONTACTOS\CONTACTOS\" & [C5] & "\[1.xlsx]FICHA GENERAL'!R9C7"
    End If
End Sub

Cada vez que modifiques el dato de la celda C5, se reescribirá la fórmula considerando el dato que pusiste en la celda C5. La nueva fórmula quedará en la celda D5.

Para que la macro se ejecute, deberás guardar el archivo como excel habilitado para macros y cuando abras el archivo deberás habilitar las macros.

Sigue las Instrucciones para poner la macro en worksheet

1. Abre tu libro de excel

2. Para abrir Vba-macros y poder pegar la macro, Presiona Alt + F11

3. Del lado izquierdo dice: VBAProject, abajo dale doble click a worksheet(tu hoja)

4. Del lado derecho copia la macro               

Prueba cuál de las 2 opciones te es más útil, si tienes dudas puedes contactarme.

Muchas gracias Dante, pero el caso es que necesitamos que esto sea automático y no especifico de una celda, necesitamos que en la columna A poder ir escribiendo los nombres de las carpetas que son números (dígase carpetas "1, 2, 3, 4, 5..." y dentro de estas mismas carpetas existen los libros: en la carpeta 1 el libro 1 en la carpeta 2 el libro 2 y así sucesivamente).

Con lo que pretendemos que al abrir el libro que estamos creando a modo de indice o hoja general, aparezcan los datos de las celdas que a nosotros nos interesen de los libros antes descritos. De esta forma, podremos consultar por ejemplo en la columna B las fechas de nacimiento y con los filtros, localizaríamos la que nos interese y sabríamos en que carpeta y archivo esta.

Por eso, la consulta más clara seria, que fórmula o macro debemos utilizar para que cada vez que copiemos la fórmula hacia abajo, ¿nos vaya extrayendo la información de la carpeta y libro indicado en la columna A?

Porque ahora mismo, lo que estamos haciendo, es copiar las fórmulas de las diferentes columnas hacia abajo y luego celda por celda y columna por columna, vamos cambiando la fórmula manualmente para que busque en la carpeta y archivo correspondiente al nombre o numero que tenemos escrito en la columna A.

Eje:

Columna A       Columna B

1                        ='E:\CONTACTOS\CONTACTOS\1\[1.xlsx]FICHA GENERAL'!$G$9

2                        ='E:\CONTACTOS\CONTACTOS\2\[2.xlsx]FICHA GENERAL'!$G$9

¿Existe alguna fórmula?

Muchas gracias