link to another workbook

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,864
what's the correct syntax for linking between workbooks. using formula to create the necessary links. i think i have forgotten INdirect somewhere.

=FilePath&"["&A8&Xtn&"]Formula'!"&B4&"210" and i know the 210 is wrong at the end. thats actually my row number. sheet in other workbook is called "Formula" and the cell i want is at B210
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,864
OK. found the syntax: "=INDIRECT("'"&FilePath&"["&$A8&Xtn&"]Formula'!"&B$4&"212")"

but returns #REF! error when the other workbook is closed. the straight link When the full filepath and name is used ) does not. I desperately need a workaround.

Anyone??
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,086
Office Version
  1. 365
Platform
  1. Windows
Indirect does not work on closed workbooks. As far as I know you have two options, either indirect or a straight link, the choice is yours.
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,864
Indirect does not work on closed workbooks. As far as I know you have two options, either indirect or a straight link, the choice is yours.
the choice was always mine (unless the boss is here and even that depends on whether i am at work or at home). i went with a straight link for the time being. saw some posts on creating a custom UDF and then using an event to work it. will read up on it some more.

cheers
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,086
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,109
Messages
5,768,148
Members
425,458
Latest member
Jaspal1996

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