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?
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,061
Messages
5,545,763
Members
410,704
Latest member
Cobber2008
Top