join cells to create formula that references another workbook;

[donkeycrusher]

I'm not sure what the proper nomenclature is for what i'm trying to do, but here's a description
In Cell B1 I want to paste the filepath to another workbook and worksheet (E.g. 'C:\Users\User1\Dropbox\Active\JACKSONVILLE\[Peachtree_Model.xlsm]Summary')
In Cell B2 I want a formula that combines the filepath in B1 and cell reference to that sheet (E.g. =$B$1&$B$3)
The cell populates with the the text 'C:\Users\User1\Dropbox\Active\JACKSONVILLE\[Peachtree_Model.xlsm]Summary'$B$3 instead of making a formula and referencing the information in that cell, which is "Fernandina Beach, FL"

 

[Gerald Higgins]

Hi, your request is a little vague.

In Cell B1 I want to paste the filepath to another workbook and worksheet (E.g. 'C:\Users\User1\Dropbox\Active\JACKSONVILLE\[Peachtree_Model.xlsm]Summary')

Do you need help with this part ? If YES, please clarify.

In Cell B2 I want a formula that combines the filepath in B1 and cell reference to that sheet (E.g. =$B$1&$B$3)

How do we know that it needs to refer to $B$3 ?
Is it always $B$3, or could it vary ?

 

[donkeycrusher]

Hi, your request is a little vague.


Do you need help with this part ? If YES, please clarify.


How do we know that it needs to refer to $B$3 ?
Is it always $B$3, or could it vary ?

Sorry, I didn't want to be too wordy. Each row will contain information from a different workbook. Each workbook is set up exactly the same (they're financial models). If row 2 is the summary for the "Oakwood" workbook and row 3 is the "Kissimmee" workbook, I'm trying to paste the file path to Oakwood in $B$1 and in cell C1 write a formula that references the file path (workbook and sheet number pasted in B1 and retrieve the information in cell B3 or K2 or whatever cell I want. I locked both cells because I'll copy the formulas to every row in the summary sheet.

 

[donkeycrusher]

I'm rephrasing my question in a manner that hopefully clears it up.

I'm trying to create a formula that references an external workbook location in the first column 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 Address	Project Name	City, State	Acres	Rent per Sq.Ft
C:\Users\User1\[Workbook1.xlsm]Sheet1'!	='Workbook Address'!C5	='Workbook Address'!F3	='Workbook Address'!D23	='Workbook Address'!N11
Workbook2
Workbook3

 

[Gerald Higgins]

Hi, I think I know roughly what you want to do, but not exactly.

Maybe you could try taking a look at the INDIRECT function, I think that may be useful to you.