Insertar un texto (o celda) en un vinculo

Para resolver tu problema, MS Excel cuenta con la función =INDIRECTO(<celda>).
Donde asumirá que el rango a considerar en una fórmula es el que esté indicado en tal celda.
Probablemente será necesario que utilices la función CONCATENAR (o el operador "&") anidados dentro de esa celda para obtener la dirección completa.
En tu caso podrías organizarlo, por ejemplo, así:
-Directorio: (D:\DIARIO2002\) en la celda B4.
-Archivo: (AA) en la celda C20.(Y otras)
-Hoja: "Inform. Diaria" en la celda B5
-Celda: (F6) en la celda D20
La celda donde quieres mostrar ese dato (digamos E20) debería contar, entonces, con una fórmula como está:
=INDIRECTO(CONCATENAR($B$4,"[",C20,".xls]",$B$5,"!",$D$20))
Notas:
- Considera si usas coma o punto y coma para separar argumentos. En esta fórmula usé coma (,).
- Excepto la celda donde está el nombre del archivo, el resto de las celdas tienen referencia absoluta ($) de forma tal que si copias esta fórmula al lado de otro nombre de archivo, tomará este y el resto serán los mismo datos comunes a todas (Directorio, Hoja, Celda).
- La extensión del archivo (.xls) está incluida en la fórmula porque también es común. No sería necesario que la incluyas en la celda de cada archivo a evaluar.
Un comentario final, muy importante:
Esta función es sumamente potente (y útil) pero tiene un inconveniente: para que funcione correctamente exige que el archivo referenciado en la celda esté ABIERTO, al mismo tiempo que el archivo dónde está la fórmula. Caso contrario, obtendrás un mensaje de error (#REF!)
Esto debería resolver tu pregunta. Si así fuera, te agradeceré un comentario y finalizarla.
Si no, pregúntame de nuevo.
Un abrazo!
Fernando

El sistema es perfecto, pero tengo el grave problema que no puedo tener todos los ficheros abiertos al ser demasiados y me ralentiza el proceso. Para ponerte en antecedentes, tengo unos 60 ficheros distintos y unas 25 columnas para cambiar. El año pasado tuve este mismo problema y estuve 3 fines de semana picando datos.
No se si podría ser posible tener, una pequeña macro que concatenara y me dejara el texto definitivo, en fin no lo se. Haber si se te ocurre algo.
Muchas gracias de todas formas
Saludos, JRM

Es cierto, está función es muy buena para pocos archivos. Sería excelente si la gente de Microsoft lograse que funcione sin tener que abrirlos...
Mientras tanto, acabo de desarrollar este código que escribirá las direcciones de acuerdo a los nombres de archivo que encuentre en una lista.
Una vez más, supondré que:
-Directorio: (D:\DIARIO2002\) en la celda B4.
-Hoja: "Inform. Diaria" en la celda B5
-Celda: (F6) en la celda B6 (ojo, cambié esta respecto a la primera respuesta)
Los nombres de los archivos a utilizar deberán estar listados uno abajo del otro. Pueden estar en cualquier columna pero sin celdas vacías entre medio.
No es necesario que agregues la extensión (.xls) pues la coloca la macro.
Asegurate también que en las celdas a la derecha no halla nada, pues allí escribirá las fórmulas que necesitas.
Activa el editor de Visual Basic (presiona Alt+F11), inserta un nuevo módulo ("Insertar", "Modulo") y pega el siguiente código:
Sub changeform()
Dim Arch, cellcell As String
Application.ScreenUpdating = False
Do While ActiveCell.Value <> ""
cellcell = Trim(Chr(39) & Range("B6").Text & Chr(39))
Arch = Trim("='" & Range("B4").Value & "[" & Trim(ActiveCell.Value) & ".xls]" & Range("B5").Value & "'!" & Range("B6").Text)
ActiveCell.Offset(0, 1).FormulaR1C1 = Arch
ActiveCell.Offset(0, 1).Replace What:=cellcell, Replacement:=Range("B6").Text, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Offset(1).Select
Loop
MsgBox "Proceso de cambio de fórmulas terminado", vbInformation
Application.ScreenUpdating = True
End Sub
Cierra el editor y graba el archivo.
Para que funcione selecciona la primer celda del rango de nombres de archivos y ejecuta la macro que acabas de pegar(changeform)
Hasta donde probé, funciona perfectamente. Intentalo y no dejes de comentarme el resultado.
Un gran abrazo!
Fernando