Extracting data from an other workbook using formula and a refering cell

[MarioExcelNoob]

Hi all

I've been looking on this forum, but can’t seem to find exactly what i'mlooking for...

So, my question:

Say you have a number of workbooks: Book1, Book2, Bookxx and Overview
I need to extract data from Book1 and Book2 (and the others) into Overview.

Now, I know a formula for Overview: =[Book1.xlsx]Sheet1!A1 to get data from cell A1from Book1

The thing is, I want to get the name of theworkbook I need from a cell in Overview.
Example from workbook Overview:
A B
1 Book1 =[$A1.xlsx]Sheet1!A1

2 Book2 =[$A2.xlsx]Sheet1!A1


$A1 and $A2 referring to the cell A1 and A2 in the same workbook and sheet.
So I need the names of the workbooks filled in, in theformula in column B. This way, I can copy my formula down.

I’ve tried makinga string with the formula as text and then use INDIRECT, but that doesn’t do the trickand my VBA knowledge is far from on point.

Any1 that can help me with this issue?


Thanks

 

[MarioExcelNoob]

	A	B
1	Book1	=[$A1.xlsx]Sheet1!A1
2	Book2	=[$A2.xlsx]Sheet1!A1

<tbody>
</tbody>

So the example should look like this...