Buscar en hojas en distintas carpetas en función de una celda

Me he decidido a pedir ayuda ya que llevo bastante tiempo intentando dar con la solución y no soy capaz. Os detallo el problema:

Yo tengo varias hojas excel con la misma estructura exacta de datos, es decir que lo que está en A1 en una es el mismo dato de A1 de otra hoja pero de productos diferentes. La estructura es la siguiente: tengo carpetas cuyo nombre son: XXXX XXXX Capacidad, XXXX XXXX Peso, etc... Para cada producto donde XXXX XXXX es el código del producto y tengo cada producto en distintas carpetas. Os pongo un ejemplo:

Producto 1234 A123, tiene una carpeta general que es "Prod. 1234 A123" y dentro de ella estaría "1234 A123 Capacidad", "1234 A123 Peso" etc...

Para el producto 2345 A123 lo mismo: "Prod. 2345 A123" y dentro "2345 A123 Capacidad", "2345 A123 Peso" etc...

Mis códigos de productos son del formato 1234 A1234, siempre 4 cifras seguidas de un espacio y una letra con 3 cifras más.

¿Qué necesito? Pues que cuando escriba el código del producto en una celda del peso (1234 A123 por ejemplo) mi hoja pueda ir a la carpeta de ese producto y buscar en el archivo del peso y me extraiga el dato.

He consguido algo con la función =INDIRECTO pero me obliga a tener todas las hojas donde lee abiertas y por tanto no me funciona. Me gustaría que me ayudaseis a dar con la solución, que imagino que será una macro, pero de macros ando muy limitado y no soy capaz de sacar nada en claro.