join cells to create formula that references another workbook;

donkeycrusher

New Member
Joined
Aug 13, 2004
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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"
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
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

New Member
Joined
Aug 13, 2004
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 13, 2004
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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 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
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
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.
 

Forum statistics

Threads
1,175,752
Messages
5,899,276
Members
434,759
Latest member
carcharoth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top