using a variable in links

bt-psmith

New Member
Joined
Oct 11, 2005
Messages
23
='[April 06_Revenue.xls]Summary'!$A$1

What I want to do is use a named range in the place of "April 06" in the above link so that I don't have to update links for every month.
I would like to just change the period in the named range (called "period") and have all the links updated.

I tried to put the variable in the link as follows:

="'["&Period&"_Revenue.xls]Summary'!"&$A$1

but I get a formula error...anyone know how I can get this to work?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

Have a look at the function
INDIRECT in the helpfiles
='[April 06_Revenue.xls]Summary'!$A$1

if A1 houses April 06_Revenue
=INDIRECT("'[" & A1 & ".xls]Summary'!$A$1)
take care of double and single quotes !

kind regards,
Erik
 

bt-psmith

New Member
Joined
Oct 11, 2005
Messages
23

ADVERTISEMENT

I put the double quote inside the last parentheses but still got a ref error. any ideas?
 

bt-psmith

New Member
Joined
Oct 11, 2005
Messages
23

ADVERTISEMENT

I figured out why i got the ref.... both files need to be open. when you use the INDIRECT function with an external reference, both files need to be open for the function to work.


Any other ideas that would not require both files to be open?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
note that indirect() requires the source workbook to be open. fixed by use of indirect.ext() from morefunc addin (for which see the recommended addins thread at the top of this board)
 

bt-psmith

New Member
Joined
Oct 11, 2005
Messages
23
using morefunc add-ins

I am trying to use the morefunc addin. I have saved the FunCustomize.dll and the FunCustomize_demo.xla to my add in folder and have used the "Tools" addin feature to load.

I can see 4 new functions will appear in the category "FunCustomize Demo" of the function wizard. The INDIRECT.EXT function is not there. I looked at the help file and it says I neeed to add the parameters of my functions ina worksheet of the .xla file. I have no idea what I am supposed to be entering or where to get that information from.

Can anyone help?
 

Forum statistics

Threads
1,141,924
Messages
5,709,369
Members
421,632
Latest member
BrennieB

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