using a variable in links

[bt-psmith]

='[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?

 

[bt-psmith]

does anybody have an idea on another way to do what I am looking for?

 

[erik.van.geit]

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]

You seem to be missing a double quote. I am not sure where to put it.

 

[bt-psmith]

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

 

[erik.van.geit]

sorry
at the end just before bracket
=INDIRECT("'[" & A1 & ".xls]Summary'!$A$1")

 

[bt-psmith]

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]

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]

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?

 

[erik.van.geit]

I can't, but you might consider posting your questions here
http://xcell05.free.fr/forums/viewforum.php?id=1 although it seems to me Laurent isn't much active on his site last months...