Need Help writing a macro

[TwEaKFrEaK]

Root Year Quarter Filename Type


='c:\Documents\ 2004\ Quarter 3\ [Test .xls]
Data Range
Performance'!$A$2:$J$402


Formula
='c:\Documents\2004\Quarter 3
[Test.xls]Performance'!$A$2:$J$402

I would like to define Name "CompanyA" (i.e, insert name, company A) t
be linked to formula above. The only problem is that I want the link t
be dynamic so when I change the top of the input sheet (highlighted i
yellow) the formula changes which then changes the link that is bein
used to define company A. Any help writing this macro? Thank you al
in advance.

I have made an example excel file but I do not know how to post on th
forum. Please let me know if you want to see the file and I willl sen
it to you via email. Thank you all very very much

 

[DavidC]

I presume that you want to change [Test.xls} to another
name defined in a cell at the top of the page.

If this is the case then create a variable to contain the
name as defined in the relevant cell.
then use the variable in the path defintiion.

eg

compname=activesheet.range("A1").value

="c:\Documents\2004\Quarter 3\"& compname & "Performance!
$A$2:$J$402"

Hope this is what ytou are looking for.

BOL
DavidC

-----Original Message-----
Root Year Quarter Filename Type


='c:\Documents\ 2004\ Quarter 3\ [Test .xls]
Data Range
Performance'!$A$2:$J$402


Formula
='c:\Documents\2004\Quarter 3\
[Test.xls]Performance'!$A$2:$J$402