combine reference to workbook and cell

Copper Contributor

I'm trying to create a formula that references an external workbook location in row A to retrieve information to create a master summary reference workbook.  Using the table below as an example, I want to paste the file path in the first column, then in the second column have a formula that references the file path in the first column and adds the cell reference I'd like to retrieve information for, then repeat for each of the other columns. 

 

This way I can simply paste the workbook address for each new project, copy the formulas in "Project Name", "City, State", "Acres" and "Rent per Sq.Ft" down to the next row and have each of the associated columns automatically retrieve that information instead of having to map every cell independently, or perform a Ctrl+H to replace the file paths.

Workbook AddressProject NameCity, StateAcresRent per Sq.Ft
C:\Users\User1\[Workbook1.xlsm]Sheet1'!='Workbook Address'!C5='Workbook Address'!F3='Workbook Address'!D23='Workbook Address'!N11
Workbook2    
Workbook3    
3 Replies

@Sgriff2022 

You may use INDIRECT(), but with that all referenced workbooks shall be opened.

Thanks for the reply Sergei. I tried that, but get a #REF! error. My formula is =INDIRECT('[B1]Sheet'!C5)
I also tried a concat formula, but that one just displays the text.

@Sgriff2022 

That is

=INDIRECT("'[B1]Sheet1'!C5")

or combine the text