Extraer datos de otro libro de forma dinámica

[Jameo]

Buenos días comité de sabios:

Voy a tratar de explicar mi problema, sobre el que googleado mucho pero no alcanzo a encontrar una solución definitiva. 

Mi propósito es hacer vínculos dinámicos a otro libro para extraer datos en concreto de una serie de celdas en concreto, valga la redundancia. 
El tema es que el software del sitio donde trabajo no realiza las operaciones que necesitaría, lo que sí puedo hacer desde el programa es exportar en .xls un listado como fuente de dichos datos. Es una lista de productos en la columna A, y las unidades vendidas en la columna D, el resto de datos son despreciables. Como cada vez que solicito el listado es un archivo nuevo, no puedo ejecutar sobre él las operaciones, o no sería cómodo, así que mi objetivo es crear una hoja para extraer de ahí lo que necesito. 
El tema es que el software solo lista productos con unidades vendidas superiores a 0, es decir, solo muestra lo que se ha vendido y no muestra lo que, aún existiendo, no se vende, por lo que el orden de dichos productos no es siempre el mismo, varía en función de las ventas. De no ser así serían referencias fijas porque siempre estarían en el mismo sitio. Aunque siempre estará su nombre en la columna A y sus unidades en la columna D, es no varía. 
En otra hoja, tendré una lista con todos los productos, y a base de fórmulas quiero extraer las unidades, el nombre no es importante porque ya lo tendré en la hoja nueva, solo quiero saber el número de unidades vendidas para un determinado producto. 
Paso a la parte un poco más práctica:
Teniendo la lista en el listado "destino", busco coincidencias en el listado "fuente" con:

[En la celda b9] -> =COINCIDIR(A9;'[fuente.xlsx]Hoja1'!$A$1:$A$185;0)
esto me devuelve la fila en la que el registro que ya tengo en A9 en destino (pongamos Producto 1). 
Luego paso a: [En la celda C9] -> =DIRECCION(B9;4)
Donde recupero la dirección de la celda a la que quiero apuntar, como es la columna D, pongo 4. 
Entonces ya tengo la dirección de la celda que necesito: $D$18
Si yo ahora utilizo: ='[fuente.xlsx]Hoja1'!$D$18 Me devuelve exactamente el dígito que necesito. 
Pero mi problema es que la celda de destino no siempre será $D$18, sino que es el resultado de la fórmula anterior. ( =DIRECCION(b9;4) )
Quisiera hacer referencia en la llamada a la celda al resultado de dicha fórmula, contenida en este caso en C9 (por ejemplo). 
No puedo utilizar ='[fuente.xlsx]Hoja1'!C9 , porque me estaría refiriendo a la celda de la hoja de origen, pero quiero que sea "ruta de destino de la otra hoja"+"celda especificada en hoja actual". 
Hay alguna manera de usar ='[fuente.xlsx]Hoja1'!=c9 ? o cual sería la sintaxis correcta para que en esa parte de la fórmula se aplique el resultado de la celda mencionada? 
En Google siempre se comenta sobre la función INDIRECTO, pero eso me obliga a tener el libro fuente abierto para que funcione, y quisiera saber alguna forma de que no fuera necesario, toda vez que los datos hasta ahora los obtengo sin problemas con el libro fuente cerrado. 

Si alguien tiene una manera menos complicada también es bienvenida, y cualquier aporte sobre como solucionar el problema también. 
Sé que no me explico muy bien así que pueden consultar lo que buenamente quieran. 
Muchas gracias de antemano.